pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pg_stat_statements: duplicated external query texts with MSY2
4+ messages / 3 participants
[nested] [flat]

* pg_stat_statements: duplicated external query texts with MSY2
@ 2020-06-08 21:28  legrand legrand <legrand_legrand@hotmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: legrand legrand @ 2020-06-08 21:28 UTC (permalink / raw)
  To: pgsql-bugs

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 ...

Regards
PAscal

Attachments:

  pgss_query_texts.stat (application/octet-stream, 2.5K) download

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: pg_stat_statements: duplicated external query texts with MSY2
@ 2020-06-11 07:21  Julien Rouhaud <rjuju123@gmail.com>
  parent: legrand legrand <legrand_legrand@hotmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Julien Rouhaud @ 2020-06-11 07:21 UTC (permalink / raw)
  To: legrand legrand <legrand_legrand@hotmail.com>; +Cc: pgsql-bugs

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)





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: pg_stat_statements: duplicated external query texts with MSY2
@ 2020-06-11 11:16  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Julien Rouhaud <rjuju123@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2020-06-11 11:16 UTC (permalink / raw)
  To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: legrand legrand <legrand_legrand@hotmail.com>; pgsql-bugs

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* RE: pg_stat_statements: duplicated external query texts with MSY2
@ 2020-06-11 20:33  legrand legrand <legrand_legrand@hotmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: legrand legrand @ 2020-06-11 20:33 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Julien Rouhaud <rjuju123@gmail.com>; +Cc: pgsql-bugs


>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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2020-06-11 20:33 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-08 21:28 pg_stat_statements: duplicated external query texts with MSY2 legrand legrand <legrand_legrand@hotmail.com>
2020-06-11 07:21 ` Julien Rouhaud <rjuju123@gmail.com>
2020-06-11 11:16   ` Tom Lane <tgl@sss.pgh.pa.us>
2020-06-11 20:33     ` legrand legrand <legrand_legrand@hotmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox