Re: [PATCH] Use $ parameters as replacement characters for pg_stat_statements

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sat, Mar 4, 2017 at 1:52 PM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>> I'd be in favor of a change
>> that makes it easier to copy and paste a query, to run EXPLAIN and so
>> on. Lukas probably realizes that there are no guarantees that the
>> query text that appears in pg_stat_statements will even appear as
>> normalized in all cases. The "sticky entry" stuff is intended to
>> maximize the chances of that happening, but it's still generally quite
>> possible (e.g. pg_stat_statements never swaps constants in a query
>> like "SELECT 5, pg_stat_statements_reset()"). This means that we
>> cannot really say that this buys us a machine-readable query text
>> format, at least not without adding some fairly messy caveats.

> Well, Lukas's original suggestion of using $n for a placeholder would
> do that, unless there's already a $n with the same numerical value,
> but Andres's proposal to use $-n or $:n would not.

I don't much like the $-n or $:n proposals, as those are infringing on
syntax space we might wish we had back someday. $:n also could cause
confusion with psql variable substitution.

I wonder if it would improve matters to use $n, but starting with the
first number after the actual external Param symbols in the query.
(This presumes that we can identify the last in-use external Param number
reasonably efficiently. struct Query doesn't really expose that ---
although it might not be unreasonable to add a field to do so.
In practice pg_stat_statements could perhaps look aside to find
that out, say from EState.es_param_list_info->numParams.)

In a situation where you wanted to try to actually execute the query,
this might be fairly convenient since you could do PREPARE with the
original external Params followed by the ones pg_stat_statements had
abstracted from constants. Of course, guessing the types of those
might be nontrivial. I wonder whether we should change the printout
to look like '$n::type' not just '$n'.

A variant that might make it easier to read would be to start the
numbering of the abstracted params from $100, or some other number
much larger than the last external Param, thus creating a pretty
clear distinction between the two. But that would break the
hypothetical use-case of building a prepared statement directly
from the query text, or at least make it mighty inconvenient.

regards, tom lane