Re: Is partition pruning impacted by data type

Something interesting and not sure if expected behaviour is as below. We
are confused 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 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 values.

So I was wondering if this can cause us any unforeseen issues in the long
run while creating the partitions though partman or while persisting the
data into the tables from the end users? 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.

And while checking the timezone using the "show timezone" function it shows
the local timezone, so is there any way to see postgres DB the server
timezone?

SET SESSION TIME ZONE 'UTC';
CREATE TABLE test_timestamp (
ts TIMESTAMP,
tstz TIMESTAMPTZ) PARTITION BY RANGE (tstz);

SELECT partman.create_parent(
p_parent_table := 'public.test_timestamp',
p_control := 'tstz',
p_type := 'native',
p_interval := '1 day',
p_premake := 4,
p_start_partition => '2024-03-07 00:00:00'
);

UPDATE partman.part_config SET infinite_time_partitions = 'true' WHERE
parent_table = 'public.test_timestamp';

with recursive inh as (
select i.inhrelid, null::text as parent
from pg_catalog.pg_inherits i
join pg_catalog.pg_class cl on i.inhparent = cl.oid
join pg_catalog.pg_namespace nsp on cl.relnamespace = nsp.oid
where nsp.nspname = 'public'
and cl.relname = 'test_timestamp2'
union all
select i.inhrelid, (i.inhparent::regclass)::text
from inh
join pg_catalog.pg_inherits i on (inh.inhrelid = i.inhparent)
)
select c.relname as partition_name,
pg_get_expr(c.relpartbound, c.oid, true) as partition_expression
from inh
join pg_catalog.pg_class c on inh.inhrelid = c.oid
join pg_catalog.pg_namespace n on c.relnamespace = n.oid
left join pg_partitioned_table p on p.partrelid = c.oid
order by n.nspname, c.relname;

test_timestamp_default DEFAULT
test_timestamp_p2024_03_07 FOR VALUES FROM ('2024-03-07 00:00:00+00') TO
('2024-03-08 00:00:00+00')
test_timestamp_p2024_03_08 FOR VALUES FROM ('2024-03-08 00:00:00+00') TO
('2024-03-09 00:00:00+00')
test_timestamp_p2024_03_09 FOR VALUES FROM ('2024-03-09 00:00:00+00') TO
('2024-03-10 00:00:00+00')
test_timestamp_p2024_03_10 FOR VALUES FROM ('2024-03-10 00:00:00+00') TO
('2024-03-11 00:00:00+00')
test_timestamp_p2024_03_11 FOR VALUES FROM ('2024-03-11 00:00:00+00') TO
('2024-03-12 00:00:00+00')

SET SESSION TIME ZONE 'EST';

test_timestamp_default DEFAULT
test_timestamp_p2024_03_07 FOR VALUES FROM ('2024-03-06 *19:00:00-05*') TO
('2024-03-07 19:00:00-05')
test_timestamp_p2024_03_08 FOR VALUES FROM ('2024-03-07 *19:00:00-05*') TO
('2024-03-08 19:00:00-05')
test_timestamp_p2024_03_09 FOR VALUES FROM ('2024-03-08 *19:00:00-05*') TO
('2024-03-09 19:00:00-05')
test_timestamp_p2024_03_10 FOR VALUES FROM ('2024-03-09 *19:00:00-05*') TO
('2024-03-10 19:00:00-05')
test_timestamp_p2024_03_11 FOR VALUES FROM ('2024-03-10 *19:00:00-05*') TO
('2024-03-11 19:00:00-05')

***********************

SET SESSION TIME ZONE 'EST';

CREATE TABLE test_timestamp2 (
ts TIMESTAMP,
tstz TIMESTAMPTZ) PARTITION BY RANGE (tstz);

SELECT partman.create_parent(
p_parent_table := 'public.test_timestamp2',
p_control := 'tstz',
p_type := 'native',
p_interval := '1 day',
p_premake := 4,
p_start_partition => '2024-03-07 00:00:00'
);

UPDATE partman.part_config SET infinite_time_partitions = 'true' WHERE
parent_table = 'public.test_timestamp2';

with recursive inh as (
select i.inhrelid, null::text as parent
from pg_catalog.pg_inherits i
join pg_catalog.pg_class cl on i.inhparent = cl.oid
join pg_catalog.pg_namespace nsp on cl.relnamespace = nsp.oid
where nsp.nspname = 'public'
and cl.relname = 'test_timestamp2'
union all
select i.inhrelid, (i.inhparent::regclass)::text
from inh
join pg_catalog.pg_inherits i on (inh.inhrelid = i.inhparent)
)
select c.relname as partition_name,
pg_get_expr(c.relpartbound, c.oid, true) as partition_expression
from inh
join pg_catalog.pg_class c on inh.inhrelid = c.oid
join pg_catalog.pg_namespace n on c.relnamespace = n.oid
left join pg_partitioned_table p on p.partrelid = c.oid
order by n.nspname, c.relname;

test_timestamp2_default DEFAULT
test_timestamp2_p2024_03_07 FOR VALUES FROM ('2024-03-07 00:00:00-05') TO
('2024-03-08 00:00:00-05')
test_timestamp2_p2024_03_08 FOR VALUES FROM ('2024-03-08 00:00:00-05') TO
('2024-03-09 00:00:00-05')
test_timestamp2_p2024_03_09 FOR VALUES FROM ('2024-03-09 00:00:00-05') TO
('2024-03-10 00:00:00-05')
test_timestamp2_p2024_03_10 FOR VALUES FROM ('2024-03-10 00:00:00-05') TO
('2024-03-11 00:00:00-05')
test_timestamp2_p2024_03_11 FOR VALUES FROM ('2024-03-11 00:00:00-05') TO
('2024-03-12 00:00:00-05')

SET SESSION TIME ZONE 'UTC';

test_timestamp2_default DEFAULT
test_timestamp2_p2024_03_07 FOR VALUES FROM ('2024-03-07 *05:00:00+00*') TO
('2024-03-08 05:00:00+00')
test_timestamp2_p2024_03_08 FOR VALUES FROM ('2024-03-08 *05:00:00+00*') TO
('2024-03-09 05:00:00+00')
test_timestamp2_p2024_03_09 FOR VALUES FROM ('2024-03-09 *05:00:00+00*') TO
('2024-03-10 05:00:00+00')
test_timestamp2_p2024_03_10 FOR VALUES FROM ('2024-03-10 *05:00:00+00*') TO
('2024-03-11 05:00:00+00')
test_timestamp2_p2024_03_11 FOR VALUES FROM ('2024-03-11 *05:00:00+00*') TO
('2024-03-12 05:00:00+00')

Regards
Sud