Skip site navigation (1) Skip section navigation (2)

Re: FOR KEY LOCK foreign keys

From: Marti Raudsepp <marti(at)juffo(dot)org>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: FOR KEY LOCK foreign keys
Date: 2011-01-28 20:42:20
Message-ID: AANLkTi=X4tghfgiXJ-eht52+_c3aWCamiWhwMCToq7LO@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, Jan 13, 2011 at 23:58, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> It goes like this: instead of acquiring a shared lock on the involved
> tuple, we only acquire a "key lock", that is, something that prevents
> the tuple from going away entirely but not from updating fields that are
> not covered by any unique index.
>
> As discussed, this is still more restrictive than necessary (we could
> lock only those columns that are involved in the foreign key being
> checked), but that has all sorts of implementation level problems, so we
> settled for this, which is still much better than the current state of
> affairs.

Seems to me that you can go a bit further without much trouble, if you
only consider indexes that *can* be referenced by foreign keys --
indexes that don't have expressions or predicates.

I frequently create unique indexes on (lower(name)) where I want
case-insensitive unique indexes, or use predicates like WHERE
deleted=false to allow duplicates after deleting the old item.

So, instead of:
  if (indexInfo->ii_Unique)
you can write:
  if (indexInfo->ii_Unique
    && indexInfo->ii_Expressions == NIL
    && indexInfo->ii_Predicate == NIL)

This would slightly simplify RelationGetIndexAttrBitmap() because you
no longer have to worry about including columns that are part of index
expressions/predicates.

I guess rd_uindexattr should be renamed to something like
rd_keyindexattr or rd_keyattr.

Is this worthwhile? I can write and submit a patch if it sounds good.

Regards,
Marti

In response to

pgsql-hackers by date

Next:From: Marti RaudseppDate: 2011-01-28 20:48:54
Subject: Re: [PATCH] Return command tag 'REPLACE X' for CREATE OR REPLACE statements.
Previous:From: Robert HaasDate: 2011-01-28 20:39:33
Subject: Re: FPI

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group