On Tue, 26 Feb 2019 at 12:03, Kevin Wilkinson <w.kevin.wilkin...@gmail.com> wrote: > 1. create a new partition table by copying the old partition table, > ordered by index key. both tables will have the same partition key range. > 2. create a brin index on the new table. > 3. detach the old partition table from the parent and drop it. > 4. attach the new partition table to the parent. > > what i need is for steps 3-4 to be atomic or quick. but, step 4 takes > tens of seconds, sometimes almost a minute. i tried adding a check > constraint to the new table so that it would not be scanned when > attached but that does not help. is there any way to do want i want?
TBH, I think the check constraint checking code needs a bit more work in regards to this. It does not appear to be able to use strict quals in the constraint to validate that the columns cannot be NULL. # create table rp (a int ) partition by range(a); CREATE TABLE # create table rp1 (a int); CREATE TABLE # insert into rp1 select generate_series(1,2000000); INSERT 0 2000000 # \timing on Spot the difference here: # alter table rp1 add constraint rp1_a_chk check(a >= 1 and a < 2000001); ALTER TABLE Time: 157.391 ms # alter table rp attach partition rp1 for values from(1) to (2000001); ALTER TABLE Time: 184.188 ms # alter table rp detach partition rp1; # alter table rp1 drop constraint rp1_a_chk; # alter table rp1 add constraint rp1_a_chk check(a is not null and a >= 1 and a < 2000001); ALTER TABLE Time: 179.750 ms # alter table rp attach partition rp1 for values from(1) to (2000001); INFO: partition constraint for table "rp1" is implied by existing constraints ALTER TABLE Time: 4.969 ms # alter table rp detach partition rp1; # alter table rp1 drop constraint rp1_a_chk; -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
