Having trouble understanding how to support foreign keys on children of a partitioned parent

[tatzlwurm2]

I created a testcase:

set search_path=partman,public,newtest;
SET
drop table newtest.child;
DROP TABLE
drop table newtest.parent;
DROP TABLE
\timing
Timing is on.
delete from partman.part_config where parent_table='newtest.parent';
DELETE 1
Time: 2.422 ms
drop table partman.template_newtest_parent;
DROP TABLE
Time: 1.790 ms
create table newtest.parent(parentid bigserial primary key, comment text) partition by range(parentid);
CREATE TABLE
Time: 3.946 ms
create table newtest.child(childid bigserial primary key, parentid bigint);
CREATE TABLE
Time: 4.827 ms
SELECT partman.create_parent('newtest.parent','parentid','native','20000');
 create_parent
---------------
 t
(1 row)

Time: 102.737 ms
        --,p_upsert := 'ON CONFLICT (id) DO NOTHING');
alter table child add foreign key (parentid) references parent(parentid);
ALTER TABLE
Time: 6.780 ms
with insert_parent as
( insert into newtest.parent (comment) (select generate_series(1,150000))  returning parentid)
insert into newtest.child (parentid) select parentid from insert_parent;
INSERT 0 150000
Time: 5448.877 ms (00:05.449)
CALL partman.partition_data_proc('newtest.parent', p_batch := 20000);
psql:/tmp/x.sql:21: ERROR:  update or delete on table "parent_default" violates foreign key constraint "child_parentid_fkey6" on table "child"
DETAIL:  Key (parentid)=(100000) is still referenced from table "child".
CONTEXT:  SQL statement "WITH partition_data AS (
            DELETE FROM newtest.parent_default WHERE parentid >= 100000 AND parentid < 120000 RETURNING *)
        INSERT INTO partman_temp_data_storage SELECT * FROM partition_data"
PL/pgSQL function partition_data_id(text,integer,bigint,numeric,text,boolean,text) line 181 at EXECUTE
SQL statement "SELECT partman.partition_data_id ('newtest.parent', p_lock_wait := '0', p_order := 'ASC', p_analyze := false)"
PL/pgSQL function partition_data_proc(text,text,integer,integer,text,text,integer,integer,boolean) line 84 at EXECUTE
Time: 71.983 ms

I can work around issue by issuing

alter table newtest.child drop constraint child_parentid_fkey; CALL partman.partition_data_proc('newtest.parent', p_batch := 20000);alter table newtest.child add foreign key (parentid) references newtest.parent(parentid);
ALTER TABLE
NOTICE:  Batch: 1, Rows moved: 20000
NOTICE:  Batch: 2, Rows moved: 20000
NOTICE:  Batch: 3, Rows moved: 10001
NOTICE:  Total rows moved: 50001
NOTICE:  Ensure to VACUUM ANALYZE the parent (and source table if used) after partitioning data
CALL
ALTER TABLE

What am I missing?
Reading -- misspelling -- https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman_howto_native.md#online-partitioning
Looks like its a issue (have you considered something like an online redefinition -- like pg_repack? )
"IMPORTANT NOTE REGUARDING FOREIGN KEYS

Taking the partitioned table offline is the only method that realistically works when you have foreign keys TO the table being partitioned. Since a brand new table must be created no matter what, the foreign key must also be recreated, so an outage involving all tables that are part of the FK relationship must be taken. A shorter outage may be possible with the online method below, but if you have to take an outage, this offline method is easier. "

I did figure how to make this less disruptive. Found this site : https://travisofthenorth.com/blog/2017/2/2/postgres-adding-foreign-keys-with-zero-downtime

Begin;
Alter table wiki_s.revision drop constraint fk_rev_text_id; 
CALL partman.partition_data_proc('wiki_s.text', p_batch := 2000000);
alter table wiki_s.revision add CONSTRAINT fk_rev_text_id FOREIGN KEY (rev_text_id) REFERENCES wiki_s."text"(old_id) not valid;
Commit;
Begin ;
alter table wiki_s.revision validate constraint fk_rev_text_id;
Commit;

I was able to achieve the goal of partitioning while maintaining no locks. I only have the exposure of dropping the fk at the time of maintenance.

[keithf4]

I'm not sure of any better way to handle this at the moment than your last option of doing everything in a transaction to try and make it transparent to the user. It seems the issue is the nature of how you have to move the data out of the default and into a new child table. You cannot add the new child table without first removing the data from the default but that would potentially violate your FK reference, as you're seeing here.

My recommendation to avoid this would be to ensure your premake is set high enough to account for your normally expected data entries and avoid them going into the default. If you really don't want data going into the default and then having to worry about this, you can drop the default. But then if someone tries to insert data outside the range of the children, it will fail and be lost.