Possible fails in pg_stat_statements test

From: "Anton A(dot) Melnikov" <aamelnikov(at)inbox(dot)ru>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Possible fails in pg_stat_statements test
Date: 2022-01-14 08:11:07
Message-ID: 1ccd00d9-1723-6b68-ae56-655aab00d406@inbox.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


There are some places in the pg_state_statement's regress test where the
bool result of comparison between the number of rows obtained and
wal_records generated by query should be displayed.
Now counting the number of wal_records for some query in
pg_state_statement is done by the global pgWalUsage.wal_records counter
difference calculation.
During query execution the extra wal_records may appear that are not
relate to the query.
There are two reasons why this might happen:
1) Owing to the hit into pruning of some page in optional pruning
function (heap_page_prune_opt()).
2) When a new page is required for a new xid in clog and
WriteZeroPageXlogRec() was called.
In both cases an extra wal record with zero xl_xid is generated, so
wal_records counter gives an incremented value for this query and
pg_stat_statement test will fail.

This patch introduces an additional counter of wal records not related
to the query being executed.
Due to this counter pg_stat_statement finds out the number of wal
records that are not relevant to the query and does not include them in
the per query statistic.
This removes the possibility of the error described above.

There is a way to reproduce this error when patch is not applied:
1) start server with "shared_preload_libraries = 'pg_stat_statements'"
string in the postgresql.conf;
2) replace makefile in contrib/pg_stat_statements with attached one;
3) replace test file
contrib/pg_stat_statements/sql/pg_stat_statements.sql and expected
results contrib/pg_stat_statements/expected/pg_stat_statements.out
with shorter versions from attached files;
4) copy test.sh to contrib/pg_stat_statements and make sure that PGHOME
point to your server;
5) cd to contrib/pg_stat_statements and execute:
export ITER=1 && while ./start.sh || break; export ITER=$(($ITER+1)); do
:; done

Usually 100-200 iterations will be enough.
To catch the error more faster one can add wal_records column to SELECT
in line 26 of contrib/pg_stat_statements/sql/pg_stat_statements.sql as
SELECT query, calls, rows, wal_records,
and replace the contrib/pg_stat_statements/expected/pg_stat_statements.out
with attached pg_stat_statements-fast.out

With best regards,
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Attachment Content-Type Size
v1-0001-Fix-possible-fails-in-pg_stat_statements-test.patch text/x-patch 4.0 KB
Makefile text/plain 1.2 KB
pg_stat_statements.sql application/sql 989 bytes
pg_stat_statements.out text/plain 2.6 KB
start.sh application/x-shellscript 267 bytes
pg_stat_statements-fast.out text/plain 2.8 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2022-01-14 08:18:14 Re: Schema variables - new implementation for Postgres 15
Previous Message Julien Rouhaud 2022-01-14 08:03:41 Re: MDAM techniques and Index Skip Scan patch