unexpected behaviour generating queryid

From: mailinglists <lists(at)database(dot)one>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: unexpected behaviour generating queryid
Date: 2026-09-10 09:47:31
Message-ID: 9E79BFEC-0A4D-4DBC-BE5E-624AF6BF0844@database.one
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

Something has changed along the line, possibly already in PG17, in the way query’s are generated that makes it possible for queryid’s to clash.
The crux of the issue seems to be that tables are now identified by their alias, and only their alias, no longer their old.
This may become especially problematic with the upcoming plan_advice extension, since that relies completely on the queryid.

I prepared a small example that shows a clash.
I am assuming that the compute_query_id is on auto.
select * from pg_settings where name like '%compute%';
— setup test tables
drop table if exists test_measurements;
create table test_measurements (
id serial primary key,
name text,
measurement_ts timestamptz default now(),
value float
);
drop table if exists test_users;
create table test_users (
id int,
fullname text,
address text,
creation_moment timestamptz default now()
);
insert into test_measurements (name, measurement_ts, value)
select 'voltage' name,
now() - interval '1 minute'*n measurement_ts,
floor(random()*100) + n value
from generate_series(1,1000) n;

insert into test_users (id, fullname, address, creation_moment)
values (0,'backend', 'Office 69', '2026-01-01 00:00:00+00'::timestamptz)
, (1,'root', 'Office 69', '2026-01-02 10:10:10+00'::timestamptz);

-- testing queries
explain verbose select * from test_measurements a where id IN (66, 77, 88, 99);
explain verbose select * from test_users a where id IN (0, 1);
explain verbose select * from test_measurements xx where id IN (66, 77, 88, 99);
The first two queries give me an identical queryid.
Tested on PG19Beta3

Ellert.

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Ajin Cherian 2026-09-10 09:52:10 Re: PSQL schema "describe" \dn is not escaping quotes
Previous Message Ashutosh Bapat 2026-09-10 09:44:22 Re: PGQ catalog representation and pg_dump support

Browse pgsql-bugs by date

  From Date Subject
Previous Message Álvaro Herrera 2026-09-10 09:24:55 Re: REPACK (CONCURRENTLY) doesn't handle invalid indexes