Re: Display individual query in pg_stat_activity

On 7/27/20 07:57, Dave Page wrote:
> I'm not sure I'd want that to happen, as it could make it much harder
> to track the activity back to a query in the application layer or
> server logs. 
>
> Perhaps a separate field could be added for the current statement, or
> a value to indicate what the current statement number in the query is?

Might be helpful to give some specifics about circumstances where
strings can appear in pg_stat_activity.query with multiple statements.

1) First of all, IIUC multiple statements are only supported in the
first place by the simple protocol and PLs.  Anyone using parameterized
statements (bind variables) should be unaffected by this.

2) My read of the official pg JDBC driver is that even for batch
operations it currently iterates and sends each statement individually.
I don't think the JDBC driver has the capability to send multiple
statements, so java apps using this driver should be unaffected.

3) psql -c will always send the string as a single "simple protocol"
request.  Scripts will be impacted.

4) PLs also seem to have a code path that can put multiple statements in
pg_stat_activity when parallel slaves are launched.  PL code will be
impacted.

5) pgAdmin uses the simple protocol and when a user executes a block of
statements, pgAdmin seems to send the whole block as a single "simple
protocol" request.  Tools like pgAdmin will be impacted.

At the application layer, it doesn't seem problematic to me if
PostgreSQL reports each query one at a time.  IMO most people will find
this to be a more useful behavior and they will still find their queries
in their app code or app logs.

However at the PostgreSQL logging layer this is a good call-out.  I just
did a quick test on 14devel to double-check my assumption and it does
seem that PostgreSQL logs the entire combined query for psql -c.  I
think it would be better for PostgreSQL to report queries individually
in the log too - for example pgBadger summaries will be even more useful
if they report information for each individual query rather than a
single big block of multiple queries.

Given how small this patch is, it seems worthwhile to at least
investigate whether the logging component could be addressed just as easily.

-Jeremy

--
Jeremy Schneider
Database Engineer
Amazon Web Services