Tom Lane wrote: > "Daniel Verite" <dan...@manitou-mail.org> writes: > > 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? > > 1. Performance. The cost of #2 is very large, and the number of cases > where you actually need it is not.
Per Dean's explanation upthread, It looks like an additional cost for #2 would occur mostly when temporary conflicts occur, that is, when it's needed. I've tried UPDATEs of a primary key in batches of 1M rows with 8.5, and in the general case of no conflict, I get #2 being about 8-15% slower than #1. I've seen no difference for INSERTs. When there are temporary conflicts, #2 is slower but succeeds whereas #1 fails, so #2 is the winner. When there are persistant conflicts, #2 fails slower than #1, but do we really care? > 2. Backwards compatibility. Some apps might be depending on the details > of the behavior. Apparently, the occurrence of conflicts during the execution is mostly unpredictable anyway, from the point of view of the end user. For example I was under the illusion that UPDATE...SET pk=pk-1 always worked, but I've discovered while testing that it wasn't the case. Conversely depending on it to fail, for this update or a similar update, thats seems insane for an app. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
