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