On Mon, Mar 6, 2017 at 9:36 AM, Robert Haas <robertmh...@gmail.com> wrote:
> On Sat, Mar 4, 2017 at 1:52 PM, Peter Geoghegan <p...@bowt.ie> wrote:
> > In my opinion, we expose query id (and dbid, and userid) as the
> > canonical identifier for each pg_stat_statements entry, and have done
> > so for some time. That's the stable API -- not query text. I'm aware
> > of cases where query text was used as an identifier, but that ended up
> > being hashed anyway.
> > Query text is just for human consumption.
> Lukas evidently thinks otherwise, based on the original post.
I actually agree with Peter that the queryid+userid+dbid is the canonical
not the query text.
There is however value in parsing the query, e.g. to find out which
type something is, or to determine which table names a query references
(assuming one knows the search_path) programatically.
It is for that latter reason I'm interested in parsing the query, and
ambiguity that ? carries, since its also an operator.
Based on some hackery, I've previously built a little example script that
filters pg_stat_statements output: https://github.com/lfittl/pg_qtop#usage
This script currently breaks in complex cases of ? operators, since the
pg_stat_statements query text is ambiguous.
> > 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.
Yes, and I do think that making it easier to run EXPLAIN would be the
primary user-visible benefit in core.
I'd be happy to add a docs section showing how to use this, if there is
some consensus that its worth pursuing this direction.
Thanks for all the comments, appreciate the discussion.