Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

by

I remember having raised an almost identical point when PG9 was in alpha state. Here was the answer from Tom Lane (high-profile PG core developer):
http://archives.postgresql.org/pgsql-general/2010-01/msg00221.php

In short: won’t fix.

Not to say that I agree with your suggestion that the current behavior is a bug. Look at it from the opposite angle: it’s the behavior of NOT DEFERRABLE that is incorrect.

In fact, the constraint violation in this UPDATE should never happen in any case, since at the end of the UPDATE the constraint is satisfied. The state at the end of the command is what matters. The intermediate states during the execution of a single statement should not be exposed to the user.

It seems like the PostgreSQL implements the non deferrable constraint by checking for duplicates after every row updated and failing immediately upon the first duplicate, which is essentially flawed. But this is a known problem, probably as old as PostgreSQL.
Nowadays the workaround for this is precisely to use a DEFERRABLE constraint. And there is some irony in that you’re looking at it as deficient because it fails to fail, while somehow it’s supposed to be the solution to the failure in the first place!

Summary of the status quo since PostgreSQL 9.1

  • NOT DEFERRABLE UNIQUE or PRIMARY KEY constraints are checked after each row.

  • DEFERRABLE constraints set to IMMEDIATE (INITIALLY IMMEDIATE or via SET CONSTRAINTS) are checked after each statement.

  • DEFERRABLE constraints set to DEFERRED (INITIALLY DEFERRED or via SET CONSTRAINTS) are checked after each transaction.

Note the special treatment of UNIQUE / PRIMARY KEY constraints.
Quoting the manual page for CREATE TABLE:

A constraint that is not deferrable will be checked immediately after every command.

While it states further down in the Compatibility section under Non-deferred uniqueness constraints:

When a UNIQUE or PRIMARY KEY constraint is not deferrable,
PostgreSQL checks for uniqueness immediately whenever a row is
inserted or modified. The SQL standard says that uniqueness should be
enforced only at the end of the statement; this makes a difference
when, for example, a single command updates multiple key values. To
obtain standard-compliant behavior, declare the constraint as
DEFERRABLE but not deferred (i.e., INITIALLY IMMEDIATE). Be aware
that this can be significantly slower than immediate uniqueness checking.

Bold emphasis mine.

If you need any FOREIGN KEY constraints to reference the column(s), DEFERRABLE is not an option because (per documentation):

The referenced columns must be the columns of a non-deferrable unique
or primary key constraint in the referenced table.

More Related Contents:

  1. PL/pgSQL checking if a row exists
  2. Compute percents from SUM() in the same SELECT sql query
  3. Postgres analogue to CROSS APPLY in SQL Server
  4. Perform this hours of operation query in PostgreSQL
  5. NOT NULL constraint over a set of columns
  6. How to find a table having a specific column in postgresql
  7. Continuing a transaction after primary key violation error
  8. How to write a constraint concerning a max number of rows in postgresql?
  9. Postgres constraint for unique datetime range
  10. Postgres function returning table not returning data in columns
  11. Check if NULL exists in Postgres array
  12. How to use SQL LIKE condition with multiple values in PostgreSQL?
  13. How to create a new database with the hstore extension already installed?
  14. How to perform a select query in a DO block?
  15. Join a count query on generate_series() and retrieve Null values as ‘0’
  16. PostgreSQL – dynamic value as table name [duplicate]
  17. Is SELECT or INSERT in a function prone to race conditions?
  18. Index for finding an element in a JSON array
  19. Turn off constraints temporarily (MS SQL)
  20. How to avoid multiple function evals with the (func()).* syntax in a query?
  21. Time zone storage in data type “timestamp with time zone”
  22. Is there something like a zip() function in PostgreSQL that combines two arrays?
  23. PostgreSQL parameterized Order By / Limit in table function
  24. Get the default values of table columns in Postgres?
  25. Division of integers returns 0
  26. PostgreSQL: how to convert from Unix epoch to date?
  27. Postgres – Function to return the intersection of 2 ARRAYs?
  28. Count cumulative total in Postgresql
  29. Prepared Statement on Postgresql in Rails
  30. Temporal database design, with a twist (live vs draft rows)

Leave a Comment