Notice lock waits
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.
Cheers,
Jeff
Attachments:
notice_lock_waits-V01.patchapplication/octet-stream; name=notice_lock_waits-V01.patchDownload+29-2
On 05/08/2016 19:00, Jeff Janes 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.
I really like the idea.
I'm not really sure on current implementation. Unless I'm wrong,
disabling log_lock_waits would also disable notice_lock_waits, even if
it's on.
Maybe a new value for log_lock_waits, like "interactive". If switching
this GUC from bool to enum is not acceptable or allowing to see blocking
PID for anyone is an issue, then maybe adding a new GUC to say to also
send a NOTICE instead?
--
Julien Rouhaud
http://dalibo.com - http://dalibo.org
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Jeff Janes <jeff.janes@gmail.com> writes:
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.
Why not just leave out the PID? I think it's often far too simplistic
to blame a lock wait on a single other process, anyway.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Aug 5, 2016 at 12:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jeff Janes <jeff.janes@gmail.com> writes:
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.Why not just leave out the PID? I think it's often far too simplistic
to blame a lock wait on a single other process, anyway.
It actually wasn't including the PID anyway, as the
errdetail_log_plural was not getting passed to the client.
So I changed it to PGC_USERSET, didn't attempt to include details that
won't be sent anyway (although it would be nice for a superuser to be
able to see the statement text of the blocker, but that is a bigger
issue than I am willing to deal with here) and have removed a memory
leak/bug I introduced by foolishly trying to use 'continue' to avoid
introducing yet another layer of nesting.
Cheers,
Jeff
Attachments:
notice_lock_waits-V02.patchapplication/octet-stream; name=notice_lock_waits-V02.patchDownload+119-83
On 8/5/16 12:00 PM, Jeff Janes wrote:
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 would strongly prefer that this accept a log level instead of being
hard-coded to NOTICE. The reason is that I find the NOTICE chatter from
many DDL commands to be completely worthless (looking at you %TYPE), so
I normally set client_min_messages to WARNING in DDL scripts. I can work
on that patch; would it essentially be a matter of changing
notice_lock_waits to int lock_wait_level?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Aug 9, 2016 at 5:17 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
On 8/5/16 12:00 PM, Jeff Janes wrote:
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 would strongly prefer that this accept a log level instead of being
hard-coded to NOTICE. The reason is that I find the NOTICE chatter from
many DDL commands to be completely worthless (looking at you %TYPE),
Perhaps we should do something about those notices? In 9.3 we removed ones
about adding implicit unique indexes to implement primary keys, and I think
that that was a pretty good call.
so I normally set client_min_messages to WARNING in DDL scripts. I can
work on that patch; would it essentially be a matter of changing
notice_lock_waits to int lock_wait_level?
How would it be turned off? Is there a err level which would work for
that? And what levels would non-superusers be allowed to set it to?
And, I'd be happy if you were to work on a patch to implement it.
Cheers,
Jeff
On Fri, Aug 5, 2016 at 10:30 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
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.
IMHO that's what we need and it will benefit many more users instead of
adding a new GUC every time.
FWIW I recently wrote a patch for Postgres-XL to do exactly this and I
found it very useful, especially while debugging race conditions and
problems with ongoing sessions. Sorry, I don't mean to hijack this thread,
will post that patch as a separate thread.
Thanks,
Pavan
--
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Sat, Aug 6, 2016 at 3:00 AM, Jeff Janes <jeff.janes@gmail.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
On Wed, Sep 28, 2016 at 11:57 PM, Haribabu Kommi <kommi.haribabu@gmail.com>
wrote:
On Sat, Aug 6, 2016 at 3:00 AM, Jeff Janes <jeff.janes@gmail.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 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?
Thanks,
Jeff
On Fri, Sep 30, 2016 at 3:00 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
On Wed, Sep 28, 2016 at 11:57 PM, Haribabu Kommi <kommi.haribabu@gmail.com
wrote:
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.
Yes, I understood. Changing the existing GUC will make it complex.
What do you think of Jim Nasby's idea of making a settable level, rather
just on or off?
I am not clearly understood, how the settable level works here? Based on
log_min_messages
or something, the behavior differs?
The Notification messages are good, If we are going to add this facility
only for lock waits, then
a simple GUC is enough. If we are going to enhance the same for other
messages, then I prefer
something like log_statement GUC to take some input from user and those
messages will be
sent to the user.
Regards,
Hari Babu
Fujitsu Australia
On Fri, Sep 30, 2016 at 2:00 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
What do you think of Jim Nasby's idea of making a settable level, rather
just on or off?
[reading the code]
That would be a better idea. The interface proposed, aka 2 GUCs doing
basically the same thing is quite confusing I think. I am marking the
patch as returned with feedback for now.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Oct 3, 2016 at 11:40 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:
On Fri, Sep 30, 2016 at 2:00 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
What do you think of Jim Nasby's idea of making a settable level, rather
just on or off?[reading the code]
That would be a better idea. The interface proposed, aka 2 GUCs doing
basically the same thing is quite confusing I think. I am marking the
patch as returned with feedback for now.
Forgot to mention that I also found myself enforcing
client_min_messages to warning to avoid annoying NOTICE messages.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers