2010/1/6 Daniel Verite <daniel@xxxxxxxxxxxxxxxx>: > Dean Rasheed wrote: > >> 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 > > Thanks for clarifying that. I've just tried the different scenarios with > 8.5alpha3, and I find that these improvements are quite useful and welcome. > But still I wonder why there is that difference in behavior between NON > DEFERRABLE and DEFERRABLE INITIALLY IMMEDIATE, when the unique constraint > doesn't get deferred by using SET CONSTRAINTS. > In the first case, we get the "after each row" behavior with the pk=pk+1 > failure, as with the previous PG versions. > In the second case, we get the "after each statement" behavior which I > believe complies with the standard, contrary to the first case, and > successfully achieves the pk=pk+1 update as expected. > Personally, I would have imagined that behavior #1 would be removed once > behavior #2 was implemented, not that the two would co-exist. Is there a > reason to keep #1? > Performance is one reason (perhaps the only one?). #1 is implemented using a unique index, which is checked as each row is inserted. #2 uses triggers in addition to the unique index (conflicts are queued up to be re-checked at the end of the command/transaction). So #1 will always out-perform #2 (unless there aren't any temporary conflicts to be re-checked). > Also, I read in the current doc for 8.5: > http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html > <quote> > DEFERRABLE > NOT DEFERRABLE > > This controls whether the constraint can be deferred. A constraint that > is not deferrable will be checked immediately after every command > </quote> > > "after every command" seems to describe behavior #2, not #1. > Hmm. Yes that comment is misleading in this context. Non-deferrable unique constraints are currently checked after each row. Regards, Dean -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
