Re: [HACKERS] FOR SHARE LOCK clause ? - Mailing list pgsql-hackers

Vadim Mikheev wrote:
> Clark Evans wrote:
> > > Ok, in multi-version systems readers never lock
> > > selected rows and so never block writers. Nice but
> > > cause problems in some cases: if you want
> > > to implement referential integrity at the
> > > application level then you'll have to use
> > > LOCK TABLE IN SHARE MODE or SELECT FOR UPDATE to
> > > prevent updation of primary keys etc. Not so good...
> >
> > I was wondering if there is another perspective
> > to look upon this problem.
> >
> > Assertion:
> >
> > The primary difference between "primary key" and
> > "unique key" is that primary keys are _never_
>                        ^^^^^^^^^^^^^^^^^^^^^^^^
> > updated (during transaction processing) where
>   ^^^^^^^
> > unique keys may be.
> 
> 1. Is this standard requirement?

A DBA at Ford that I had many afternoon chats with
wished he could make the Oracle database prevent
updates of primary keys, short of a full table 
lock -- which is a privilige.  I'm sure that there
are others that might disagree.

The other thing that the DBA said he'd love was
a "garbage collector".  Where a delete on row would
only "hide" the row after all constraint checks 
succeeded.  In this way, data referenced by systems
that are not attached to the database have a chance
to be checked for data integrety concerns.

Although you may be able to find DBA's that would
argue about these points, the idea that primary
keys are updated only on rare exception is a solid 
modeling practice (it's one of Oracle's Ten Commandments).

> 2. Note that foreign keys may reference unique key,
>    not just primary one...

You may be able to do it... but I'm not sure that
it makes sence.  Also, unique keys can have NULLS,
primary keys cannot have any NULL allowable columns

The idea of a primary key is to identify column(s)
as a constant pointer to the object in question,
so that other objects can use that pointer.

The idea of a unique index is to enforce 
constraints that a combination of columns must
be unique.  The mechinism does not necessarily
mean that the columns are "constant", and thus
make good pointers.  For instance, a full name 
may very well be unique in a small company - if
this assumption is made in the reporting code, 
then a unique key is warranted, with clear 
documentation explaining that the reporting
code assumes this fact.  However, it would 
be a bad idea to use the full name as a 
pointer, as a marriage could cause havoc.

> 3. I told about implementing referential
>    integrity _at_the_application_level_, not by the
>    DB system itself - it's up to the user decide
>    what's allowed and what's not, in this case.

I think you are right here, although allowing the
user to make an application level decision and
then configure the database to automagically 
enforce this decision is golden.

More e-mail to follow...