Horton

Re: queryId constant squashing does not support prepared statements

Sami Imseih <samimseih@gmail.com>

View thread
From: Sami Imseih <samimseih@gmail.com>
To: Álvaro Herrera <alvherre@kurilemu.de>
Cc: Michael Paquier <michael@paquier.xyz>, Dmitry Dolgov <9erthalion6@gmail.com>, Junwang Zhao <zhjwpku@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-05-27T22:05:39Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix typo in comment

  2. Make query jumbling also squash PARAM_EXTERN params

  3. Fix squashing algorithm for query texts

  4. pg_stat_statements: Fix parameter number gaps in normalized queries

Attachments

> > therefore, a user supplied query like this:
> > ```
> > select where $5 in ($1, $2, $3) and $6 = $4 and 1 = 2
> > ```
> >
> > will be normalized to:
> > ```
> > select where $1 in ($2 /*...*/) and $3 = $4 and $5 = $6
> > ```
>
> Hmm, interesting.
>
> I think this renumbering should not be a problem in practice; users with
> unordered parameters have little room to complain if the param numbers
> change on query normalization.  At least that's how it seems to me.
>
> If renumbering everything in physical order makes the code simpler, then
> I don't disagree.
>

It does make it simpler, otherwise we have to introduce O(n) behavior
to find eligible parameter numbers.

I've spent a bit of time looking at this, and I want to
propose the following patchset.

* 0001:

This is a normalization issue discovered when adding new
tests for squashing. This is also an issue that exists in
v17 and likely earlier versions and should probably be
backpatched.

The crux of the problem is if a constant location is
recorded multiple times, the values for $n don't take
into account the duplicate constant locations and end up
incorrectly incrementing the next value fro $n.

So, a query like

SELECT WHERE '1' IN ('2'::int, '3'::int::text)

ends up normalizing to

SELECT WHERE $1 IN ($3::int, $4::int::text)

I also added a few test cases as part of
this patch.

This does also feel like it should be backpatched.

* 0002:

Added some more tests to the ones initially proposed
by Dmitri in v3-0001 [0] including the "edge cases" which
led to the findings for 0001.


* 0003:

This fixes the normalization anomalies introduced by
62d712ec ( squashing feature ) mentioned here [1]

This patch therefore implements the fixes to track
the boundaries of an IN-list, Array expression.


* 0004: implements external parameter squashing.

While I think we should get all patches in for v18, I definitely
think we need to get the first 3 because they fix existing
bugs.

What do you think?


[0] https://www.postgresql.org/message-id/i635eozw2yjpzqxi5vgm4ceccqq3gv7ul4xj2xni2v6pfgtqlr%40vc5otquxmgjg
[1] https://www.postgresql.org/message-id/CAA5RZ0ts6zb-efiJ%2BK31Z_YDU%3DM7tHE43vv6ZBCqQxiABr3Yaw%40mail.gmail.com

--
Sami