PL/PgSQL, Inheritance, Locks, and Deadlocks - Mailing list pgsql-general

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