Re: pg_stat_statements locking

> On 12 Sep 2022, at 13:40, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>
> I'm not a fan of that patch as it now silently ignores entries if the lwlock
> can't be acquired *immediately*, without any way to avoid that if your
> configuration and/or workload doesn't lead to this problem, or any way to know
> that entries were ignored.

Practically, workload of this configuration is uncommon. At least I could not find any reports of such locking.
But theoretically, all prerequisites of a disaster is very common (variety of queries + some QPS of pg_stat_statements view + small work_mem + occasional reset() or GC).

Maybe it's only a problem of programs that use pgss. pgwatch is calling pgss on every DB in the cluster, that's how check once in a minute became some queries per second.

Personally, I'd prefer if I could configure a timeout to aquire lock. That timeout would denote maximum delay that pgss can incur on the query. But we would need to extend LWLock API for this.

> On 12 Sep 2022, at 13:40, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>
> I think that the better long term approach is to move pg_stat_statements and
> the query texts to dynamic shared memory.

BTW we don't even need a dynamic memory. We need just a shared memory, probably pre-allocated.
I agree that pgss must reside in main memory only, never on disk.

But we still will have a possibility of long lock conflicts preventing queries from completing. And the ability to configure pgss hooks timeout would be useful anyway.

Best regards, Andrey Borodin.