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 don't think it would be a good idea to refactor the existing GUC (log_lock_waits) to accomplish this.
There would have to be four states, log only, notice only, both log and notice, and neither. But non-superusers can't be allowed to change the log flag, only the notice flag. It is probably possible to implement that, but it seems complicated both to implement, and to explain/document. I think that adding another GUC is better than greatly complicating an existing one.
What do you think of Jim Nasby's idea of making a settable level, rather just on or off?