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

Thanks a lot for really good points!! I did not expected I will get this
many points of view. :P

I have identical experience with Robert when other extension calculate the
id different as PGSS, PGSS will overwritten that id when it is on. But Tom
got a point that if we centralize the logic that pgss has, then other
extension will have no way to change it unless we have some new config to
toggle pointed out by Julien. Also Tom got the concern about the current
PGSS jumble query logic is not bullet proof and may take time then impact
the perf.

Let's take one step back. Since queryId is stored in core as Julien pointed
out, can we just add that global to the pg_stat_get_activity and ultimately
exposed in pg_stat_activity view? Then no matter whether PGSS is on or
off, or however the customer extensions are updating that filed, we expose
that field in that view then enable user to leverage that id to join with
pgss or their extension. Will this sounds a good idea?

Thanks again,
Yun

On Sat, Mar 16, 2019 at 11:01 AM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:

> On Sat, Mar 16, 2019 at 5:20 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > > On Fri, Mar 15, 2019 at 9:50 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > >> pg_stat_statements has a notion of query ID, but that notion might be
> > >> quite inappropriate for other usages, which is why it's an extension
> > >> and not core.
> >
> > > Having written an extension that also wanted a query ID, I disagree
> > > with this position.
> >
> > [ shrug... ] The fact remains that pg_stat_statements's definition is
> > pretty lame. There's a lot of judgment calls in which query fields
> > it chooses to examine or ignore, and there's been no attempt at all
> > to make the ID PG-version-independent, and I rather doubt that it's
> > platform-independent either. Nor will the IDs survive a dump/reload
> > even on the same server, since object OIDs will likely change.
> >
> > These things are OK, or at least mostly tolerable, for
> pg_stat_statements'
> > usage ... but I don't think it's a good idea to have the core code
> > dictating that definition to all extensions. Right now, if you have
> > an extension that needs some other query-ID definition, you can do it,
> > you just can't run that extension alongside pg_stat_statements.
> > But you'll be out of luck if the core code starts filling that field.
> >
> > I'd be happier about having the core code compute a query ID if we
> > had a definition that was not so obviously slapped together.
>
> But the queryId itself is stored in core. Exposing it in
> pg_stat_activity or log_line_prefix would still allow users to choose
> the implementation of their choice, or none. That seems like a
> different complaint from asking pgss integration in core to have all
> its metrics available by default (or at least without a restart).
>
> Maybe we could add a GUC for pg_stat_statements to choose whether it
> should set the queryid itself and not, if anyone wants to have its
> metrics but with different queryid semantics?
>