Re: oracle-l Digest V14 #190

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> 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