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

Doubtful, because users never share groups, so even though the groups
table is not part of an inheritance hierarchy, there shouldn't be any
overlap between foreign keys in the users1_groups table and the
users2_groups table in the groups table.

users1_groups links all users in the users1 subclass to groups that
will be completely distinct from the groups in which users2 users could
be categorized.

We were seeing, for instance, the stored procedure I posted, which was
unique to users1, acquire an AccessShareLock on the users2_groups
table. And as it ran (which took a while, since it does several
counts), it seemed to acquire locks on a few different linking tables
from itself (e.g., users3_groups and users4_groups, as well).

The extra locks it was acquiring seemed to be related to some of the
deadlocks I've been seeing during CREATE statements (during standard
operation of the database) on a variety of the subclass tables (both
user tables and linking tables).

-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@sitening.com> 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