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

On Sat, Feb 1, 2020 at 12:30 PM Tomas Vondra
<tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>
> On Fri, Nov 29, 2019 at 09:39:09AM +0100, Julien Rouhaud wrote:
> >On Fri, Nov 29, 2019 at 7:21 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> >>
> >> On Fri, Nov 29, 2019 at 03:19:49PM +0900, Michael Paquier wrote:
> >> > On Wed, Nov 13, 2019 at 12:53:09PM +0100, Julien Rouhaud wrote:
> >> >> 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.
> >> >
> >> > Yeah.. So, we have a patch marked as ready for committer here, and it
> >> > seems to me that we have a couple of issues to discuss more about
> >> > first particularly this query ID of 0. Again, do others have more
> >> > any input to offer?
> >
> >I just realized that with current infrastructure it's not possible to
> >display a utility queryid. We need to recognize utility to not
> >process the counters twice (once in processUtility, once in the
> >underlying executor), so we don't provide a queryid for utility
> >statements in parse analysis. Current magic value 0 has the side
> >effect of showing an invalid queryid for all utilty statements, and
> >using a magic value different from 0 will just always display that
> >magic value. We could instead add another field in the Query and
> >PlannedStmt structs, say "int queryid_flags", that extensions could
> >use for their needs?
> >
> >> And while on it, the latest patch does not apply, so a rebase is
> >> needed here.
> >
> >Yep, I noticed that this morning. I already rebased the patch
> >locally, I'll send a new version with new modifications when we reach
> >an agreement on the utility issue.
> >
>
> Well, this patch was in WoA since November, but now that I look at it
> that might have been wrong - we're clearly waiting for agreement on how
> to handle queryid for utility commands. I suspect the WoA status might
> have been driving people away from this thread :-(

Oh, indeed.

> I've switched the patch to "needs review" and moved it to the next CF.

Thanks

> What I think needs to happen is we get a patch implementing one of the
> proposed solutions, and discuss that.

There's also the possibility to reserve 1 bit of the hash to know if
this is a utility command or not, although I don't recall right now
all the possible issues with utility commands and some special
handling of them. I'll work on it before the next commitfest.