BUG #8289: pg_stat_statements incorrect query text when executing multiple queries in a single PQexec

From: potapov(dot)dmitry(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #8289: pg_stat_statements incorrect query text when executing multiple queries in a single PQexec
Date: 2013-07-09 15:05:19
Message-ID: E1UwZTj-00075u-CB@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 8289
Logged by: Dmitriy Potapov
Email address: potapov(dot)dmitry(at)gmail(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message pgnube 2013-07-09 16:02:22 BUG #8290: broken/unexpected locking behavior
Previous Message tgauss 2013-07-09 12:44:02 BUG #8288: 9.3.0Beta2 - Windows Installer bug #7738 still open