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? 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. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
