2010/1/4 Daniel Verite <daniel@xxxxxxxxxxxxxxxx>: > David Fetter wrote: > >> The DEFERRED uniqueness constraints in 8.5alpha3 fix this problem > > That fix has a drawback: when the unique constraint is violated, the rest of > the transaction runs with data that is somehow corrupted, with duplicate > values being visible. It may be uneasy to predict if and how the statements > following the temporary-ignored constraint violation will misbehave. > Generally, the transaction will ultimately fail and the mess will be cleaned > up by the rollback, but in the worst case it may not even fail, for instance > if the offending rows get deleted before the end. > No, deferrable constraints are more flexible than that, so you can have end-of-statement checks if that's what you want. A deferrable constraint has 2 modes of operation, depending on how you choose to define the constraint: 1). DEFERRABLE INITIALLY IMMEDIATE will result in the constraint being checked after each statement in the transaction. This will allow the i=i+1 UPDATE to succeed, but any UPDATE which causes uniqueness to be violated at the end of the statement will fail immediately, and you will have to rollback. 2). DEFERRABLE INITIALLY DEFERRED will cause the constraint check to be done at the end of the transaction (or when SET CONSTRAINTS is called). This will allow the constraint to be temporarily violated by statements inside a transaction, and if the duplicates are then deleted, the transaction will succeed. If you just specify DEFERRABLE, then INITIALLY IMMEDIATE is the default. See http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html This is all per the SQL spec, and also the same behaviour as Oracle. So there is quite a bit of flexibility - you may choose to have the constraint checked at any of these times: - after each row (the default for NON DEFERRABLE constraints) - after each statement (DEFERRABLE [INITIALLY IMMEDIATE]) - at the end of the transaction (DEFERRABLE INITIALLY DEFERRED) - whenever you want in a transaction using SET CONSTRAINTS Regards, Dean -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
