Getting "updated partition constraint for default partition would be violated by some row" ERROR #525
Replies: 2 comments 5 replies
-
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
What is your intended interval value here? |
Beta Was this translation helpful? Give feedback.
-
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 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 So if you get this error, you must do one of two things
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 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 Hopefully that helps! |
Beta Was this translation helpful? Give feedback.
-
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
Beta Was this translation helpful? Give feedback.
All reactions