Re: Notice lock waits

On Sat, Aug 6, 2016 at 3:00 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> One time too many, I ran some minor change using psql on a production
> server and was wondering why it was taking so much longer than it did
> on the test server. Only to discover, after messing around with
> opening new windows and running queries against pg_stat_activity and
> pg_locks and so on, that it was waiting for a lock.
>
> So I created a new guc, notice_lock_waits, which acts like
> log_lock_waits but sends the message as NOTICE so it will show up on
> interactive connections like psql.
>
> I turn it on in my .psqlrc, as it doesn't make much sense for me to
> turn it on in non-interactive sessions.
>
> A general facility for promoting selected LOG messages to NOTICE would
> be nice, but I don't know how to design or implement that. This is
> much easier, and I find it quite useful.
>
> I have it PGC_SUSET because it does send some tiny amount of
> information about the blocking process (the PID) to the blocked
> process. That is probably too paranoid, because the PID can be seen
> by anyone in the pg_locks table anyway.
>
> Do you think this is useful and generally implemented in the correct
> way? If so, I'll try to write some sgml documentation for it.
>

Providing the details of lock wait to the client is good. I fell this
message
is useful for the cases where User/administrator is trying to perform some
SQL operations.

I also feel that, adding a GUC variable for these logs to show it to user
may not be good. Changing the existing GUC may be better.

I am not sure whether it really beneficial in providing all LOG as NOTICE
messages with a generic framework, it may be unnecessary overhead
for some users, I am not 100% sure.

Regards,
Hari Babu
Fujitsu Australia