Re: BUG: pg_stat_statements query normalization issues with combined queries

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
Cc: robertmhaas(at)gmail(dot)com, PostgreSQL Developers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: BUG: pg_stat_statements query normalization issues with combined queries
Date: 2016-12-23 09:51:43
Message-ID: alpine.DEB.2.20.1612231037030.3892@lancre
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

> Yes. I'll try to put together a patch and submit it to the next CF.

Here it is. I'll add this to the next CF.

This patch fixes the handling of compound/combined queries by
pg_stat_statements (i.e. several queries sent together, eg with psql:
"SELECT 1 \; SELECT 2;").

This bug was found in passing while investigating a strange performance
issue I had with compound statements.

Collect query location information in parser:

* create a ParseNode for statements with location information fields
(qlocation & qlength).

* add these fields to all parsed statements (*Stmt), Query and PlannedStmt.

* statements location is filled in:

- the lexer keep track of the last ';' encountered.

- the information is used by the parser to compute the query length,
which depends on whether the query ended on ';' or on the input end.

- the query location is propagated to Query and PlannedStmt when built.

Fix pg_stat_statement:

* the information is used by pg_stat_statement so as to extract the relevant part
of the query string, including some trimming.

* pg_stat_statement validation is greatly extended so as to test options
and exercise various cases, including compound statements.

note 1: two non-statements tags (use for alter table commands) have been
moved so that all statements tags are contiguous and easy to check.

note 2: the impact on the lexer & parser is quite minimal with this
approach, about 30 LOC, most of which in one function. The largest changes
are in the node header to add location fields.

note 3: the query length excludes the final separator, so that
;-terminated and end of input terminated queries show the same.

note 4: the added test suggests that when tracking "all", queries in SQL
user functions are not tracked, this might be a bug.


Attachment Content-Type Size
parsenodes-1.patch text/x-diff 59.8 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Craig Ringer 2016-12-23 10:00:43 Re: [PATCH] Transaction traceability - txid_status(bigint)
Previous Message Pavel Stehule 2016-12-23 08:17:32 Re: [COMMITTERS] pgsql: Simplify LWLock tranche machinery by removing array_base/array_s