Error when run_maintenance wasn't run for weeks

[vinkaga]

Hello,

I have a weekly partitioned table but I didn't know that run_maintenance() had to be executed on a regular basis. As a result, new partitions weren't created. Today, when I tried executing it, I get the following error

mydb=> select partman.run_maintenance();
ERROR:  updated partition constraint for default partition would be violated by some row
CONTEXT: SQL statement "ALTER TABLE public.hist ATTACH PARTITION public.hist_p2022w06 FOR VALUES FROM ('2022-02-07 00:00:00+00') TO ('2022-02-14 00:00:00+00')"
PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,boolean) line 243 at EXECUTE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean,boolean) line 283 at assignment
DETAIL:
HINT:
CONTEXT: PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,boolean) line 482 at RAISE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean,boolean) line 283 at assignment
DETAIL:
HINT:
CONTEXT:  PL/pgSQL function partman.run_maintenance(text,boolean,boolean,boolean) line 415 at RAISE

How do I fix it?

[keithf4]

It means you have data in your default partition that either needs to be moved to the proper child or deleted if it is bad data.

You can move the data with the partition_data_*() functions or procedure. This will create any necessary child tables to hold that data, but note it requires a lock on those rows because it has to move that data around between tables. Also note that if it creates a child table far ahead of the existing latest one, you may have a gap in the sequence of child tables. That gap can be filled with the partition_gap_fill() function.

[vinkaga]

@keithf4, just so that I understand (since I can't afford to lose the data), if I run

select partman.partition_data_time('public.hist', 1000);

It will take data from default partition, 1000 rows at a time, and push them into the target partition table (creating them as necessary). The tables (default and target) will be locked during the batch of 1000 but that lock will be released after batch completion. Before the next batch, a new lock will be acquired and the process will repeat.

Is my understanding correct?

[keithf4]

Please double-check the documentation for this function.

The second parameter (p_batch_count) is now many times it will loop through the interval given by the third parameter (p_batch_interval). The default for p_batch_count is 1 so it would only move enough rows to create one new child table. The default for p_batch_interval is the partition interval, so you typically don't have to set this for native partitioning.

For native partitioning, the interval that is given currently cannot be lower than the partition interval because it has to move all of the rows for the new child table out of the default before it can make it (they're moved to a temp table first, then the new child table is created, then the rows are moved there).

[keithf4]

Also, partition_data_time() is a function, so even if you run multiple batches in a single call, it will still do the entire process in a single transaction. If you'd like it to commit and release those rows after each batch, look at the partition_data_proc() procedure instead.

[vinkaga]

After reading the documentation further, the following seems to be a better command.

call partman.partition_data_proc('public.hist', null, 10000);

I am using PG 12, so, partition_data_prod instead of partition_data.py. Each weekly data to be moved has about 10 million rows. So, batch of 10000 would move approximately 1000 rows at a time.

[vinkaga]

It ran mostly successfully but did generate the following error after batch

mydb=> call partman.partition_data_proc('public.hist', null, 5000);
NOTICE:  Batch: 1, Rows moved: 2718682
NOTICE:  Batch: 2, Rows moved: 3914275
NOTICE:  Batch: 3, Rows moved: 3194248
NOTICE:  Batch: 4, Rows moved: 5284213
ERROR:  updated partition constraint for default partition would be violated by some row
CONTEXT: SQL statement "ALTER TABLE public.hist ATTACH PARTITION public.hist_p2022w10 FOR VALUES FROM ('2022-03-07 00:00:00+00') TO ('2022-03-14 00:00:00+00')"
PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,boolean) line 243 at EXECUTE
SQL statement "SELECT partman.create_partition_time(p_parent_table, v_partition_timestamp, p_analyze)"
PL/pgSQL function partman.partition_data_time(text,integer,interval,numeric,text,boolean,text) line 258 at PERFORM
SQL statement "SELECT partman.partition_data_time ('public.hist', p_lock_wait := '0', p_order := 'ASC', p_analyze := false)"
PL/pgSQL function partman.partition_data_proc(text,text,integer,integer,text,text,integer,integer,boolean) line 84 at EXECUTE
DETAIL:
HINT:
CONTEXT:  PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,boolean) line 482 at RAISE
SQL statement "SELECT partman.create_partition_time(p_parent_table, v_partition_timestamp, p_analyze)"
PL/pgSQL function partman.partition_data_time(text,integer,interval,numeric,text,boolean,text) line 258 at PERFORM
SQL statement "SELECT partman.partition_data_time ('public.hist', p_lock_wait := '0', p_order := 'ASC', p_analyze := false)"
PL/pgSQL function partman.partition_data_proc(text,text,integer,integer,text,text,integer,integer,boolean) line 84 at EXECUTE

Any suggestions?

[keithf4]

As I said, the third argument is the partition interval, not a number of rows. It's trying to interpret that value '5000' as a time interval.

For native partitioning, you cannot lower the partition interval that is moved, so it has to move at least 1 week's worth of data before it can make the child table.

[vinkaga]

I am looking at the documentation

partition_data_proc(p_parent_table text, p_interval text DEFAULT NULL, p_batch int DEFAULT NULL, p_wait int DEFAULT 1, p_source_table text DEFAULT NULL, p_order text DEFAULT 'ASC', p_lock_wait int DEFAULT 0, p_lock_wait_tries int DEFAULT 10, p_quiet boolean DEFAULT false, p_ignored_columns text[] DEFAULT NULL)

The second seems to be the interval while the third is the batch count. Note that I switched to partition_data_proc from partition_data_time .

[keithf4]

Apologies, was looking at partition_data_time(). Just realized I reversed those between the function and procedure.

Only thing I can think of is maybe something inserted data again to the default that needs to go into the new child table that it's trying to create?

[keithf4]

Checking this seems likely since now() is week 10 and that's the table that failed

keith@keith=# select extract('week' from now());
 extract 
---------
      10
(1 row)

[vinkaga]

My understanding is that the issue is related to the current week where the data is being inserted into the default and it isn't able to create a corresponding weekly partition.

Perhaps the problem can be solved by skipping the current weekly partition and creating future weekly partitions. Once, the new data starts going to a new weekly partition, the current week's data can be sent to appropriate partitions by another call to partition_data_proc.

Would it work?

[keithf4]

Yes that was going to be my recommendation. If normal maintenance still isn't able to make future partitions, you could make one manually that is ahead of the data in your default. Should only need to do one. Just be sure that the naming pattern matches and you should be fine.

[vinkaga]

How do I create a future partitions without creating the current one? Do I just create them manually?

[keithf4]

Yes, you can make a new child table as the PG documentation shows - https://www.postgresql.org/docs/14/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

Or, the way pg_partman does it is it first creates the table using the CREATE TABLE ... (LIKE ... INCLUDING ...) syntax and then alters the table to ATTACH it to the parent. The statement generation starts at the below link in the code if you're curious, but it has a lot of other cases to handle as well

https://github.com/pgpartman/pg_partman/blob/master/sql/functions/create_partition_time.sql#L177

[vinkaga]

I created the next weekly tables. Keeping this thread open till next week. Hopefully, everything works fine then.

[vinkaga]

I reran partition_data_proc today and it moved last week's data to the corresponding partition table. Subsequently, I was able to execute

select partman.run_maintenance();

successfully.
Thank for your help!

[keithf4]

Great to hear!