Thread: BUG #8289: pg_stat_statements incorrect query text when executing multiple queries in a single PQexec
BUG #8289: pg_stat_statements incorrect query text when executing multiple queries in a single PQexec
From
potapov.dmitry@gmail.com
Date:
The following bug has been logged on the website: Bug reference: 8289 Logged by: Dmitriy Potapov Email address: potapov.dmitry@gmail.com PostgreSQL version: 9.2.4 Operating system: Scientific Linux 6.3 Description: pg_stat_statements view contains incorrectly normalized query texts when executing multiple queries in a single PQexec call. How to reproduce: 1. Create a database. 2. Populate it with pss_mq_testcase.sql http://pgsql.privatepaste.com/e834c641d6 3. Compile pss_mq.c http://pgsql.privatepaste.com/88421cfdf7 with following command: gcc -o pss_mq -I`/usr/pgsql-9.2/bin/pg_config --includedir` -L `/usr/pgsql-9.2/bin/pg_config --libdir` -lpq pss_mq.c 4. Execute select pg_stat_statements_reset(); 5. Run pss_mq with connection string to database as command line parameter (for example: ./pss_mq 'dbname=psstest port=5433') 6. Check pg_stat_statements view. I get the following results: http://pgsql.privatepaste.com/6908db7e80 The testcase runs five UPDATE commands with two PQexec calls. First PQexec call runs: "UPDATE t1 SET a=101 WHERE id=0; UPDATE t1 SET a=102 WHERE id=1; UPDATE t1 SET a=103 WHERE id=2" Second PQexec call runs: "UPDATE t2 SET b=1 where a=0; UPDATE t2 SET a=2 WHERE b=3" I expect pg_stat_statements to contain three records with following query texts: 1) UPDATE t1 SET a=? WHERE id=? 2) UPDATE t2 SET b=? where a=? 3) UPDATE t2 SET a=? WHERE b=? Instead it contains three records with following query texts: 1) "UPDATE t2 SET b=1 where a=0; UPDATE t2 SET a=? WHERE b=?" 2) "UPDATE t1 SET a=? WHERE id=?; UPDATE t1 SET a=102 WHERE id=1; UPDATE t1 SET a=103 WHERE id=2" 3) "UPDATE t2 SET b=? where a=?; UPDATE t2 SET a=2 WHERE b=3" PostgreSQL version: PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit OS version: Scientific Linux release 6.3 (Carbon) PostgreSQL packages (from pgdg yum repo): postgresql92-9.2.4-1PGDG.rhel6.x86_64, postgresql92-contrib-9.2.4-1PGDG.rhel6.x86_64, postgresql92-libs-9.2.4-1PGDG.rhel6.x86_64, postgresql92-server-9.2.4-1PGDG.rhel6.x86_64, postgresql92-devel-9.2.4-1PGDG.rhel6.x86_64 Testcase archive can be downloaded here: http://yadi.sk/d/rRcN9GTO6e7Me This bug was discovered when I checked pg_stat_statements on a database of zabbix monitoring system.
Re: BUG #8289: pg_stat_statements incorrect query text when executing multiple queries in a single PQexec
From
Tom Lane
Date:
potapov.dmitry@gmail.com writes: > The testcase runs five UPDATE commands with two PQexec calls. > First PQexec call runs: "UPDATE t1 SET a=101 WHERE id=0; UPDATE t1 SET a=102 > WHERE id=1; UPDATE t1 SET a=103 WHERE id=2" > Second PQexec call runs: "UPDATE t2 SET b=1 where a=0; UPDATE t2 SET a=2 > WHERE b=3" > I expect pg_stat_statements to contain three records with following query > texts: > 1) UPDATE t1 SET a=? WHERE id=? > 2) UPDATE t2 SET b=? where a=? > 3) UPDATE t2 SET a=? WHERE b=? > Instead it contains three records with following query texts: > 1) "UPDATE t2 SET b=1 where a=0; UPDATE t2 SET a=? WHERE b=?" > 2) "UPDATE t1 SET a=? WHERE id=?; UPDATE t1 SET a=102 WHERE id=1; UPDATE t1 > SET a=103 WHERE id=2" > 3) "UPDATE t2 SET b=? where a=?; UPDATE t2 SET a=2 WHERE b=3" Yeah, that's what's going to happen, because there is no infrastructure for determining which portion of the source text string belongs to which query. I suspect there are some other infelicities in pg_stat_statements' behavior for multi-query strings, too. At least for now, that combination is best avoided. regards, tom lane