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

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