partition_data_async better implementation?

[Faithfinder]

Based on my previous research, there's a very good pattern for creating partitions with minimal table downtime.

Here:

-- Creating and attaching a partition separately causes a more permissive lock, than creating a table straight as a partition
CREATE TABLE partition_p00000000 (LIKE parent_table INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

-- Check constraint on the partition table tells Postgre that this table has no data that should be in other partitions, no need to scan
ALTER TABLE partition_p00000000 ADD CONSTRAINT "partition_check"
   CHECK ( partition_column >= DATE '2024-12-04' AND partition_column < DATE '2024-12-11' );
  
-- Moving data from the default partition in case some is there
INSERT INTO partition_p00000000 SELECT * FROM partition_default WHERE partition_column >= DATE '2024-12-04' AND partition_column < DATE '2024-12-11';
DELETE FROM partition_default WHERE partition_column >= DATE '2024-12-04' AND partition_column < DATE '2024-12-11';


-- Check constraint on default partition tells Postgre that it has no data that should be in the new partition and no need to scan it
-- Creating it as "NOT VALID" allows to create it without checking the data, limiting the ExclusiveLock time
ALTER TABLE partition_default ADD CONSTRAINT "default_partition_check"
   CHECK ( partition_column < DATE '2024-12-04' ) NOT VALID;
-- Validating the constraint after the check is applied only does `ShareUpdateExclusiveLock`, allowing selects and inserts during the process
ALTER TABLE partition_default VALIDATE CONSTRAINT "default_partition_check";
  
-- Attach the new partition
ALTER TABLE parent_table ATTACH PARTITION partition_p00000000
    FOR VALUES FROM ('2024-12-04') TO ('2024-12-11');

-- Check constraints aren't needed after the operation is complete
ALTER TABLE partition_p00000000 DROP CONSTRAINT "partition_check";
ALTER TABLE partition_default DROP CONSTRAINT "default_partition_check";

For me, the key difference from what partition_data_async currently does is that the "outside" table is not temporary, and is attached as the partition itself, not dropped. The obvious tradeoff is that you can't insert data that would go to this partition until everything is done, but that's not necessarily a bad thing.

If I'm being silly, feel free to disregard, just thought I'd share my research in case it's useful :)

[keithf4]

That's actually a really good idea for the async partition operation! Saves the entire step of having to move it back. If you'd like to do a PR to implement this, I'd be happy to review it. Otherwise I'll see about what I can do to change that procedure's functionality.

Thank you!

[keithf4]

Going to turn this discussion into an issue so I can keep track of it and not forget.

[keithf4]

Issue can be found here. Please continue further discussion there. Thank you again!

#827