partman.create_parent overlap issue on trends and trends_uint

[elekgeek]

Hello,

Actually, know not how to thank you for your work, I have never worked with postgresql, however you made it real easy to go with partitioning on it :)

Anyway, I have these packages installed:

pcp-pmda-postgresql-5.2.5-4.el8.x86_64
postgresql13-libs-13.3-2PGDG.rhel8.x86_64
postgresql13-13.3-2PGDG.rhel8.x86_64
postgresql13-server-13.3-2PGDG.rhel8.x86_64

When executing:

SELECT partman.create_parent('public.trends', 'clock', 'native', 'monthly', null, 12, 'on', null, true, 'seconds');

I get the following:

ERROR:  partition "trends_p2022_05" would overlap partition "trends_p2022_04"
CONTEXT: SQL statement "ALTER TABLE public.trends ATTACH PARTITION public.trends_p2022_05 FOR VALUES FROM ('1651352400') TO ('1654030800')"
PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 249 at EXECUTE
PL/pgSQL function partman.create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean) line 473 at assignment
DETAIL:
HINT:
CONTEXT: PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 479 at RAISE
PL/pgSQL function partman.create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean) line 473 at assignment
DETAIL:
HINT:
CONTEXT:  PL/pgSQL function partman.create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean) line 771 at RAISE

The same applies to trends_uint, hence I am unable to continue 👯‍♂️

[Doctorbal]

Hello @elekgeek ,

I am glad you find this information useful.

I have not tested this on a CentOS environment nor on PostgreSQL v13 (only 11 and below).

I do not see the partman package in your list although the command appear to work. Which package is it included in in CentOS from your list?

Can you provide me a DESCRIBE TABLE output of your public.trends table?

zabbix=# \d+ public.trends
                                       Table "public.trends"
  Column   |     Type      | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------------+-----------+----------+---------+---------+--------------+-------------
 itemid    | bigint        |           | not null |         | plain   |              |
 clock     | integer       |           | not null | 0       | plain   |              |
 num       | integer       |           | not null | 0       | plain   |              |
 value_min | numeric(16,4) |           | not null | 0.0000  | main    |              |
 value_avg | numeric(16,4) |           | not null | 0.0000  | main    |              |
 value_max | numeric(16,4) |           | not null | 0.0000  | main    |              |
Partition key: RANGE (clock)
Indexes:
    "trends_pkey" PRIMARY KEY, btree (itemid, clock)
Partitions: trends_p2020_06 FOR VALUES FROM (1590969600) TO (1593561600),
            trends_p2020_07 FOR VALUES FROM (1593561600) TO (1596240000),
            trends_p2020_08 FOR VALUES FROM (1596240000) TO (1598918400),
            trends_p2020_09 FOR VALUES FROM (1598918400) TO (1601510400),
            trends_p2020_10 FOR VALUES FROM (1601510400) TO (1604188800),
            trends_p2020_11 FOR VALUES FROM (1604188800) TO (1606780800),
            trends_p2020_12 FOR VALUES FROM (1606780800) TO (1609459200),
            trends_p2021_01 FOR VALUES FROM (1609459200) TO (1612137600),
            trends_p2021_02 FOR VALUES FROM (1612137600) TO (1614556800),
            trends_p2021_03 FOR VALUES FROM (1614556800) TO (1617235200),
            trends_p2021_04 FOR VALUES FROM (1617235200) TO (1619827200),
            trends_p2021_05 FOR VALUES FROM (1619827200) TO (1622505600),
            trends_p2021_06 FOR VALUES FROM (1622505600) TO (1625097600),
            trends_p2021_07 FOR VALUES FROM (1625097600) TO (1627776000),
            trends_p2021_08 FOR VALUES FROM (1627776000) TO (1630454400),
            trends_p2021_09 FOR VALUES FROM (1630454400) TO (1633046400),
            trends_p2021_10 FOR VALUES FROM (1633046400) TO (1635724800),
            trends_p2021_11 FOR VALUES FROM (1635724800) TO (1638316800),
            trends_p2021_12 FOR VALUES FROM (1638316800) TO (1640995200),
            trends_p2022_01 FOR VALUES FROM (1640995200) TO (1643673600),
            trends_p2022_02 FOR VALUES FROM (1643673600) TO (1646092800),
            trends_p2022_03 FOR VALUES FROM (1646092800) TO (1648771200),
            trends_p2022_04 FOR VALUES FROM (1648771200) TO (1651363200),
            trends_p2022_05 FOR VALUES FROM (1651363200) TO (1654041600),
            trends_p2022_06 FOR VALUES FROM (1654041600) TO (1656633600),
            trends_default DEFAULT

What are your NTP settings on your server (timedatectl)? Are you synced properly? What timezone are you using?

Your output for 2022-05 says EPOCH timestamp 1651352400, which is April 30th, 21:00:00 UTC and ends 1654030800, which ends May 31st, 21:00:00 UTC. I am curious what your other partitions are set at and if they are consistently end at 21:00:00 on the last day of the month.

Regards, Andreas

[elekgeek]

Hello @Doctorbal

Thank you for your prompt answer.

zabbix=> \d+ public.trends
                                         Partitioned table "public.trends"
  Column   |       Type       | Collation | Nullable |        Default        | Storage | 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)
Number of partitions: 0

zabbix=> \d+ public.history
                                       Partitioned table "public.history"
 Column |       Type       | Collation | Nullable |        Default        | Storage | Stats target | Description
--------+------------------+-----------+----------+-----------------------+---------+--------------+-------------
 itemid | bigint           |           | not null |                       | plain   |              |
 clock  | integer          |           | not null | 0                     | plain   |              |
 value  | double precision |           | not null | '0'::double precision | plain   |              |
 ns     | integer          |           | not null | 0                     | plain   |              |
Partition key: RANGE (clock)
Indexes:
    "history_1" btree (itemid, clock)
Partitions: history_p2021_06_16 FOR VALUES FROM (1623790800) TO (1623877200),
            history_p2021_06_17 FOR VALUES FROM (1623877200) TO (1623963600),
            history_p2021_06_18 FOR VALUES FROM (1623963600) TO (1624050000),
            history_p2021_06_19 FOR VALUES FROM (1624050000) TO (1624136400),
            history_p2021_06_20 FOR VALUES FROM (1624136400) TO (1624222800),
            history_p2021_06_21 FOR VALUES FROM (1624222800) TO (1624309200),
            history_p2021_06_22 FOR VALUES FROM (1624309200) TO (1624395600),
            history_p2021_06_23 FOR VALUES FROM (1624395600) TO (1624482000),
            history_p2021_06_24 FOR VALUES FROM (1624482000) TO (1624568400),
            history_p2021_06_25 FOR VALUES FROM (1624568400) TO (1624654800),
            history_p2021_06_26 FOR VALUES FROM (1624654800) TO (1624741200),
            history_p2021_06_27 FOR VALUES FROM (1624741200) TO (1624827600),
            history_p2021_06_28 FOR VALUES FROM (1624827600) TO (1624914000),
            history_p2021_06_29 FOR VALUES FROM (1624914000) TO (1625000400),
            history_default DEFAULT

timezone settings in "/var/lib/pgsql/13/data/postgresql.conf" is timezone = 'Asia/Amman'

[root@zabbix ~]# date
Wed Jun 23 06:45:56 EEST 2021 --> correct

[root@opmanager ~]# timedatectl
               Local time: Wed 2021-06-23 06:45:58 EEST
           Universal time: Wed 2021-06-23 03:45:58 UTC
                 RTC time: Wed 2021-06-23 03:45:57
                Time zone: Asia/Amman (EEST, +0300)
System clock synchronized: no
              NTP service: inactive
          RTC in local TZ: no

I am puzzled on this too, coz I looked at the epoch yesterday, checked the timezone on the OS and database conf file, all OK, wondering why the time is what it is for trends*. the difference is 31 days, 0 hours, 0 minutes and 0 seconds. This should not introduce a problem after all.

[elekgeek]

[root@zabbix ~]# rpm -qa pg_partman*
pg_partman_13-4.5.1-1.rhel8.x86_64

[root@zabbix ~]# rpm -qa postgre*
postgresql13-libs-13.3-2PGDG.rhel8.x86_64
postgresql13-13.3-2PGDG.rhel8.x86_64
postgresql13-server-13.3-2PGDG.rhel8.x86_64

I deleted both tables, trends and trends_uint, recreated them, tried to partition them, I noticed that it is the same issue, Sunday, May 1, 2022 12:00:00 AM GMT+03:00 DST to Wednesday, June 1, 2022 12:00:00 AM GMT+03:00 DST these should not be conflicting. Do you have any idea what has to be done :)

The idea behind what I am trying to do is to use both, partitioning with timescaledb to gain both, space and tidy database.

zabbix=> SELECT partman.create_parent('public.trends', 'clock', 'native', 'monthly', null, 12, 'on', null, true, 'seconds');
ERROR:  partition "trends_p2022_05" would overlap partition "trends_p2022_04"
CONTEXT: SQL statement "ALTER TABLE public.trends ATTACH PARTITION public.trends_p2022_05 FOR VALUES FROM ('1651352400') TO ('1654030800')"
PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 249 at EXECUTE
PL/pgSQL function partman.create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean) line 473 at assignment
DETAIL:
HINT:
CONTEXT: PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 479 at RAISE
PL/pgSQL function partman.create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean) line 473 at assignment
DETAIL:
HINT:
CONTEXT:  PL/pgSQL function partman.create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean) line 771 at RAISE
zabbix=> SELECT partman.create_parent('public.trends_uint', 'clock', 'native', 'monthly', null, 12, 'on', null, true, 'seconds');
ERROR:  partition "trends_uint_p2022_05" would overlap partition "trends_uint_p2022_04"
CONTEXT: SQL statement "ALTER TABLE public.trends_uint ATTACH PARTITION public.trends_uint_p2022_05 FOR VALUES FROM ('1651352400') TO ('1654030800')"
PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 249 at EXECUTE
PL/pgSQL function partman.create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean) line 473 at assignment
DETAIL:
HINT:
CONTEXT: PL/pgSQL function create_partition_time(text,timestamp with time zone[],boolean,text) line 479 at RAISE
PL/pgSQL function partman.create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean) line 473 at assignment
DETAIL:
HINT:
CONTEXT:  PL/pgSQL function partman.create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean) line 771 at RAISE

[Doctorbal]

@elekgeek ,

Based on your timedatectl output it states you aren't synced via NTP.

Can you sync with the proper NTP servers first? Possible following Asia NTP pools available.

I am unfamiliar with using both pg_partman and timescaledb. Would that confict in any sense? Possibly. Use one or the other in my opinion.

For the history* tables is partitioning working fine?

Alternatively I would suggest to ask the developer of this 3rd party extension here and open an issue with Keith. He replies pretty quickly as well.

Let me know the results once you sync with NTP.

Regards, Andreas

[elekgeek]

Actually, I did open an issue over there already, as you mentioned Keith did reply and asked for the schema, I did mention that I am doing both pg_partman and timescaledb (complete madness). However, I tried the NTP suggestion, no luck yet. Let's see what Keith comes up with, also I think you might be correct about the timescaledb with pg_partman, however, partitioning works for history tables.. I checked if there is an overlap on the errors with trends, the Epoch are OK.

[Doctorbal]

@elekgeek , a definite weird scenario. Your trends and trends_unit table look appropriate from your output; the clock column matches and partition key are all appropriate.

What's your locale on the DB? E.g.

zabbix=# show lc_collate;
 lc_collate
-------------
 en_GB.UTF-8
(1 row)

zabbix=# show lc_time;
   lc_time
-------------
 en_GB.UTF-8
(1 row)

[elekgeek]

Here you go:

zabbix=> show lc_collate;
 lc_collate
-------------
 en_US.UTF-8
(1 row)

zabbix=> show lc_time;
   lc_time
-------------
 en_US.UTF-8
(1 row)

[Doctorbal]

@elekgeek ,

I would need to replicate your issue based on your environment. Might take me some time to get to it.

Can you try to set up a system all in the UTC timezone and go through the steps?

Regards, Andreas

[elekgeek]

Strange, it works OK with UTC... why not with GMT+03 ...

[Doctorbal]

First off, great to hear it works.

Secondly, I'm not sure. Sounds more like a system time issue opposed to a postgresql issue but I could be mistaken.

[elekgeek]

Thank your or your help, really appreciated indeed..

I am using rocky linux 8.4, now you make me wonder if it is rocky linux :] I better write Keith about this as well to see what he thinks.

Since it worked, can u plz tell me what difference is between "Deleting Designated Partitions" and "Partition Maintenance: Dropping/expiring old partitions" sections in the tutorial, since "retention_keep_index" is missing from the update statement in "Partition Maintenance: Dropping/expiring old partitions", why is that anyway? What am I supposed to do to ensure old partitions are automatically deleted, do I have to "partman.run_maintenance" all the time? which one should I issue to ensure automatic deletion of the old partitions? I am confused TBH.

BTW, timescaledb is running side by side with partman as an extension only, it is not possible to turn partitioned tables into hypertables :) I will try this setup for few days to see how things go before the timezone issue is resolved. Each time I try sth new, I must stumble on a weird issue, real lucky :D I am not sure which is better, go with partitioning or use timescaledb :s

[Doctorbal]

@elekgeek,

The Deleting Designated Partitions is just an example on how to drop partitions on a single table. The Partition Maintenance: Dropping/expiring old partitions section outlines all tables where you update the partman.part_config table with the settings.

Very good question around the retention_keep_index setting being missing. As stated in the pg_partman table docs

NOTE: This setting has no affect on native partitioning in PG11+. You cannot drop natively inherited child indexes.

Thus technically speaking the section in "Deleting Designated Partitions" where I set "retention_keep_index" is intended for audiences that use PostgreSQL version <11.

To ensure partitions are automatically deleted ensure your postgresql.conf file as mentioned in the docs has the parameters set:

### 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

These settings set the background worker to execute the pg_partman maintenance. In this case every 3600 seconds as the zabbix user on the zabbix database.

You could also use pg_jobmon extension if you'd like and although I have set it to on I don't have it installed (should probably update the docs to specify that in more detail..).

Good to hear about timescaledb. I have not explored that but hear very good performance results.

At the time pg_partman was a good solution and I am very happy I went with it. Azure PostgreSQL Flexible Servers support it as well for version 13 which is great.

[elekgeek]

Thank you for the explanations.

Yes, I would love to see the docs updated on pg_jobmon extension.

[elekgeek]

Keith might reply in a week or more, I will wait to see what he comes up with then. I will keep this issue open till the issue is resolved.

[Doctorbal]

@elekgeek , sounds good. Let's keep this open until we figure out root cause.

[elekgeek]

Hi Andreas,

I think Keith has figured the issue behind overlapping, plz take a look at this issue.

[Doctorbal]

@elekgeek ,

I read the responses by Keith and give him credit for figuring out the root cause.

Daylights Savings Time (DST) is indeed the culprit and that is unfortunate that there is no current resolution even by pinging the IANA team (still give it a try to see what they say...).

My suggestion for servers that are globally distributed is to use the UTC timezone. Using different timezones, unless absolutely necessary for your use case, can cause loads of issues.

We have 1000s of servers globally distributed and all use the UTC timezone; it makes log analyzing, monitoring and other pesky issues as such at a minimum. Ensuring you sync properly with a NTP server and using a universal timezone will solve a lot of your issues.

Regarding pg_partman vs timescaledb that is purely subjective. Indeed timescaledb compresses the data and saves you a lot of space but speaking from personal experience I enjoy the usage of pg_partman for our ~2000 NVPS Zabbix instance. If you are in the realms of 5-10k NVPS then maybe timescaledb would be better; I do not know as I haven't tested it at that level.

I wish you good luck in deciding.

I will go ahead and close this ticket as there is nothing else outstanding regarding your inquiry.