PostgreSQL constraint, that gets checked on commit and not earlier [duplicate]

Question

Is it possible to create to create a unique index or other kind of constraint in PostgreSQL, that would be checked on transaction COMMIT and not a millisecond earlier?

I need an index for a pair of (record_id, ordering), so I make sure that inside a given record_id only one and no more than one records has the same ordering. Where's the problem? Well, the problem is in the way the web framework I'm using handles re-ordering the items. It looks like, when an item was moved, when its sort ordering was changed, the framework writes the new item with the new ordering value, then shortly after that it updates another one, thus creating a temporary situation where more than one record has the same ordering value. After re-ordering everything all the records gets updated and on transaction COMMIT everything should be fine again.

I'm using PostgreSQL 10 if that matters.

Shrubbery

You want DEFERRABLE INITIALLY DEFERRED See: https://mcmap.net/q/428440/-how-do-i-get-a-column-with-consecutive-increasing-numbers-without-having-any-numbers-missing – Taft

Thanks, looks exactly like it. Any references to docs or tutorials? I've looked for such things under "CREATE INDEX" in official PostgreSQL docs but not found it. I've seen "initially deferred" in sql dumps, though... – Shrubbery

An index is not a constraint (and does not have the DEFERRABLE property). In postgres, you can promote an index to a constraint using ALTER TABLE ... USING ... – Taft

Related: #10032772 – Vernavernacular

Thank you @ErwinBrandstetter ; I guess mods could mark this as a duplicate of that question... – Shrubbery

I can do that, too. – Vernavernacular

Answer 1

Use SET CONSTRAINTS command:

SET CONSTRAINTS

SET CONSTRAINTS — set constraint check timing for the current transaction

Synopsis

SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

Description

SET CONSTRAINTS sets the behavior of constraint checking within the current transaction. IMMEDIATE constraints are checked at the end of each statement. DEFERRED constraints are not checked until transaction commit. Each constraint has its own IMMEDIATE or DEFERRED mode.

Upon creation, a constraint is given one of three characteristics: DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY IMMEDIATE, or NOT DEFERRABLE. The third class is always IMMEDIATE and is not affected by the SET CONSTRAINTS command. The first two classes start every transaction in the indicated mode, but their behavior can be changed within a transaction by SET CONSTRAINTS.

SET CONSTRAINTS with a list of constraint names changes the mode of just those constraints (which must all be deferrable). Each constraint name can be schema-qualified. The current schema search path is used to find the first matching name if no schema name is specified. SET CONSTRAINTS ALL changes the mode of all deferrable constraints.

When SET CONSTRAINTS changes the mode of a constraint from DEFERRED to IMMEDIATE, the new mode takes effect retroactively: any outstanding data modifications that would have been checked at the end of the transaction are instead checked during the execution of the SET CONSTRAINTS command. If any such constraint is violated, the SET CONSTRAINTS fails (and does not change the constraint mode). Thus, SET CONSTRAINTS can be used to force checking of constraints to occur at a specific point in a transaction.

Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE constraints are affected by this setting. NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement). Uniqueness and exclusion constraints that have not been declared DEFERRABLE are also checked immediately.

The firing of triggers that are declared as “constraint triggers” is also controlled by this setting — they fire at the same time that the associated constraint should be checked.

---

You can also find that in the reference documentation of (for example) CREATE TABLE

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

The constraint can be:

• INITIALLY DEFERRED | INITIALLY IMMEDIATE
• DEFERRABLE | NOT DEFERRABLE

Aarhus