Re: augmenting MultiXacts to improve foreign keys

On Aug9, 2011, at 19:01 , Alvaro Herrera wrote:
> Note that this means that UPDATE would always have to check whether key
> columns are being modified. (I loosely talk about "key columns" here
> referring to columns involving unique indexes. On tables with no unique
> indexes, I think this would have to mean all columns, but I haven't
> thought much about this bit.)
>
> To implement this, we need to augment MultiXact to store the lock type
> that each comprising Xid holds on the tuple. Two bits per Xid are
> needed. My thinking is that we could simply extend the "members" to add
> a byte for every four members. This should be relatively simple, though
> this forecloses the option of using MultiXact for some other purpose
> than locking tuples. This being purely theoretical, I don't have a
> problem with that.

Hm, I'm still not a huge fan of having the only the choice between locking
all columns, or all columns that are part of a unique index. For multiple
reasons.

First, I'd like to see us support FKs which reference non-unqiue columns.
As long as we restrict such FK constraints to ON UPDATE/DELETE RESTRICT
(or NO ACTION), there's no conceptual difficulty in doing that. Yeah, I
know that there'll probably be a lot of push-back on the grounds of standards
compliance, but I still believe it'd be a nice feature.

Second, there are lots of reasons for adding UNIQUE constraints to columns
beside being on the referencing side of a FK constraint. If we make the
lock strength taken by UPDATE depend on whether or not the UPDATE modified a
column included in a UNIQUE constraint, we force people to drop UNIQUE
constraints to avoid deadlocks, and thus indirectly support sloppy schema
design.

Couldn't we simply give the user a way to specify, per column, whether or
not that column is a "KEY" column? Creating a foreign key constraint could
still implicitly mark all referenced columns as KEY columns, but columns
would no longer be "KEY" columns simply because they're part of a UNIQUE
constraint. Users would be free to add arbitrary columns to the set of
"KEY" columns by doing ALTER TABLE ALTER COLUMN SET KEY.

best regards,
Florian Pflug