Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

On Wed, Nov 13, 2019 at 4:15 AM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> On Mon, Nov 11, 2019 at 05:37:30PM +0900, Michael Paquier wrote:
> > On Wed, Sep 11, 2019 at 06:30:22PM +0200, Julien Rouhaud wrote:
> > > The thing is that pg_stat_statements assigns a 0 queryid in the
> > > post_parse_analyze_hook to recognize utility statements and avoid
> > > tracking instrumentation twice in case of utility statements, and then
> > > compute a queryid base on a hash of the query text. Maybe we could
> > > instead fully reserve queryid "2" for utility statements (so forcing
> > > queryid "1" for standard queries if jumbling returns 0 *or* 2 instead
> > > of only 0), and use "2" as the identifier for utility statement
> > > instead of "0"?
> >
> > Hmm. Not sure. At this stage it would be nice to gather more input
> > on the matter, and FWIW, I don't like much the assumption that a query
> > ID of 0 is perhaps a utility statement, or perhaps nothing depending
> > on the state of a backend entry, or even perhaps something else
> > depending how on how modules make use and define such query IDs.
>
> I thought each extension would export a function to compute the query
> id, and you would all that function with the pg_stat_activity.query
> string.

I'd really like to have the queryid function available through SQL,
but I think that this specific case wouldn't work very well for
pg_stat_statements' approach as it's working with oid. The query
string in pg_stat_activity is the user provided one rather than a
fully-qualified version, so in order to get that query's queryid, you
need to know the exact search_path in use in that backend, and that's
not something available.