Closed KevMurph closed 4 years ago
What version of pg_partman are you using? You can see this by running \dx
in psql. Also with daily partitioning, maintenance should be running at least once, preferrably twice, a day to properly keep ahead, even with a 30 day premake.
Would you also mind providing the \d+ tablename
output for the parent table so I can try to recreate things as you have them? Thank you.
keith@keith=# \dx
List of installed extensions
Name | Version | Schema | Description
---------------+---------+------------+--------------------------------------------------------------
dblink | 1.2 | public | connect to other PostgreSQL databases from within a database
pg_partman | 4.2.2 | partman | Extension to manage partitioned tables by time or ID
pgstattuple | 1.5 | public | show tuple-level statistics
pgtap | 1.1.0 | public | Unit testing for PostgreSQL
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
Hi Keith,
I should have included the version #. I am facing the issue with pg_partman 3.2.0.
Unfortunately, I can't share the \d+ output due to legal reasons. Can I help to provide a more specific set of information that you might want in order to look into the issue?
Regarding the requirement that maintenance be run 1-2x day for daily maintenance: could you elaborate on the technical reason for that? I'm quite curious. I noticed that the recommendation was there in the documentation within the context of trigger-based partitioning, but I didn't notice anything about that for native partitioning.
Thank you for your help!
This looks like a bug that was fixed in version 3.2.1. I'd missed the full macro string in the format() function of the inherit_table_properties() function which seems to be the exact error your getting.
v3.2.1 commit - https://github.com/pgpartman/pg_partman/commit/3ee672cc7af43b44794f29cd12c2fa592bb3aa08
So my recommendation is to update to at least that version to avoid this error. However, I'd recommend updating to the latest version if possible. If you stay on PG10 when updating to the latest version, there shouldn't be many changes to account for. However, when you upgrade to PG11+, there have been quite a few changes with how property inheritance is managed. I've tried to lay out all the details as much as possible in the CHANGELOG, so please review it carefully.
https://raw.githubusercontent.com/pgpartman/pg_partman/master/CHANGELOG.txt
As far as the maintenance run period, maintenance should be running at least as often as your partition interval. The entire purpose of the premake value is to keep child table creation ahead by at least that amount to account for the expected interval of data to be ingested. This is particularly critical while you are still on PG10 when using native partitioning because there is no DEFAULT partition to catch data that doesn't have a matching child. It will just throw an error and cause potential data loss if your application isn't set to handle that. I'd recommended running at least twice within the partition interval just to catch if maintenance happened to run at a time when it wouldn't have triggered a new partition to be created. This can sometimes happen with time-based partitioning depending on when the last bit of data was inserted vs the time maintenance ran. For integer-based partitioning your interval just has to be often enough to keep up with your data ingestion rate and that can vary widely.
I will give that a shot and report back soon with my findings. I believe the environment I am working in right now is on a freeze until after holiday vacation, but all of that context is really helpful. Also, we are planning an upgrade to PG11 (and 12 soon after), so that information you gave will help me to target potential problem areas for the upgrade.
P.S. I (and I'm sure many others) greatly appreciate the work you do to benefit the PostgreSQL community. You're great!
The bug you encountered should have been fixed. If you're still having issues, please feel free to comment with additional details and it can be reopened.
@keithf4 Hey Keith! I fully intended to follow up with the results of this, but it took a while to get the update deployed given the holiday break, and this issue fell off my radar once everything was working again. Your suggestion fixed the problem! Thank you very much for your contribution to the community through this project!
Hello, I have had a recurring problem with pg_partman maintenance running against PostgreSQL 10. I feel as though sometimes the issue crops up more often when I wait a week between running maintenance for the partitions, but that may be a coincidence.
Summary: Run maintenance fails with 'Unterminated Format Specifier'
Here are the run_maintenance logs and part_config setup:
Run maintenance error:
NOTICE: run_maint: parent_table: my_schema_name.my_table_name, v_last_partition: my_table_name_p2019_12_23 NOTICE: run_maint: v_current_partition_timestamp: 2019-12-12 19:08:52.48493+00, v_max_time_parent:
NOTICE: run_maint before loop: current_partition_timestamp: 2019-12-12 19:08:52.48493+00, v_premade_count: 10, v_sub_timestamp_min: , v_sub_timestamp_max:
NOTICE: run_maint: parent_table: my_schema_name.my_table_name, v_premade_count: 10, v_next_partition_timestamp: 2019-12-23 00:00:00+00
NOTICE: create_partition_time: v_partition_expression: activity_date
NOTICE: create_partition_time v_sql: CREATE TABLE my_schema_name.my_table_name_p2019_12_24 (LIKE my_schema_name.my_table_name INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING STORAGE INCLUDING COMMENTS INCLUDING INDEXES)
NOTICE: ALTER TABLE my_schema_name.my_table_name_p2019_12_24 SET TABLESPACE pg_default;
ERROR: unterminated format() type specifier
CONTEXT: PL/pgSQL function inherit_template_properties(text,text,text) line 105 at assignment
SQL statement "SELECT partman.inherit_template_properties(p_parent_table, v_parent_schema, v_partition_name)"
PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,boolean) line 216 at PERFORM
PL/pgSQL function partman.run_maintenance(text,boolean,boolean,boolean) line 241 at assignment
DETAIL:
HINT: For a single "%" use "%%".
CONTEXT: PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,boolean) line 441 at RAISE
PL/pgSQL function partman.run_maintenance(text,boolean,boolean,boolean) line 241 at assignment
DETAIL:
HINT:
CONTEXT: PL/pgSQL function partman.run_maintenance(text,boolean,boolean,boolean) line 358 at RAISE
part_config:
-[ RECORD 1 ]--------------+--------------------------- parent_table | my_schema_name.my_table_name control | activity_date partition_type | native partition_interval | 1 day constraint_cols | premake | 30 optimize_trigger | 4 optimize_constraint | 30 epoch | none inherit_fk | t retention | 60 days retention_schema | retention_keep_table | t retention_keep_index | f infinite_time_partitions | t datetime_string | YYYY_MM_DD 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 | my_schema_name.template_my_table_name publications |
Has anyone seen this before? Any recommendations?