Обсуждение: BUG #17146: pg_dump statements are going into pg_stat_statements in 13.4

The following bug has been logged on the website:

Bug reference:      17146
Logged by:          James Inform
Email address:      james.inform@pharmapp.de
PostgreSQL version: 13.4
Operating system:   Ubuntu 18.04 LTS AND macOS Catalina 10.15.7
Description:

Hi

I have just updated to 13.4 and I have noticed that "COPY" statements that
are produced by pg_dump appear in pg_stat_statements view.

You can imagine that the copy statements are slower than all my other
statements, so they come up as the first and most expensive statements in
pg_stat_statements.

This is a new behaviour in 13.4 and hasn't existed in 13.3 and prior.

Is there a setting to prevent this?

Cheers,
James

On Sun, Aug 15, 2021 at 05:30:52PM +0000, PG Bug reporting form wrote:
> I have just updated to 13.4 and I have noticed that "COPY" statements that
> are produced by pg_dump appear in pg_stat_statements view.

Of course they do. These are statements.

> You can imagine that the copy statements are slower than all my other
> statements, so they come up as the first and most expensive statements in
> pg_stat_statements.
> This is a new behaviour in 13.4 and hasn't existed in 13.3 and prior.

No, it's not.

I just tested to be 100% sure, on 12.6 and pg_dump queries are in
pg_stat_statements.

Just consider what would you do if excessive dumping was causing
problems. You want these to be gone from stats?

If you don't care about COPY, then filter it out using WHERE clause.

Best regards,

depesz

On Mon, Aug 16, 2021 at 4:46 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> I have just updated to 13.4 and I have noticed that "COPY" statements that
> are produced by pg_dump appear in pg_stat_statements view.
>
> You can imagine that the copy statements are slower than all my other
> statements, so they come up as the first and most expensive statements in
> pg_stat_statements.

You could order by the average execution time rather than the total
execution time and/or filter out queries executed less than X times,
it's more likely to give you low hanging fruits.

> This is a new behaviour in 13.4 and hasn't existed in 13.3 and prior.

Nothing changed here for a long time.

> Is there a setting to prevent this?

You could disable pg_stat_statements.track_utility, but it obviously
means that all utility statements will be ignored, not only COPY.

That's strange.
I see those copy commands it pg_stat_statments and they have 1 calls. 
The backup job runs once a day since many months. I would expect to see 
calls > 1 then.

So again very strange, but maybe a layer 8 problem :)

  I will recheck.


Julien Rouhaud Wrote:
> On Mon, Aug 16, 2021 at 4:46 PM PG Bug reporting form
> <noreply@postgresql.org> wrote:
>> The following bug has been logged on the website:
>>
>> I have just updated to 13.4 and I have noticed that "COPY" statements that
>> are produced by pg_dump appear in pg_stat_statements view.
>>
>> You can imagine that the copy statements are slower than all my other
>> statements, so they come up as the first and most expensive statements in
>> pg_stat_statements.
> You could order by the average execution time rather than the total
> execution time and/or filter out queries executed less than X times,
> it's more likely to give you low hanging fruits.
>
>> This is a new behaviour in 13.4 and hasn't existed in 13.3 and prior.
> Nothing changed here for a long time.
>
>> Is there a setting to prevent this?
> You could disable pg_stat_statements.track_utility, but it obviously
> means that all utility statements will be ignored, not only COPY.

On Mon, Aug 16, 2021 at 5:48 PM James Inform <james.inform@pharmapp.de> wrote:
>
> That's strange.
> I see those copy commands it pg_stat_statments and they have 1 calls.
> The backup job runs once a day since many months. I would expect to see
> calls > 1 then.

One possibility is that pg_stat_statements.max is too small, leading
to pg_stat_statements evicting the least frequently used entries.  You
can compare the current number of entries to the
pg_stat_statements.max and see if it has between 95 and 100% of
entries allocated.

Note that pg_stat_statements entry eviction is quite costly, so if
that the reason why you don't see more execution you may want to
increase pg_stat_statements.max.  It might not be an option if you
have hundred of thousands of tables though, and in that case disabling
track_utility may be a better option.  Another good option may also be
to change you backup approach and rely on physical backup rather than
pg_dump, which would solve your original problem and also probably
improve your RPO.