updating table with foreign keys cause locking of referenced row/table?

From: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: updating table with foreign keys cause locking of referenced row/table?
Date: 2008-04-10 21:39:36
Message-ID: Pine.LNX.4.64.0804101428000.3084@discord.home.frostconsultingllc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Postgresql-8.3.1

I have a plpgsql function which is called nightly to update rows in a summary
table. The summary table has foreign keys that reference the users table.
When the nightly job runs, the users table gets locked such that UPDATES to
the user table are stuck waiting on the transactionid of the function.

The function does not update or select for update any rows in the users table
and removing the foreign keys in the summary table allows the UPDATES of the
users table to happen with no problem.

Interestingly, in testing this, I started a transaction, updated a bunch of
rows in the summary table with UPDATE, left the transaction open, started
a new session and successfully updated rows in the users table that are
pointed to by the updated rows in the summary table. So there doesn't seem to
be a locking problem outside of the function.

Is the locking behavior different inside a plpgsql function?

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

Browse pgsql-admin by date

  From Date Subject
Next Message Mikel Lindsaar 2008-04-11 07:52:48 Anyone successfully moved the pg_xlog to a separate drive on windows server?
Previous Message Vishal Arora 2008-04-10 08:27:30 RE: [ADMIN] CREATE LANGUAGE cannot load library ERROR!