Re: Question related to partitioning with pg_partman

[sud <suds1434@xxxxxxxxx>]

On Sat, Mar 9, 2024 at 3:41 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 3/8/24 00:23, sud wrote:
>
> Starting a new thread...
>
> Something interesting and not sure if its expected behaviour as below.
> We are also confused a bit here.
>
> In the below example we created two partitioned tables on timestamptz
> type columns with different time zones and the child partitions are
> created appropriately with boundaries as one mid night to next mid night
> of a day and so on. But when we change the time zone and query the  data
> dictionary views again, it shows the start and end of the partition
> boundary as not midnights but different times of the day's values.
>
> So I was wondering if this can cause us any unforeseen issues in the
> long run while creating the partitions though partman and persisting the
> data into the tables from the end users then querying those and having
> queries properly partitioned pruned?
> or
> should we always set the local timezone as UTC always before running or
> calling the pg_partman/pg_cron process which creates the partitions?
> Mainly in a database which serves global users sitting across multiple
> timezones. And same thing while inserting data into the table, we should
> use UTC timezone conversion function. Can you please confirm.

'2024-03-07 00:00:00+00' and '2024-03-06 19:00:00-05' are the same time
as is '2024-03-07 00:00:00-05' and '2024-03-07 05:00:00+00'.

Still I would think for sanity sake you would want to stick with UTC.

Thank you so much Adrian.

In my example in the first post, I see, if someone connected to a RDS Postgres database and run the create partition command using pg_partman by setting the timezone as "UTC", the 7th march partition looks to be spanned from "7th march midnight" to "8th march midnight", when queried the partition_experession from the data dictionary view. Which is correct.

And same information if someone querying by setting the timezone as EST is showing spanning from "6th march 7PM" to "7th March 7PM". And this can cause sometimes the partition may shift to other days all together. Similar differences happen if creating the partitions using EST timezone initially and then querying the data dictionary from UTC timezone.

So my question was, if in these types of scenarios, we should follow a standard approach of setting the timezone as UTC in such a type of global user use case, while the system can persist data from multiple users sitting across different time zones? So that the boundary(start and end time) of each of the range partitions will be set as consistent in one timezone across all the partitioned tables?

And even while inserting the data , should we set the timezone to first UTC and do the data load ?

******* Partition created by pg_partman by setting timezone as UTC ***************

UTC

Partition_name                         Partition_expression
test_timestamp_p2024_03_07 FOR VALUES FROM ('2024-03-07 00:00:00+00') TO ('2024-03-08 00:00:00+00')

when queried the partition_expression using EST ..

EST

Partition_name                         Partition_expression
test_timestamp_p2024_03_07 FOR VALUES FROM ('2024-03-06 19:00:00-05') TO ('2024-03-07 19:00:00-05')


******* Partition created by pg_partman by setting timezone as EST ***************

EST

Partition_name                         Partition_expression
test_timestamp2_p2024_03_07 FOR VALUES FROM ('2024-03-07 00:00:00-05') TO ('2024-03-08 00:00:00-05')

when queried the partition_expression using UTC ..

UTC

Partition_name                         Partition_expression
test_timestamp2_p2024_03_07 FOR VALUES FROM ('2024-03-07 05:00:00+00') TO ('2024-03-08 05:00:00+00')

*******

Also i see both the "setting" and "reset_val" is showing as local timezone only. If we set the timezone to a different value than the local timezone then it gets updated on the "setting". 

Regards

Sud