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@xxxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
