On Thu, Aug 25, 2016 at 12:29 AM, Francisco Olarte <folarte@xxxxxxxxxxxxxx> wrote: > That been said, I'm not sure making it ( deferred constraint act like > immediate ones during upserts ) work is even a good idea. If it can be > conditionally enabled with a simple set and implemented in very few ( > < 20 ) lines of code, ok for me , otherwise I would prefer the reduced > bug surface. But UPSERT doesn't support a DEFERRABLE constraint, even when the system is set to IMMEDIATE enforcement of constraints. I think that it's not hard to see that UPSERT on a deferred (not just DEFERRABLE) constraint doesn't make much sense, but that's not how I understood the OP's complaint. IMMEDIATE enforcement just makes the second pass for verification occur immediately, rather than at the end of the transaction. That's a very small difference, at least from an implementation perspective; in particular, the constraint will not actually behave like (say) a uniqueness constraint that was not declared DEFERRABLE in the first place. Enforcement will not occur in the path of insertion, as it does for B-Tree. The reason for the broad restriction on DEFERRABLE constraints is that it's not clear how the implementation of UPSERT should handle *somebody else's* unconfirmed DEFERRABLE-mode insertion (this may happen even if the upserter is forbidden from treating the constraint as DEFERRABLE). I'm not saying that it's impossible, but it's far more complicated than it first appears, if you expect UPSERT to worry about lock starvation, "unprincipled deadlocks" [1], and other problems like that. [1] https://wiki.postgresql.org/wiki/Value_locking#.22Unprincipled_Deadlocking.22_and_value_locking -- Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
