Re: atomically replace partition of range partitioned table

On Tue, 26 Feb 2019 at 12:03, Kevin Wilkinson
<w(dot)kevin(dot)wilkinson(at)gmail(dot)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