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 <constraint name
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, let TXN 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 <[EMAIL PROTECTED]>
PGP Key ID: DB3C29FC
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
