Re: oracle-l Digest V14 #190

From: Stéphane Faroult <sfaroult_at_roughsea.com>
Date: Tue, 25 Jul 2017 05:15:07 +0800
Message-ID: <f83f2346-1ddc-d75b-43c5-93647e74cdc6_at_roughsea.com>



Franck,

   By definition a PK identifies a row, and so cannot change. You can only delete the row and recreate it with another PK, because different PK means different whatever the row represents. I fully agree about what you say about candidate keys, or unique values - typically, your professional email address or phone number changes with every job.

Unique constraints can in PostgreSQL be created "deferrable initially deferred"
(https://www.postgresql.org/docs/9.4/static/sql-createtable.html) although this seems to be pretty recent, I have seen older docs saying that only FKs could be created with this option. So I think (I have not tested it) that there is a solution to the real problem (especially the process that breaks) that you are mentioning. Note that from what I understand constraint checking is postponed to the end of the transaction, which in some cases (fortunately, there are still people *not* using autotransactions) is laxer than the end of the statement. It might be a source for other surprises.

If PostgreSQL *can* more or less behave like every other DBMS, why this wasn't chosen as the default behaviour totally baffles me. The reason for keeping a non-standard, badly chosen behaviour is usually upward compatibility. In that case, I don't see which older PostgreSQL process the change could break (it could make complicated processes to work around the "feature" unnecessary, but that's the fate of most improvements), while I see very clearly the benefit when migrating from other systems (do you hear me PostgreSQL people?). I have seen other products (I'm thinking of SQLite, not exactly in the same category but much used) quietly ditching a bad non-standard behaviour without any fuss (SQLite used to ignore NULLs in an IN list - now the condition is false, or at least "not true", as with every other product). Compatibility also means compatible default values.

Stéphane Faroult

On 25/07/2017 02:10, Franck Pachot wrote:
> Hi Stéphane,
>
> I take the penance as soon as I find time ;)
> I'm not sure to find something about PK update. It's one candidate key
> and some candidate keys may change. Problems come when we have foreign
> keys or other reference to it, such as Change Data Record. One key
> must be Static IMO, and naming it the primary makes sense as that one
> must be not null, but if you have a reference from Chris Date about
> that I will be happy to see it.
>
> By the way this was just a quick example that fit a tweet. Real cases
> are more exchanging two values in a column that participates to a
> unique constraint. Example: ordered items, stored with ID and sequence
> number. Moving up one item can do this kind of update which may
> temporarily allow duplicates until the end of the statement execution.
>
> My problem with Postgres implementation is more about things that work
> for years and suddenly break because physical order changes. That
> probably comes from a bad experience long time ago on a billing system
> (developed with a framework called 'magic') which did a lot of 'order
> by rowid' to get rows in the order they were inserted =:0
>
> Regards,
> Franck
> Le lun. 24 juil. 2017 à 11:17, Stéphane Faroult <sfaroult_at_roughsea.com
> <mailto:sfaroult_at_roughsea.com>> a écrit :
>
> Sayan,
>
> Not really, because ACID is at the transaction level, and here we
> are talking of what happens at the statement level. Kind of
> sub-atomic :-).
>
> I agree that this kind of behaviour is rather disturbing.
> Especially in Franck's example, what is shocking is that it works
> in one case and not in the other one. Of course it's perfectly
> understandable when you consider that PostgreSQL takes rows in
> sequence - but order has no particular meaning in SQL.
> Potentially, a change in the optimizer, even a data reload, could
> make fail an update that used to work simply because row updates
> are no longer performed in the same order. I would have liked to
> see consistency throughout - failure in both cases, or success in
> both cases.
>
> It would make more sense for me if all constraints were created
> deferred by default - which cannot be done with a PK. Now,
> Franck's example is a bit unfortunate as it uses a primary key.
> Updating a primary key? I hope he will reread Chris Date's
> complete works as a penance.
>
> SF
>
>
> On 24/07/2017 16:55, Sayan Malakshinov wrote:
>> Stéphane,
>>
>> This behavior breaks A from ACID...
>>
>> Best regards,
>> Sayan Malakshinov
>> http://orasql.org
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jul 24 2017 - 23:15:07 CEST

Original text of this message