pg.ddx.io  pgpartman/pg_partman GitHub issues and pull requests  
help / color / mirror / Atom feed
From: kurjam (@kurjam) <noreply+kurjam@github.com>
To: pgpartman/pg_partman <noreply+pgpartman-pg_partman@github.com>
Subject: [pgpartman/pg_partman] issue #865: inconsistent timezone handling in case of retention
Date: Fri, 08 May 2026 08:05:05 +0000
Message-ID: <gh-pgpartman-pg_partman-865@github.com> (raw)

Timezone calculation of partition boundaries is off when dealing with non-gmt timezones when retention is set.

pg_partman version 5.3.1
postgresql 18.1

Although it might seem like an edge case, our different services have stumbled on this multiple times. The problem is that all sorts of client drivers set the timezone of a session to GMT silently while the db server timezone is something else. So when the pg_partman_bgw maintenance runs it does so in server time. 

This is an example from one of our dbs:

[local]:5432 postgres@test> \d+ public.test

Partitions: 
            test_p20260501 FOR VALUES FROM ('2026-04-30 19:00:00-05') TO ('2026-05-31 19:00:00-05'),
            test_p20260601 FOR VALUES FROM ('2026-05-31 19:00:00-05') TO ('2026-06-30 19:00:00-05'),
            test_p20260701 FOR VALUES FROM ('2026-06-30 19:00:00-05') TO ('2026-07-31 19:00:00-05'),
            test_p20260801 FOR VALUES FROM ('2026-07-31 19:00:00-05') TO ('2026-08-31 19:00:00-05'),
            test_p20260901 FOR VALUES FROM ('2026-08-31 19:00:00-05') TO ('2026-09-30 19:00:00-05')

These boundaries were being treated just fine (e.g. new partitions created by run_maintenance()) when we initially deployed this service on pg_partman 4.7.0. Now after upgrading to 5.3.1 things have gone downhill.

If the retention setting is on, subsequent partitions created by pg_partman will look like this:

[local]:5432 postgres@test> \d+ public.test
Partitions: 
            test_p20260501 FOR VALUES FROM ('2026-04-30 19:00:00-05') TO ('2026-05-31 19:00:00-05'),
            test_p20260601 FOR VALUES FROM ('2026-05-31 19:00:00-05') TO ('2026-06-30 19:00:00-05'),
            test_p20260701 FOR VALUES FROM ('2026-06-30 19:00:00-05') TO ('2026-07-31 19:00:00-05'),
            test_p20260801 FOR VALUES FROM ('2026-07-31 19:00:00-05') TO ('2026-08-31 19:00:00-05'),
            test_p20260901 FOR VALUES FROM ('2026-08-31 19:00:00-05') TO ('2026-09-30 19:00:00-05'),
            test_p20261001 FOR VALUES FROM ('2026-10-01 00:00:00-05') TO ('2026-11-01 00:00:00-05'),
            test_p20261101 FOR VALUES FROM ('2026-11-01 00:00:00-05') TO ('2026-12-01 00:00:00-05'),
            test_p20261201 FOR VALUES FROM ('2026-12-01 00:00:00-05') TO ('2027-01-01 00:00:00-05')

So this means there is a gap between test_p20260901  and test_p20261001.
I've tracked down the change responsible - https://github.com/pgpartman/pg_partman/issues/660 which introduced this piece of code:

<img width="1143" height="183" alt="Image" src="https://github.com/user-attachments/assets/1be3cd41-8319-4e58-bd14-e9e39e3c948a"; />

The calculate_time_partition_info() does not honor partition boundaries, it applies date_trunc to existing boundary, which is wrong.

view thread (2+ messages)  latest in thread

Message-ID: <gh-pgpartman-pg_partman-865@github.com>
Permalink:  ../gh-pgpartman-pg_partman-865@github.com/

 · 


This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox