Re: table lock when where clause uses unique constraing instead of primary key.

On 11/04/2013 12:44 PM, Jeff Amiel wrote:

On Monday, November 4, 2013 2:25 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

I grepped the schema (just to be sure) - no foreign keys on columns or table at all.
I do have an audit trigger on the table for updates - inserts into an audit table when changes are made and it DOES do a separate select from user_profile for other reasons - but not "for update" or anything - no explicit locking.

Would it be possible to see that audit function?

it's kind of long (really just a lot of compares of old/new values.
The relevant portion (that selects from user_profile) looks like this:

BEGIN
             SELECT user_id, user_ip INTO my_user_id, my_user_ip FROM audit_metadata WHERE pg_pid = getpid();
             IF ((NOT FOUND) OR (my_user_id = -1)) THEN
                 SELECT user_id INTO my_user_id FROM user_profile WHERE username = 'db-'||CURRENT_USER and user_type='DBASE';
                 IF (NOT FOUND) THEN
                      RAISE EXCEPTION 'USERNAME NOT FOUND IN USER_PROFILE: % ',CURRENT_USER;
                 END IF;
                 my_user_ip := inet_client_addr();
             END IF;

             INSERT INTO audit .....

         END;

Hmmm, nothing obvious here.

In the screenshot you posted what are the columns indicating, in particular the third one?

Assuming the third column is pointing to the pid of the offending query it is interesting that the other queries are coming from other IPs. Almost as if the original query is bouncing off something. Is that possible?