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