Prepared statement wrong results with default behaviour and partitioning

TimescaleDB and PostgreSQL
1

Problem observed with pure postgres(“PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20231218 (Red Hat 11.4.1-3), 64-bit”)
, but same is true for hypertables.

drop table test2;

create table test2 

(time timestamptz not null, tag_id bigint, a int, b int, c int, d int, e int, 

f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, 

p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int)

partition by range (time)

;

create index on test2 (tag_id, time);

SELECT partman.create_parent( p_parent_table => 'public.test2',

p_control => 'time',

p_interval=> '4h');



insert into test2 (select '2024-06-25T00:00:00Z'::timestamptz + make_interval(mins => i/10000), i%10000, i, i+1, i+2, i+3, i+4, i+5, i+6, i+7, i+8, i+9, i+10, i+11, i+12, i+13, i+14, i+15, i+16, i+17, i+18, i+19, i+20, i+21, i+22, i+23, i+24, i+25 from (select generate_series(0,60*10000-1) as i) a);

insert into test2 (select '2024-06-25T01:00:00Z'::timestamptz + make_interval(mins => i/10000), i%10000, i, i+1, i+2, i+3, i+4, i+5, i+6, i+7, i+8, i+9, i+10, i+11, i+12, i+13, i+14, i+15, i+16, i+17, i+18, i+19, i+20, i+21, i+22, i+23, i+24, i+25 from (select generate_series(0,60*10000-1) as i) a);

insert into test2 (select '2024-06-25T02:00:00Z'::timestamptz + make_interval(mins => i/10000), i%10000, i, i+1, i+2, i+3, i+4, i+5, i+6, i+7, i+8, i+9, i+10, i+11, i+12, i+13, i+14, i+15, i+16, i+17, i+18, i+19, i+20, i+21, i+22, i+23, i+24, i+25 from (select generate_series(0,60*10000-1) as i) a);

insert into test2 (select '2024-06-25T03:00:00Z'::timestamptz + make_interval(mins => i/10000), i%10000, i, i+1, i+2, i+3, i+4, i+5, i+6, i+7, i+8, i+9, i+10, i+11, i+12, i+13, i+14, i+15, i+16, i+17, i+18, i+19, i+20, i+21, i+22, i+23, i+24, i+25 from (select generate_series(0,60*10000-1) as i) a);

insert into test2 (select '2024-06-25T04:00:00Z'::timestamptz + make_interval(mins => i/10000), i%10000, i, i+1, i+2, i+3, i+4, i+5, i+6, i+7, i+8, i+9, i+10, i+11, i+12, i+13, i+14, i+15, i+16, i+17, i+18, i+19, i+20, i+21, i+22, i+23, i+24, i+25 from (select generate_series(0,60*10000-1) as i) a);

insert into test2 (select '2024-06-25T05:00:00Z'::timestamptz + make_interval(mins => i/10000), i%10000, i, i+1, i+2, i+3, i+4, i+5, i+6, i+7, i+8, i+9, i+10, i+11, i+12, i+13, i+14, i+15, i+16, i+17, i+18, i+19, i+20, i+21, i+22, i+23, i+24, i+25 from (select generate_series(0,60*10000-1) as i) a);

insert into test2 (select '2024-06-25T06:00:00Z'::timestamptz + make_interval(mins => i/10000), i%10000, i, i+1, i+2, i+3, i+4, i+5, i+6, i+7, i+8, i+9, i+10, i+11, i+12, i+13, i+14, i+15, i+16, i+17, i+18, i+19, i+20, i+21, i+22, i+23, i+24, i+25 from (select generate_series(0,60*10000-1) as i) a);

insert into test2 (select '2024-06-25T07:00:00Z'::timestamptz + make_interval(mins => i/10000), i%10000, i, i+1, i+2, i+3, i+4, i+5, i+6, i+7, i+8, i+9, i+10, i+11, i+12, i+13, i+14, i+15, i+16, i+17, i+18, i+19, i+20, i+21, i+22, i+23, i+24, i+25 from (select generate_series(0,60*10000-1) as i) a);

insert into test2 (select '2024-06-25T08:00:00Z'::timestamptz + make_interval(mins => i/10000), i%10000, i, i+1, i+2, i+3, i+4, i+5, i+6, i+7, i+8, i+9, i+10, i+11, i+12, i+13, i+14, i+15, i+16, i+17, i+18, i+19, i+20, i+21, i+22, i+23, i+24, i+25 from (select generate_series(0,60*10000-1) as i) a);

CALL partman.run_maintenance_proc();

CALL partman.partition_data_proc('public.test2');

VACUUM ANALYZE public.test2;

prepare tst1(int, date) as

select tst.* from test2 tst

where tst.tag_id = $1 and tst.time = $2

;

explain (analyze, verbose, costs, buffers)

execute tst1(1, '2024-06-25 00:00:00+00') --7 times

;

execute tst1(1, '2024-06-25 12:00:00+00'); -- wrong result
2

(I was led here via a complaint about run-time pruning via LinkedIn.)

Do you expect the final statement to return 0 rows because of the timestamp being 12 hours ahead? If so, note that the PREPARE statement is using the DATE type and the 12 hours is cast away when casting the TIMESTAMPTZ to DATE. Maybe using a TIMESTAMPTZ as the 2nd parameter type in the PREPARE statement is what you want?

Otherwise, I’m not clear on the nature of the complaint as you’ve not stated your expected results.

David

3

Hi David,

Yes, looks like my mistake with types lead to it. Thank you!