Re: PL/PgSQL, Inheritance, Locks, and Deadlocks

"Thomas F.O'Connell" <tfo@xxxxxxxxxxxx> · Wed, 2 Feb 2005 12:21:00 -0600

One thing that is curious, though, is that when the AccessShareLock is 
acquired by the stored procedure on an unrelated linking table, there 
is also an AccessShareLock acquired on the primary key of the groups 
table. The latter lock is understandable, but why would the procedure 
need any locks whatsoever on linking tables on which it has no direct 
effect (either reading or writing)?

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Feb 2, 2005, at 9:41 AM, Tom Lane wrote:

"Thomas F.O'Connell" <tfo@xxxxxxxxxxxx> writes:
The linking table is a pure linking table. It has a user_id and a

group_id, each a foreign key. The user_id ties to the appropriate

subclass user table. The group_id ties to the groups table, which is

not part of an inheritance hierarchy. A multicolumn primary key covers

both foreign keys in the linking table, and the secondary column of 
the

key also has its own index.

Inserts/updates in a table that has a foreign key result in locks on 
the

referenced rows in the master table.  Could this explain your problem?

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

              http://archives.postgresql.org