On Mon, Jul 27, 2020 at 4:28 PM Jeremy Schneider <schnj...@amazon.com> wrote:
> 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. > That is just one of a number of different popular drivers of course. > > 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. > It does. It also prepends some queries with comments, specifically to allow users to filter them out when they're analysing logs (a feature requested by users, not just something we thought was a good idea). I'm assuming that this patch would also strip those? > > 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. > I think there are arguments to be made for both approaches. > > 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 > > > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EDB: http://www.enterprisedb.com
