Re: [HACKERS] Notice lock waits
On Mon, Oct 3, 2016 at 11:40 AM, Michael Paquier wrote: > On Fri, Sep 30, 2016 at 2:00 AM, Jeff Janes 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
Re: [HACKERS] Notice lock waits
On Fri, Sep 30, 2016 at 2:00 AM, Jeff Janes 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
Re: [HACKERS] Notice lock waits
On Fri, Sep 30, 2016 at 3:00 AM, Jeff Janes wrote: > On Wed, Sep 28, 2016 at 11:57 PM, Haribabu Kommi > 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
Re: [HACKERS] Notice lock waits
On Wed, Sep 28, 2016 at 11:57 PM, Haribabu Kommi wrote: > > > On Sat, Aug 6, 2016 at 3:00 AM, 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. >> > > > 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
Re: [HACKERS] Notice lock waits
On Sat, Aug 6, 2016 at 3:00 AM, 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. > 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
Re: [HACKERS] Notice lock waits
On Fri, Aug 5, 2016 at 10:30 PM, Jeff Janes 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
Re: [HACKERS] Notice lock waits
On Tue, Aug 9, 2016 at 5:17 PM, Jim Nasby 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
Re: [HACKERS] Notice lock waits
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
Re: [HACKERS] Notice lock waits
On Fri, Aug 5, 2016 at 12:17 PM, Tom Lane wrote: > Jeff Janes 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 notice_lock_waits-V02.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notice lock waits
Jeff Janes 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
Re: [HACKERS] Notice lock waits
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
