PL/PgSQL, Inheritance, Locks, and Deadlocks
От | Thomas F.O'Connell |
---|---|
Тема | PL/PgSQL, Inheritance, Locks, and Deadlocks |
Дата | |
Msg-id | 5a05342b312a3243197a71b6b6501e97@sitening.com обсуждение исходный текст |
Ответы |
Re: PL/PgSQL, Inheritance, Locks, and Deadlocks
|
Список | 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
В списке pgsql-general по дате отправления: