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

2013-07-09 Thread potapov . dmitry 
The following bug has been logged on the website:

Bug reference:  8289
Logged by:  Dmitriy Potapov
Email address:  potapov.dmi...@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.




-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

2013-07-09 Thread Tom Lane 
potapov.dmi...@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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs