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

On Monday, November 4, 2013 2:25 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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;