On 11/04/2013 01:56 PM, Adrian Klaver wrote:
> On 11/04/2013 12:44 PM, Jeff Amiel wrote:
>>
>>
>>
>>
>>
>> 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;
>>
>
> 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?
>
Are we sure the interaction with audit_metadata is clean and tidy?