Re: Patch: Show queries of processes holding a lock

Hi Alexey Orlov
Thank you for your work on this path,The lock information is recorded
in detail,Easy to trace the lock competition at that time there is a
detailed lock competition log，But I have a concern,Frequent calls to this
function (pgstat_get_backend_current_activity) in heavy lock contention or
high concurrency environments may cause performance degradation, especially
when processes frequently enter and exit lock waits. Can you add a guc
parameter to turn this feature on or off?After all communities for this
parameter( log_lock_waits )default values set to on many people concern (
https://commitfest.postgresql.org/49/4718/)

Thanks

Alexey Orlov <aporlov(at)gmail(dot)com> 于2024年10月1日周二 16:04写道：

> Hi, there!
>
> I created patch improving the log messages generated by
> log_lock_waits.
>
> Sample output (log_lock_waits=on required):
>
> session 1:
> CREATE TABLE foo (val integer);
> INSERT INTO foo (val) VALUES (1);
> BEGIN;
> UPDATE foo SET val = 3;
>
> session 2:
> BEGIN;
> UPDATE TABLE foo SET val = 2;
>
> Output w/o patch:
>
> LOG: process 3133043 still waiting for ShareLock on transaction 758
> after 1000.239 ms
> DETAIL: Process holding the lock: 3132855. Wait queue: 3133043.
> CONTEXT: while updating tuple (0,7) in relation "foo"
> STATEMENT: update foo SET val = 2;
>
> Output with path
>
> LOG: process 3133043 still waiting for ShareLock on transaction 758
> after 1000.239 ms
> DETAIL: Process holding the lock: 3132855. Wait queue: 3133043.
> Process 3132855: update foo SET val = 3;
> CONTEXT: while updating tuple (0,7) in relation "foo"
> STATEMENT: update foo SET val = 2;
>
> As you can see information about query that holds the lock goes into log.
>
> If this approach proves unacceptable, we can make the log_lock_waits
> parameter as an enum
> and display the query if the log_lock_waits=verbose (for example).
>
> What do you think?
>
> Regards,
>
> --
> Orlov Alexey
>