Hi all,
The SQL92 spec has this to say about SET CONSTRAINTS DEFERRED:
a) If ALL is specified, then the constraint mode in TXN of all constraints that are DEFERRABLE is set to
deferred.
b) Otherwise, the constraint mode in TXN for the constraints identified by the <constraint name>s in the
<constraintname list> is set to deferred.
(section 14.2, page 401)
My reading of this: if you specify ALL, only the constraints marked
as DEFERRABLE are affected. If you specify a specific constraint,
it is deferred, whether the constraint is marked as DEFERRABLE or
not.
Current Postgres behavior is incompatible with this interpretation:
nconway=> create table pk (id serial primary key);
NOTICE: CREATE TABLE will create implicit sequence 'pk_id_seq' for SERIAL column 'pk.id'
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pk_pkey' for table 'pk'
CREATE
nconway=> create table fk (pk_ref int constraint my_constraint references pk);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
nconway=> begin;
BEGIN
nconway=> set constraints my_constraint deferred;
ERROR: Constraint 'my_constraint' is not deferrable
Second question: SQL92 also specifies this for SET CONSTRAINTS --
1) If an SQL-transaction is currently active, then let TXN be the currently active SQL-transaction. Otherwise,
letTXN be the next SQL-transaction for the SQL-agent.
(section 14.2, page 400)
In PostgreSQL, SET CONSTRAINTS only affects the current
transaction. Is it possible to make this more compliant?
If not, it should be noted in the docs for SET CONSTRAINTS.
Cheers,
Neil
--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC