On Wednesday, June 23, 2021 12:16 PM I wrote: > When directly INSERT INTO partition, postgres will invoke ExecPartitionCheck > which will execute its parent's and grandparent's partition constraint check. > From the code, the whole constraint check is saved in relcache::rd_partcheck. > > For a multi-level partition, for example: table 'A' is partition of table > 'B', and 'B' > is also partition of table 'C'. After I 'ALTER TABLE C DETACH B', I thought > partition constraint check of table 'C' does not matter anymore if INSERT INTO > table 'A'. But it looks like the relcache of 'A' is not invalidated after > detaching 'B'. > And the relcache::rd_partcheck still include the partition constraint of > table 'C'. > Note If I invalidate the table 'A''s relcache manually, then next time the > relcache::rd_partcheck will be updated to the expected one which does not > include partition constraint check of table 'C'. > (ATTACH partition has the same behaviour that relcache::rd_partcheck will not > be updated immediately)
An DETACH PARTITION example which shows the relcache::rd_partcheck is not invalidated immediately is: ----- parttable1 -> parttable2-> parttable3 create table parttable1 (a int, b int, c int) partition by list(a); create table parttable2 (a int, b int, c int) partition by list(b); create table parttable3 (a int, b int, c int); alter table parttable1 attach partition parttable2 for values in (1); alter table parttable2 attach partition parttable3 for values in (1); ----- -----INSERT a tuple into parttable3 which does not satisfy parttable1's partition constraint -----we will get an error ----- insert into parttable3 values(2,1,1); ERROR: new row for relation "parttable3" violates partition constraint DETAIL: Failing row contains (2, 1, 1). ----- ----- parttable1 is no longer the grandparent of parttable3. ----- I thought the partition constraint of parttable1 does not matter anymore ----- alter table parttable1 detach partition parttable2; ----- -----INSERT a tuple into parttable3 which does not satisfy parttable1's partition constraint ----- *** I expect a successful insertion, but it returns an error again. *** ----- insert into parttable3 values(2,1,1); ERROR: new row for relation "parttable3" violates partition constraint DETAIL: Failing row contains (2, 1, 1). RECONNECT ----- -----Reconnect the postgres which will invalidate the relcache ----- INSERT a tuple into parttable3 which does not satisfy parttable1's partition constraint ----- We succeeded this time as expected. ----- insert into parttable3 values(2,1,1); INSERT 0 1 Best regards, houzj
