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