question on maintenance failure / incorrect setup?

[screig]

I create a table, entered lots of data and now I am having a bit of trouble

CREATE TABLE testing_schema.tbl_test_bars ( product_id INT NOT NULL, 
bar_data_time timestamp without time zone NOT NULL, 
open_px REAL, 
high_px REAL,
low_px REAL,
close_px REAL,
volume INTEGER,
barlen INTEGER,
PRIMARY KEY (product_id, bar_data_time)  )  
PARTITION BY RANGE(bar_data_time);  

And I used partition manager (4.2.2) like so:

SELECT partman.create_parent('testing_schema.tbl_test_bars', 'bar_data_time','native', 'monthly',p_start_partition:= '2000-01-01');

I am a bit confused if I had to include the constraints_column argument. Anyway I added lots of data between now and 2000 and that's fine. I then added data in the future and all the data had ended up in the default table. On running the following:

CALL partman.run_maintenance_proc(p_analyze := false, p_debug := true);

I get the error below (note this runs for about an hour before popping out the message). The first line indicated to me that I should maybe have explicitly set the constraints column?

NOTICE:  Given parent table (testing_schema.tbl_test_bars) not set up for constraint management (constraint_cols is NULL)
NOTICE:  run_maint: parent_table: testing_schema.tbl_test_bars, v_premade_count: -865, v_next_partition_timestamp: 2028-12-01 00:00:00+00
NOTICE:  create_partition_time: v_partition_expression: bar_data_time
NOTICE:  create_partition_time v_sql: CREATE TABLE testing_schema.tbl_test_bars_p2029_01 (LIKE testing_schema.tbl_test_bars INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING STORAGE INCLUDING COMMENTS  INCLUDING INDEXES) 

ERROR:  updated partition constraint for default partition would be violated by some row
CONTEXT: while executing command on 10.40.12.12:5432
SQL statement "ALTER TABLE testing_schema.tbl_test_bars ATTACH PARTITION testing_schema.tbl_test_bars_p2029_01 FOR VALUES FROM ('2029-01-01 00:00:00+00') TO ('2029-02-01 00:00:00+00')"
PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,boolean) line 233 at EXECUTE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean,boolean) line 280 at assignment
SQL statement "SELECT partman.run_maintenance('testing_schema.tbl_test_bars', p_jobmon := 't', p_debug := 't', p_analyze := 'f')"
PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean,boolean) line 44 at EXECUTE
DETAIL: 
HINT: 
CONTEXT: PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,boolean) line 459 at RAISE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean,boolean) line 280 at assignment
SQL statement "SELECT partman.run_maintenance('testing_schema.tbl_test_bars', p_jobmon := 't', p_debug := 't', p_analyze := 'f')"
PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean,boolean) line 44 at EXECUTE
DETAIL: 
HINT: 
CONTEXT:  PL/pgSQL function partman.run_maintenance(text,boolean,boolean,boolean) line 398 at RAISE
SQL statement "SELECT partman.run_maintenance('testing_schema.tbl_test_bars', p_jobmon := 't', p_debug := 't', p_analyze := 'f')"
PL/pgSQL function partman.run_maintenance_proc(integer,boolean,boolean,boolean) line 44 at EXECUTE
SQL state: P0001

What have I done wrong here?

running on

PostgreSQL 12.1 (Ubuntu 12.1-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit

[keithf4]

The constraint_cols option is only needed if you will be doing a lot of queries that will be doing extensive filtering on columns other than the partition key column. This only works for static data as well that doesn't change after a certain period of time. I wouldn't worry about this feature yet unless you're seeing big query performance issues on your partition sets. More info on this feature is on my blog here - https://www.keithf4.com/managing-constraint-exclusion-in-table-partitioning/

The only reason you're seeing that NOTICE in your logs is because you set the p_debug flag to true. It's just a debug notice for tracing down issues and can be ignored.

The reason you're seeing that error is because of the data that got inserted to the default child table. Native partitioning in postgresql cannot add another child table if that child table's constraint would include data that is in the default partition. The default partition essentially has an anti-constraint that is the opposite of all the current children.

You can use the partition_data_time() function (or preferably the partition_data_proc() procedure) to move that data out of the default and into the proper child table(s). However, be aware that there could now be a gap in your child tables if that data was far further into the future than your latest actual child table. So it's good to first check that the data in the default is valid data before actually moving it out to real children to become a permanent part of the partition set. Next version of pg_partman will have a function to fix those gaps.

If you are frequently having data go to the default, you may want to adjust the premake value either via the create_parent() function when making the partition set, or in the part_config table afterwards to ensure you're creating the proper child table to account for your normal window of data.

[screig]

Hi

Thanks for your response, and for writing this great plugin! I found your statement below to be key to my understanding of whats going on here.

Native partitioning in postgresql cannot add another child table if that child table's constraint would include data that is in the default partition.

I was under the naive impression that the function partman.run_maintenance would manage the creation of partitions completely. However I now see that's not the case and believe I have a better understanding of how the process works, am on the right track here:

The expectation is that data will be inserted in a linear fashion and as the 'latest' point in the series approaches (through insertions over time) the end of the partitions, or rather decreases the size of vacant partitions from the target number (p_premake) then new partitions will be created?

I also suppose that the functionality will only add partitions in the increasing time axis (or more specifically the increasing p_control column)? By that say for example I was inserting data backwards (eg 2010, 2009, 2008) the maintenance function would i take it not create partitions as I approached p_start_partition?

Thanks Sean

[keithf4]

I didn't have it try to automatically handle migrating data out of the default because I didn't want an accidental ingestion of a lot of bad data suddenly creating a bunch of children and have the cleanup be a lot harder. Also having data purposely go there is an anti-pattern to table partitioning, so having to manually check what goes there is the desired action.

Yes, your understanding is correct. So you definitely want to have your premake window be large enough to handle your normal window of expected data. It's fine to keep it large if your window needs it since it will only ever be creating one new child table at a time as needed. And by default, whether to premake a new future child is based on the data that exists in the partition set, not what the "current" date/time is. If you need it to always create future time partitions no matter what data exists, you can set the infinte_time_partitions option in part_config to true. Again, it's not enabled by default because I didn't feel always creating new partitions when you have no new data should be the norm. And for integer/id based partitioning, it's always based on the existing data.

And right now it only it only works for future data. I guess for time partitioning, being able to go both directions could possibly work. But the main use-case for partitioning in PostgreSQL is to make dropping off old data easier by just dropping old tables vs delete statements. I guess if a valid use-case for going backward in time was presented, I could look into it more, but for now it's not a feature I plan on implementing. If you find yourself occasionally getting past data, you can just monitor the default partition and migrate it out if it's valid.