I'm attempting to merge a table into a partition, the merge works but the performance isn't good. I've read this link, in particular this quote....
Before running the ATTACH PARTITION command, it is recommended to create a CHECK constraint on the table to be attached describing the desired partition constraint. That way, the system will be able to skip the scan to validate the implicit partition constraint. Without such a constraint, the table will be scanned to validate the partition constraint while holding an ACCESS EXCLUSIVE lock on the parent table. One may then drop the constraint after ATTACH PARTITION is finished, because it is no longer necessary.
Here's the SQL I'm using....
ALTER TABLE mess_201811 ADD CONSTRAINT const_201811 CHECK ( loadedtime >= DATE '2018-11-01' AND loadedtime < DATE '2018-11-23' ); ALTER TABLE mess ATTACH PARTITION mess_201811 FOR VALUES FROM ('2018-11-01') TO ('2018-11-23'); ALTER TABLE mess_201811 DROP CONSTRAINT const_201811 ;
As close as possible I've copied my SQL from the link mentioned. The three commands all work without problem, the issue is the time it takes. Adding the constraint takes about 60 seconds (fair enough, there are about 2 million records in the table). However the ATTACH PARTITION command also takes about 60 seconds, this suggests to me that table is being scanned a second time validate the partition. My understand was that with the CHECK constraint in place the ATTACH should be a simple DDL statement and would only take a fraction of a second.
The problem is that ATTACH PARTITION requires an exclusive lock on the table mess which prevents inserting records for 60 seconds.
I'm using postgres v10.5. the definition for table mess ends with
PARTITIONED BY loadedtime;
At the time of my testing there were no other users on the database and no lock issues.
Following jjanes solution, I've checked the original documentation, the example quoted does use a NOT NULL field constraint although it make no mention of this being mandatory. I'm hoping that the additional constraint won't have a noticeable effect on normal inserts to this table.