Getting "updated partition constraint for default partition would be violated by some row" ERROR

[yaghyesh-fareye]

I plan to implement pg_partman and pg_cron for partitioning my table.

The table is created, the initial partitions are created, the cron job is scheduled, and new partitions are created at the scheduled time.
While inserting data is also partitioning into the correct partition and data which have no partition exists are going into the default partition. But after having any data in the default partition Job is facing an issue on creating a new partition.

This is the SQL query I used for creating new table and job. Detailed ERROR while running job is below.

`
alter table if exists parcel_event rename to parcel_event_old;

CREATE TABLE IF NOT EXISTS parcel_event_master (
id BIGINT NOT NULL,
order_no VARCHAR(255),
latitude VARCHAR(255),
carrier_status_time_gmt TIMESTAMP with TIME ZONE,
status_received_at VARCHAR(255),
value VARCHAR(255),
previous_status VARCHAR(255),
carrier_code VARCHAR(255),
longitude VARCHAR(255),
CONSTRAINT pk_parcel_event_master PRIMARY KEY (id, carrier_status_time_gmt)
) partition by range (carrier_status_time_gmt);

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;

SELECT partman.create_parent(p_parent_table => 'public.parcel_event_master',
p_control => 'carrier_status_time_gmt',
p_type => 'native',
p_interval=> '60',
p_premake => 2, p_jobmon => false);

CREATE EXTENSION pg_cron;

UPDATE partman.part_config
SET infinite_time_partitions = true,
retention = '3 months',
retention_keep_table=true
WHERE parent_table = 'public.parcel_event_master';
SELECT cron.schedule('* * * * *', $$CALL partman.run_maintenance_proc()$$);
`

ERROR: updated partition constraint for default partition would be violated by some row CONTEXT: SQL statement "ALTER TABLE public.parcel_event_master ATTACH PARTITION public.parcel_event_master_p2023_05_10_1836 FOR VALUES FROM ('2023-05-10 18:36:00+05:30') TO ('2023-05-10 18:37:00+05:30')" PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 249 at EXECUTE PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 284 at assignment SQL statement "SELECT partman.run_maintenance('public.parcel_event_master', p_jobmon := 't')" PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean) line 42 at EXECUTE DETAIL: HINT: CONTEXT: PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 511 at RAISE PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 284 at assignment SQL statement "SELECT partman.run_maintenance('public.parcel_event_master', p_jobmon := 't')" PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean) line 42 at EXECUTE DETAIL: HINT: CONTEXT: PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 413 at RAISE SQL statement "SELECT partman.run_maintenance('public.parcel_event_master', p_jobmon := 't')" PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean) line 42 at EXECUTE

[keithf4]

I noticed you've set the partition set to be partitioned by time, but you've set the interval to a plain integer value. This gets interpreted as 60 seconds

keith=# select '60'::interval;
 interval 
----------
 00:01:00
(1 row)

What is your intended interval value here?

[yaghyesh-fareye]

Yes, 60 seconds(1 min) is my intended interval value. I have no problem creating partitions on create_parent function run. I am getting error on running "CALL partman.run_maintenance_proc()"

[keithf4]

Ok, I just wanted to make sure. In the long run, I'd recommend being more specific in your interval value just to avoid any ambiguity with future changes. So set it to 1 min or 60 seconds.

So the issue is that for native partitioning, you cannot create a new child table if that child table's constraint matches any data that exists in the default. This limitation is with PostgreSQL itself, not pg_partman. The default is not without a constraint, it has an anti-constraint that is the opposite of all existing children. That anti-constraint is automatically updated when a new child table is added. You can see it by look at the \d+ default_table output.

So if you get this error, you must do one of two things

1. If it is valid data, it must be moved out to the proper child table before maintenance can continue. You can use the `partition_data_proc()1 procedure to do this. It handles swapping data around with a temp table to get around the above mentioned limitation.
2. If it is invalid data, delete it from the default

If this keeps happening, you need to adjust your premake window to accommodate your expected new data window. I noticed you dropped it down to 2. I'd recommend leaving it at the default of 4 for now. Or if you know your expected data window, ensure enough future partitions are made. Note the premake only handles future partitions. If you are getting data further in the past, you'll have to follow the next recommendation.

If data from any time period can come in, and you want to keep it, you will have to set up some sort of monitoring to watch for data going into the default and move it out. You can use the check_default() function to assist with this.

Also another thing to be aware of with this is the child table that is created via this method may leave gaps in the coverage of the child tables. You can use the partition_gap_file() function to fill in any missing child tables in the sequence between the oldest and newest.

Hopefully that helps!

[yaghyesh-fareye]

@keithf4

Thanks for the clarification.

I am trying to use partition_data_proc and partition_gap_fill (you can see below). I am seeing no problem with this. Can you suggest anything about this? Will this be a good and complete replacement for run_maintenance_proc? Should I use check_default as condition? How much p_batch should I configure?

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;

SELECT partman.create_parent(p_parent_table => 'public.parcel_event_master',
p_control => 'carrier_status_time_gmt',
p_type => 'native',
p_interval=> '60',
p_premake => 2);

CREATE EXTENSION pg_cron;

UPDATE partman.part_config
SET infinite_time_partitions = true,
retention = '3 months',
retention_keep_table=true
WHERE parent_table = 'public.parcel_event_master';
SELECT cron.schedule('* * * * ', $$CALL partman.partition_data_proc('public.parcel_event_master', p_batch := 1000)$$);
SELECT cron.schedule(' * * * *', $$SELECT * FROM partman.partition_gap_fill('public.parcel_event_master')$$);

[keithf4]

I'd really like some clarification before saying whether this is a good idea. 1 minute is a VERY small partition window. How much data are you expecting per minute? If you're going to be keeping 3 months of data, that means you're going to have at roughly 129600 (60*24*90) child tables and you'll be churning through the system catalog tables making quite a lot of DDL changes and possibly causing some bloat issues there over time that will need to be managed. I'd say maybe figure out an interval that would give you something starting around several hundred thousand rows per table. PG can handle that fine for most maintenance processes, which is one of the reasons for partitioning.

Also, again, you really want to open that premake up to be as close to your expected data window as possible. Right now you're only accounting for up to 2 minutes in the future. If you're frequently getting data in the default, that doesn't sound like it's the case.

[yaghyesh-fareye]

Apologies for the previous data provided, Here is what I want to do. I am expecting 1 million entries daily (30 million per month). I just want to store data optimally. There won't be running frequent read queries. There will always be live data, but I want good handling of past and future data.

@keithf4 May you help me get answers to the above-asked questions?

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;

SELECT partman.create_parent(p_parent_table => 'public.parcel_event_master',
p_control => 'carrier_status_time_gmt',
p_type => 'native',
p_interval=> 'monthly',
p_premake => 2);

CREATE EXTENSION pg_cron;

UPDATE partman.part_config
SET infinite_time_partitions = true,
retention = '3 months',
retention_keep_table=true
WHERE parent_table = 'public.parcel_event_master';
SELECT cron.schedule('* * * * ', $$CALL partman.partition_data_proc('public.parcel_event_master', p_batch := 1000)$$);
SELECT cron.schedule(' * * * *', $$SELECT * FROM partman.partition_gap_fill('public.parcel_event_master')$$);

[keithf4]

Sure, I just wanted to get a sense of what your goal was before answering it.

Monthly would probably work ok in this situation, but I'd say you could even go down to daily. That will help things like vacuum/analyze run a little more efficiently and with only a 3 month retention, you're only keeping around 90 partitions which is perfectly reasonable.

With that said, you could have your premake set to 30 or maybe even a little higher if you really expect to be getting data out that far ahead of "now". If you need to get started covering a little bit more in the past as well, you can use the p_start_partition flag to create_parent() to start a bit further back as well.

So, if we were to run this today, I'd recommend starting off with this

SELECT partman.create_parent(p_parent_table => 'public.parcel_event_master',
p_control => 'carrier_status_time_gmt',
p_type => 'native',
p_interval=> '1 day',
p_premake => 30,
p_start_partition => '2023-05-01');

That will result in this much larger set of starting partitions

\d+ parcel_event_master
                                              Partitioned table "public.parcel_event_master"
         Column          |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
-------------------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id                      | bigint                   |           | not null |         | plain    |             |              | 
 order_no                | character varying(255)   |           |          |         | extended |             |              | 
 latitude                | character varying(255)   |           |          |         | extended |             |              | 
 carrier_status_time_gmt | timestamp with time zone |           | not null |         | plain    |             |              | 
 status_received_at      | character varying(255)   |           |          |         | extended |             |              | 
 value                   | character varying(255)   |           |          |         | extended |             |              | 
 previous_status         | character varying(255)   |           |          |         | extended |             |              | 
 carrier_code            | character varying(255)   |           |          |         | extended |             |              | 
 longitude               | character varying(255)   |           |          |         | extended |             |              | 
Partition key: RANGE (carrier_status_time_gmt)
Indexes:
    "pk_parcel_event_master" PRIMARY KEY, btree (id, carrier_status_time_gmt)
Partitions: parcel_event_master_p2023_05_01 FOR VALUES FROM ('2023-05-01 00:00:00-04') TO ('2023-05-02 00:00:00-04'),
            parcel_event_master_p2023_05_02 FOR VALUES FROM ('2023-05-02 00:00:00-04') TO ('2023-05-03 00:00:00-04'),
            parcel_event_master_p2023_05_03 FOR VALUES FROM ('2023-05-03 00:00:00-04') TO ('2023-05-04 00:00:00-04'),
            parcel_event_master_p2023_05_04 FOR VALUES FROM ('2023-05-04 00:00:00-04') TO ('2023-05-05 00:00:00-04'),
            parcel_event_master_p2023_05_05 FOR VALUES FROM ('2023-05-05 00:00:00-04') TO ('2023-05-06 00:00:00-04'),
            parcel_event_master_p2023_05_06 FOR VALUES FROM ('2023-05-06 00:00:00-04') TO ('2023-05-07 00:00:00-04'),
            parcel_event_master_p2023_05_07 FOR VALUES FROM ('2023-05-07 00:00:00-04') TO ('2023-05-08 00:00:00-04'),
            parcel_event_master_p2023_05_08 FOR VALUES FROM ('2023-05-08 00:00:00-04') TO ('2023-05-09 00:00:00-04'),
            parcel_event_master_p2023_05_09 FOR VALUES FROM ('2023-05-09 00:00:00-04') TO ('2023-05-10 00:00:00-04'),
            parcel_event_master_p2023_05_10 FOR VALUES FROM ('2023-05-10 00:00:00-04') TO ('2023-05-11 00:00:00-04'),
            parcel_event_master_p2023_05_11 FOR VALUES FROM ('2023-05-11 00:00:00-04') TO ('2023-05-12 00:00:00-04'),
            parcel_event_master_p2023_05_12 FOR VALUES FROM ('2023-05-12 00:00:00-04') TO ('2023-05-13 00:00:00-04'),
            parcel_event_master_p2023_05_13 FOR VALUES FROM ('2023-05-13 00:00:00-04') TO ('2023-05-14 00:00:00-04'),
            parcel_event_master_p2023_05_14 FOR VALUES FROM ('2023-05-14 00:00:00-04') TO ('2023-05-15 00:00:00-04'),
            parcel_event_master_p2023_05_15 FOR VALUES FROM ('2023-05-15 00:00:00-04') TO ('2023-05-16 00:00:00-04'),
            parcel_event_master_p2023_05_16 FOR VALUES FROM ('2023-05-16 00:00:00-04') TO ('2023-05-17 00:00:00-04'),
            parcel_event_master_p2023_05_17 FOR VALUES FROM ('2023-05-17 00:00:00-04') TO ('2023-05-18 00:00:00-04'),
            parcel_event_master_p2023_05_18 FOR VALUES FROM ('2023-05-18 00:00:00-04') TO ('2023-05-19 00:00:00-04'),
            parcel_event_master_p2023_05_19 FOR VALUES FROM ('2023-05-19 00:00:00-04') TO ('2023-05-20 00:00:00-04'),
            parcel_event_master_p2023_05_20 FOR VALUES FROM ('2023-05-20 00:00:00-04') TO ('2023-05-21 00:00:00-04'),
            parcel_event_master_p2023_05_21 FOR VALUES FROM ('2023-05-21 00:00:00-04') TO ('2023-05-22 00:00:00-04'),
            parcel_event_master_p2023_05_22 FOR VALUES FROM ('2023-05-22 00:00:00-04') TO ('2023-05-23 00:00:00-04'),
            parcel_event_master_p2023_05_23 FOR VALUES FROM ('2023-05-23 00:00:00-04') TO ('2023-05-24 00:00:00-04'),
            parcel_event_master_p2023_05_24 FOR VALUES FROM ('2023-05-24 00:00:00-04') TO ('2023-05-25 00:00:00-04'),
            parcel_event_master_p2023_05_25 FOR VALUES FROM ('2023-05-25 00:00:00-04') TO ('2023-05-26 00:00:00-04'),
            parcel_event_master_p2023_05_26 FOR VALUES FROM ('2023-05-26 00:00:00-04') TO ('2023-05-27 00:00:00-04'),
            parcel_event_master_p2023_05_27 FOR VALUES FROM ('2023-05-27 00:00:00-04') TO ('2023-05-28 00:00:00-04'),
            parcel_event_master_p2023_05_28 FOR VALUES FROM ('2023-05-28 00:00:00-04') TO ('2023-05-29 00:00:00-04'),
            parcel_event_master_p2023_05_29 FOR VALUES FROM ('2023-05-29 00:00:00-04') TO ('2023-05-30 00:00:00-04'),
            parcel_event_master_p2023_05_30 FOR VALUES FROM ('2023-05-30 00:00:00-04') TO ('2023-05-31 00:00:00-04'),
            parcel_event_master_p2023_05_31 FOR VALUES FROM ('2023-05-31 00:00:00-04') TO ('2023-06-01 00:00:00-04'),
            parcel_event_master_p2023_06_01 FOR VALUES FROM ('2023-06-01 00:00:00-04') TO ('2023-06-02 00:00:00-04'),
            parcel_event_master_p2023_06_02 FOR VALUES FROM ('2023-06-02 00:00:00-04') TO ('2023-06-03 00:00:00-04'),
            parcel_event_master_p2023_06_03 FOR VALUES FROM ('2023-06-03 00:00:00-04') TO ('2023-06-04 00:00:00-04'),
            parcel_event_master_p2023_06_04 FOR VALUES FROM ('2023-06-04 00:00:00-04') TO ('2023-06-05 00:00:00-04'),
            parcel_event_master_p2023_06_05 FOR VALUES FROM ('2023-06-05 00:00:00-04') TO ('2023-06-06 00:00:00-04'),
            parcel_event_master_p2023_06_06 FOR VALUES FROM ('2023-06-06 00:00:00-04') TO ('2023-06-07 00:00:00-04'),
            parcel_event_master_p2023_06_07 FOR VALUES FROM ('2023-06-07 00:00:00-04') TO ('2023-06-08 00:00:00-04'),
            parcel_event_master_p2023_06_08 FOR VALUES FROM ('2023-06-08 00:00:00-04') TO ('2023-06-09 00:00:00-04'),
            parcel_event_master_p2023_06_09 FOR VALUES FROM ('2023-06-09 00:00:00-04') TO ('2023-06-10 00:00:00-04'),
            parcel_event_master_p2023_06_10 FOR VALUES FROM ('2023-06-10 00:00:00-04') TO ('2023-06-11 00:00:00-04'),
            parcel_event_master_p2023_06_11 FOR VALUES FROM ('2023-06-11 00:00:00-04') TO ('2023-06-12 00:00:00-04'),
            parcel_event_master_p2023_06_12 FOR VALUES FROM ('2023-06-12 00:00:00-04') TO ('2023-06-13 00:00:00-04'),
            parcel_event_master_p2023_06_13 FOR VALUES FROM ('2023-06-13 00:00:00-04') TO ('2023-06-14 00:00:00-04'),
            parcel_event_master_p2023_06_14 FOR VALUES FROM ('2023-06-14 00:00:00-04') TO ('2023-06-15 00:00:00-04'),
            parcel_event_master_p2023_06_15 FOR VALUES FROM ('2023-06-15 00:00:00-04') TO ('2023-06-16 00:00:00-04'),
            parcel_event_master_p2023_06_16 FOR VALUES FROM ('2023-06-16 00:00:00-04') TO ('2023-06-17 00:00:00-04'),
            parcel_event_master_p2023_06_17 FOR VALUES FROM ('2023-06-17 00:00:00-04') TO ('2023-06-18 00:00:00-04'),
            parcel_event_master_p2023_06_18 FOR VALUES FROM ('2023-06-18 00:00:00-04') TO ('2023-06-19 00:00:00-04'),
            parcel_event_master_default DEFAULT

Apply the updates for retention and infinite child table creation

UPDATE partman.part_config
SET infinite_time_partitions = true,
retention = '3 months',
retention_keep_table=true
WHERE parent_table = 'public.parcel_event_master';

That should hopefully cover your typical time window of data that your normally expecting

Now the questions is, do you expect there to be a lot of data coming in outside your 3 month window?

Is that data in the past? Then perhaps consider increasing your retention period to help cover that. With modern versions of PG, 365 child partitions for a year of coverage is fully manageable.

Is that data in the future? Consider increasing the premake value if that is going to be a frequent and expected occurrence.

After all that, if you're still expecting data outside the expected retention and premake value, I would first start by just setting up monitoring of the default with check_default() and find out WHY that as happening and see if it can be fixed. Maybe consider removing the default table and flat out returning an error if someone attempts to insert data outside the expected window. Or if you don't want to lose that data, at least find out why it's happening and see if it can be fixed before you move on to automating around "bad" data. Garbage in, garbage out.

Your idea of running the partition_data and partition_gap_fill functions not bad, but running every minute is way too aggressive. Maybe once or twice a day at most during off-peak hours. Also it does not replace run_maintenance calls. You should still be doing that to premake partitions since partitioning the data out of the default does cause locks on that data while it's happening. And, again, I would highly recommend finding a way to not do that first by doing the evaluations I mentioned above. Try and get things running normally for the best experience long term.