Future data inserted into default partition and maintenance failed.

[pdev91]

We're running pg_partman on our Zabbix installation with the history tables partitioned daily and the trends tables partitioned monthly. For some reason, a host tried to insert data from 1/12/2029 on 1/12/2024. We don't have that many partitions created so it went into the default partitions for some of these tables. The background worker ran and it failed which seems to have broke partitioning. I'm new to the partitioning/pg_partman world so not totally confident in the ins/outs but I assume this failed because it didn't have the trends_p2029_01 partition already created. We had the next 4 days (premake is 4 for the dailies) created (13-16th) and data was inserted as normal but the Jan 17th partition wasn't created on the 13th and so on which led to millions and even billions of records to be left in the default partition.

I've since removed the 2029 data and started moving the data with partition_data_proc() but this takes a lot of time. Looking for prevention measures.

My questions:

1. Is this normal behavior where if a partition doesn't exist it goes into the default and pg_partman can't pull it out?
2. Is there a way to prevent this from happening besides creating a lot of partitions?
3. How/when are new partitions created?
4. Is the default partition necessary? Wondering if we'd prefer that inserts fail for partitions not existing due to bad data as opposed to trying to move the data from the default.

Error:

2024-01-12 20:43:31.503 CST [3600180] ERROR:  updated partition constraint for default partition "trends_default" would be violated by some row
        CONTEXT: SQL statement "ALTER TABLE public.trends ATTACH PARTITION public.trends_p2029_01 FOR VALUES FROM ('1861941600') TO ('1864620000')"
        PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 259 at EXECUTE
        PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 284 at assignment
        SQL statement "SELECT "partman".run_maintenance(p_analyze := false, p_jobmon := true)"
        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(p_analyze := false, p_jobmon := true)"
        DETAIL:
        HINT:
2024-01-12 20:43:31.503 CST [3600180] CONTEXT:  PL/pgSQL function partman.run_maintenance(text,boolean,boolean) line 413 at RAISE
        SQL statement "SELECT "partman".run_maintenance(p_analyze := false, p_jobmon := true)"
2024-01-12 20:43:31.506 CST [1074141] LOG:  background worker "????" (PID 3600180) exited with exit code 1

Table (post fix):

storezabbixdb=# \d+ trends
                                                Partitioned table "public.trends"
  Column   |       Type       | Collation | Nullable |        Default        | Storage | Compression | Stats target | Description
-----------+------------------+-----------+----------+-----------------------+---------+-------------+--------------+-------------
 itemid    | bigint           |           | not null |                       | plain   |             |              |
 clock     | integer          |           | not null | 0                     | plain   |             |              |
 num       | integer          |           | not null | 0                     | plain   |             |              |
 value_min | double precision |           | not null | '0'::double precision | plain   |             |              |
 value_avg | double precision |           | not null | '0'::double precision | plain   |             |              |
 value_max | double precision |           | not null | '0'::double precision | plain   |             |              |
Partition key: RANGE (clock)
Indexes:
    "trends_pkey" PRIMARY KEY, btree (itemid, clock)
Partitions: trends_p2023_09 FOR VALUES FROM (1693544400) TO (1696136400),
            trends_p2023_10 FOR VALUES FROM (1696136400) TO (1698814800),
            trends_p2023_11 FOR VALUES FROM (1698814800) TO (1701410400),
            trends_p2023_12 FOR VALUES FROM (1701410400) TO (1704088800),
            trends_p2024_01 FOR VALUES FROM (1704088800) TO (1706767200),
            trends_p2024_02 FOR VALUES FROM (1706767200) TO (1709272800),
            trends_p2024_03 FOR VALUES FROM (1709272800) TO (1711947600),
            trends_p2024_04 FOR VALUES FROM (1711947600) TO (1714539600),
            trends_p2024_05 FOR VALUES FROM (1714539600) TO (1717218000),
            trends_p2024_06 FOR VALUES FROM (1717218000) TO (1719810000),
            trends_p2024_07 FOR VALUES FROM (1719810000) TO (1722488400),
            trends_p2024_08 FOR VALUES FROM (1722488400) TO (1725166800),
            trends_p2024_09 FOR VALUES FROM (1725166800) TO (1727758800),
            trends_p2024_10 FOR VALUES FROM (1727758800) TO (1730437200),
            trends_default DEFAULT

part_config for public.trends:

storezabbixdb=# select * from partman.part_config where parent_table = 'public.trends';
-[ RECORD 1 ]--------------+-------------------------------
parent_table               | public.trends
control                    | clock
partition_type             | native
partition_interval         | 1 mon
constraint_cols            |
premake                    | 4
optimize_trigger           | 4
optimize_constraint        | 30
epoch                      | seconds
inherit_fk                 | t
retention                  | 12 month
retention_schema           |
retention_keep_table       | f
retention_keep_index       | t
infinite_time_partitions   | f
datetime_string            | YYYY_MM
automatic_maintenance      | on
jobmon                     | t
sub_partition_set_full     | f
undo_in_progress           | f
trigger_exception_handling | f
upsert                     |
trigger_return_null        | t
template_table             | partman.template_public_trends
publications               |
inherit_privileges         | f
constraint_valid           | t
subscription_refresh       |
drop_cascade_fk            | f
ignore_default_data        | f

[keithf4]

1. Is this normal behavior where if a partition doesn't exist it goes into the default and pg_partman can't pull it out?

Yes, this is the normal behavior when a default partition exists

2. Is there a way to prevent this from happening besides creating a lot of partitions?

If you want to have a default partition to avoid losing data, then any data outside the window of available children will get that data inserted to the default. There is no way to prevent it if the available child does not exist.

3. How/when are new partitions created?

New partitions are made when the run_maintenance() or run_maintenance_proc() function is called via some scheduler app (cron, etc). Or the background worker has been set up to run them for you. Check if shared_preload_libraries has pg_partman_bgw defined in it and if there are any other pg_partman_bgw.* parameters defined in your postgresql.conf

4. Is the default partition necessary? Wondering if we'd prefer that inserts fail for partitions not existing due to bad data as opposed to trying to move the data from the default.

The default partition is the standard behavior for pg_partman since I wanted its standard behavior to not be to lose data. Whether it is necessary or not is entirely up to you and if you'd rather PostgreSQL throw an error when data outside of the available children is inserted, you are free to drop it. partman will only create the default table when initially setting up the partition set and in versions 5.x and later there is now an option whether to create the default as a parameter to the create_parent() function.

Hopefully that answered all you're questions! Please let me know if you need further assistance.

[pdev91]

I appreciate your responses. This definitely helps me out greatly!

[keithf4]

Also for reference, versions 4.x and earlier did take into account data in the default partition when creating future partitions. This is why you were missing the partitions you needed and it was trying to make partitions in 2029. For version 5.x and later, data in the default partition is ignored for normal child table creation and if a new child table tries to be made that would match data in the default, that is when an error will be thrown. So hopefully if you're able to upgrade to 5.0, data going into the default will be less of a problem if you'd like to keep the default partition around.