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

I apologize for the broadbrush subject, but I've been dealing with some
anomalies that seem somewhat interrelated.

In the latest manifestation, I have a stored procedure that is designed
to update some counts on a table that is inherited from another table.

The basic structure is a superclass user table that is broken down into
several subclass user tables. There is an account table and a group
table, each of which store some statistics. Each subclass user table is
linked to a group through a linking table that is itself a subclass.
Each subclass user table also has its own stored procedure for updating
statistics.

What I find in running this is that the stored procedure attempts to
acquire locks on linking tables from unrelated subclasses. I don't see
anything that would cause this. The idea is to preserve an isolation
among the different user subclasses.

Here is the stored procedure (with proprietary identifiers altered; I
hope I haven't introduced inconsistencies in this process...):

DECLARE
v_group record;
v_group_id groups.group_id%TYPE;
v_user_count1 users.count1%TYPE;
v_group_count1 groups.count1%TYPE;
v_group_count2 groups.count2%TYPE;
v_group_count3 groups.count2%TYPE;
BEGIN
SELECT INTO v_user_count1 COUNT( * )
FROM ONLY users1
WHERE user_status_id = '1'
AND user_is_deleted IS FALSE;
UPDATE accounts
SET count1 = v_user_count1
WHERE account_id = '1';
FOR v_group IN
SELECT DISTINCT group_id
FROM users1_groups
LOOP
SELECT INTO v_group_count1 COUNT( * )
FROM users1_groups AS ug, users1 AS u
WHERE ug.user_id = u.user_id
AND ug.group_id = v_group.group_id
AND u.user_status_id = '1';
SELECT INTO v_group_count2 COUNT( * )
FROM users1_groups AS ug, users1 AS u
WHERE ug.user_id = u.user_id
AND ug.group_id = v_group.group_id
AND u.user_status_id = '2';
SELECT INTO v_group_count3 COUNT( * )
FROM users1_groups AS ug, users1 AS u
WHERE ug.user_id = u.user_id
AND ug.group_id = v_group.group_id
AND u.user_status_id = '3';
UPDATE groups
SET count1 = v_group_count1, count2 = v_group_count2,
count3 = v_group_count3
WHERE group_id = v_group.group_id;
END LOOP;
RETURN;
END;

For instance, when run, this stored procedure could try to acquire a
lock on users2_groups despite not directly referencing it.

In a somewhat related issue, I frequently encounter deadlocks while
creating various pieces of the inheritance structure -- including
tables and triggers -- when adding new user types. During these
deadlock situations, pieces of the subclasses seem to be waiting for
locks in other pieces that should be unrelated.

Unfortunately, I've had a difficult time isolating a reproducible
deadlock scenario. In fact, tips for doing so are welcome.

I realize that inheritance is an incomplete implementation in postgres,
but I'm seeing behavior that I definitely wouldn't expect given (the
limited amount of) what I know about the pieces that are implemented.

Does anyone have insight into why the above procedure would try to
acquire locks not specifically referenced or why a data model with
heavy usage of inheritance would be prone to deadlock situations in
CREATE statements?

Version information: PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by
GCC 2.95.4

-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

"Thomas F.O'Connell" <tfo(at)sitening(dot)com> writes:

> UPDATE groups
> SET count1 = v_group_count1, count2 = v_group_count2, count3 =

>
> For instance, when run, this stored procedure could try to acquire a lock on
> users2_groups despite not directly referencing it.

Does the users2_groups contain a foreign key reference to the groups table? If
so then if you need to update the groups table regularly you'll want an index
on the referring column. Otherwise in order to check the constraint Postgres
needs to do a sequential scan of the referring table to make sure your update
doesn't break a reference.

I don't know how this plays with locks though.

--
greg

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.

I'm more concerned with the locking, which is thoroughly unexpected
behavior to me.

-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 12:03 AM, Greg Stark wrote:

>
> "Thomas F.O'Connell" <tfo(at)sitening(dot)com> writes:
>
>> UPDATE groups
>> SET count1 = v_group_count1, count2 =
>> v_group_count2, count3 =
>
>>
>> For instance, when run, this stored procedure could try to acquire a
>> lock on
>> users2_groups despite not directly referencing it.
>
> Does the users2_groups contain a foreign key reference to the groups
> table? If
> so then if you need to update the groups table regularly you'll want
> an index
> on the referring column. Otherwise in order to check the constraint
> Postgres
> needs to do a sequential scan of the referring table to make sure your
> update
> doesn't break a reference.
>
> I don't know how this plays with locks though.
>
> --
> greg
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

"Thomas F.O'Connell" <tfo(at)sitening(dot)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

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(at)sitening(dot)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

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(at)sitening(dot)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