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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
