pg_stat_statements: duplicated external query texts with MSY2

Hello,

On Mon, Jun 8, 2020 at 11:28 PM legrand legrand
<legrand_legrand@hotmail.com> wrote:

Hello,

I met a stange behavior with testing pg_stat_statements (default setup) with pgbench on my MSYS2 build
(PostgreSQL 13beta1 on x86_64-w64-mingw32, compiled by x86_64-w64-mingw32-gcc.exe (Rev2, Built by MSYS2 project) 9.2.0, 64-bit).

pgbench -i postgres
pgbench -c20 -t5 postgres

generates the attached pgss_query_texts.stat,
where "BEGIN" and "UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2"
appears 20 times ...

It does not seem to appear on linux, and I'm not able to juge if its specific to this port, and if its always limited ...

Is the duplication only in the query text file? Looking at the code
the query text part is stored holding a shared lwlock, so it seems
like an expected behavior (less overhead but might store duplicated
query text)

Julien Rouhaud <rjuju123@gmail.com> writes:

On Mon, Jun 8, 2020 at 11:28 PM legrand legrand
<legrand_legrand@hotmail.com> wrote:

pgbench -i postgres
pgbench -c20 -t5 postgres
generates the attached pgss_query_texts.stat,
where "BEGIN" and "UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2"
appears 20 times ...

Is the duplication only in the query text file? Looking at the code
the query text part is stored holding a shared lwlock, so it seems
like an expected behavior (less overhead but might store duplicated
query text)

I agree, this looks like operating-as-designed: different processes can
store the same query text and only later discover that they were creating
duplicate hash table entries. It's a bit surprising that the duplication
would be reproducible, but it just depends on timing. Maybe this is
telling us something about how scheduling works under MSYS2.

regards, tom lane

Julien Rouhaud <rjuju123@gmail.com> writes:

On Mon, Jun 8, 2020 at 11:28 PM legrand legrand
<legrand_legrand@hotmail.com> wrote:

pgbench -i postgres
pgbench -c20 -t5 postgres
generates the attached pgss_query_texts.stat,
where "BEGIN" and "UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2"
appears 20 times ...

Is the duplication only in the query text file? Looking at the code
the query text part is stored holding a shared lwlock, so it seems
like an expected behavior (less overhead but might store duplicated
query text)

I agree, this looks like operating-as-designed: different processes can
store the same query text and only later discover that they were creating
duplicate hash table entries. It's a bit surprising that the duplication
would be reproducible, but it just depends on timing. Maybe this is
telling us something about how scheduling works under MSYS2.

regards, tom lane

Hello,

duplication is only in the query text file, selecting plans or calls in the view
pg_stat_statements are corrects.

For information, this is reproducable with official build
"PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit"
on windows 10.

Regards
PAscal