Question on history_default table

[vanimesh]

Hi,

What does this _default tables mean? Are those intermediate tables between the main tables(history/trends) and the partitioned tables?

ALso i could see some errors which stopped partitioning as below
ERROR: updated partition constraint for default partition would be violated by some row
CONTEXT: SQL statement "ALTER TABLE public.history ATTACH PARTITION public.history_p2021_10_08 FOR VALUES FROM ('1633651200') TO ('1633737600')"

Can someone help here pls?

[vanimesh]

Can someone help me pls...

[Doctorbal]

@vanimesh ,

As stated in the documentation here:

With PostgreSQL version 11 it is possible to create a "default" partition. This stores rows that do not fall into any existing partition's range. This is ideal since the partitioned range might not include specific data which the default will then pick up. This is automatically done with pg_partman.

Regarding your issue from the error message "updated partition constraint for default partition would be violated by some row", please review the official pg_partman plugin issues, in particular issue 247 and issue 230 as those appear to directly relate to the message you are running into.

From the link above, issue 247, it appears you have a default partition set already and that data needs to be moved properly.

In addition the following article will help you.

Essentially move the data out of the default partition or if this is a fresh install delete the default partition and run through the procedure again.

Regards,
Andreas

[vanimesh]

Excellent Andreas. Thank you for your kind help. Will check the solutions.

[vanimesh]

For the error "updated partition constraint for default partition would be violated by some row". Cant we identlfy the row which is violating ?

[Doctorbal]

@vanimesh ,

You can identify what entries are blocking the partitioned table from being created by running following:

SELECT * from history_default WHERE clock > 1633651200 AND clock < 1633737600

This should tell you what entries are left over. You can then migrate them to an empty table.

As I am unfamiliar with your setup I suggest you to perform the following step, conceptually, here - https://github.com/Doctorbal/zabbix-postgres-partitioning#change-zabbix-history-tables-from-monthly-to-daily-with-pgpartman.

The concept here is to create a new table, unpartition the old table, insert data from the old table into the new one and then migrate it back.

From my perspective this will resolve the issue as what I believe happened is when you created the table there were some entries still present in the default column.

Alternatively you can delete all data in the default column but I advise to only perform this is you believe any data in the default column is not being used.

If you need more hands on help let me know, however I suggest to provide more insight to how you performed each step.

Regards,
Andreas

[vanimesh]

1. Take the backup of the default partition into a different table.
   create table history_log_bkp as select * from history_log_default where date > 7th day;

2. Then delete the data in the main table - history_log
   truncate public.history_log;

3. Run the partition maintainence
   SELECT partman.run_maintenance('public.history_log');
   THis will create new empty partitions for future dates I suppose.

4. copy the data from the bkp table to actual table
   insert into history_log select * from history_log_bkp;

5. After 7 Days, delete/truncate the data in default partition
   truncate public.history_log_default;

These are the steps which we are going to follow. THis would be followed for all the history and trends tables.

Also a question. Where exactly can we check the data retention configurations ?

[Doctorbal]

@vanimesh ,

I believe the steps look reasonable but why delete after 7 days when the partition steps do it automatically?

In your postgresql.conf file ensure you set the background worker to automatically run - https://github.com/Doctorbal/zabbix-postgres-partitioning#postgresqlconf

### General
shared_preload_libraries = 'pg_partman_bgw' # (this change requires restart)

### Partitioning & pg_partman settings
enable_partition_pruning = on
pg_partman_bgw.interval = 3600
pg_partman_bgw.role = 'zabbix'
pg_partman_bgw.dbname = 'zabbix'
pg_partman_bgw.analyze = off
pg_partman_bgw.jobmon = on

From the example above it runs every 3600 seconds / 1 hour; i.e. partitions are created or dropped.

Data retention configuration is on the partman.part_config table:

zabbix=# \x
Expanded display is on.
zabbix=# SELECT * FROM partman.part_config WHERE parent_table = 'public.history';
-[ RECORD 1 ]--------------+--------------------------------
parent_table               | public.history
control                    | clock
partition_type             | native
partition_interval         | 1 day
constraint_cols            | 
premake                    | 7
optimize_trigger           | 4
optimize_constraint        | 30
epoch                      | seconds
inherit_fk                 | t
retention                  | 8 day
retention_schema           | 
retention_keep_table       | f
retention_keep_index       | t
infinite_time_partitions   | f
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             | partman.template_public_history
publications               | 
inherit_privileges         | f
constraint_valid           | t

[vanimesh]

why delete after 7 days when the partition steps do it automatically?"WHich step are you speaking about?" Is it the step 5? The partitioning automatically takes care of this table too ?

[Doctorbal]

@vanimesh ,

Apologies as I was a bit confused on your step 5:

After 7 Days, delete/truncate the data in default partition

Why do you plan on deleting any data when it should technically be empty based on your steps?

Regards,
Andreas

[Doctorbal]

@vanimesh ,

Any update on your end?

Can I go ahead and close this inquiry?

Regards,
Andreas

[vanimesh]

Going to try the steps tomorrow. Will keep you posted on the results :-)

[vanimesh]

We tried the steps and we are receiving the below warning and no partitions are getting created

"zabbix_db=# SELECT partman.run_maintenance('public.history_log');
WARNING: Attempt to drop final partition in partition set public.history_log as part of retention policy. If you see this message multiple times for the same table, advise reviewing retention policy and/or data entry into the partition set. Also consider setting "infinite_time_partitions = true" if there are large gaps in data insertion.)."

Our partitions are not created from October. Do we need to change option/setting infinite_time_partitions=true and run the same command again ?

[Doctorbal]

@vanimesh ,

I am unfamiliar with that error but based on the log output I suggest to try that.

If you still run into issues I suggest you open a ticket with the official pg_partman project.

[vanimesh]

Hi @Doctorbal, making the above config work. But the only thing is we will have to set it back to 'f'. But today when I was checking I found that the partitions are not getting created automatically. Is there something I have to check still?

[Doctorbal]

@vanimesh it is very difficult to understand what went wrong given you haven't provided me the steps you went through when automatically creating partitions. I suggest you follow the documentation again and ensure the steps are properly performed.