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.