Re: [HACKERS] FOR SHARE LOCK clause ?
От | Clark Evans |
---|---|
Тема | Re: [HACKERS] FOR SHARE LOCK clause ? |
Дата | |
Msg-id | 3693075C.6D48C63D@manhattanproject.com обсуждение исходный текст |
Ответ на | FOR SHARE LOCK clause ? (Vadim Mikheev <vadim@krs.ru>) |
Список | 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...
В списке pgsql-hackers по дате отправления: