Thread: set constraints behavior

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

On Fri, 3 May 2002, Neil Conway wrote:

> 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:

I think you missed Syntax Rule 2:
"The constraint specified by <constraint name> shall be DEFERRABLE"

On Fri, 3 May 2002 10:39:28 -0700 (PDT)
"Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote:
> 
> On Fri, 3 May 2002, Neil Conway wrote:
> > 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:
> 
> I think you missed Syntax Rule 2:
> "The constraint specified by <constraint name> shall be DEFERRABLE"

Ah, okay. Yeah, I missed that part. Stupid standards, they're
practically unreadable :-)

(My other question, regarding transaction and SET CONSTRAINTS,
is still valid)

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

On Fri, 3 May 2002, Neil Conway wrote:

> On Fri, 3 May 2002 10:39:28 -0700 (PDT)
> "Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote:
> >
> > On Fri, 3 May 2002, Neil Conway wrote:
> > > 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:
> >
> > I think you missed Syntax Rule 2:
> > "The constraint specified by <constraint name> shall be DEFERRABLE"
>
> Ah, okay. Yeah, I missed that part. Stupid standards, they're
> practically unreadable :-)
>
> (My other question, regarding transaction and SET CONSTRAINTS,
> is still valid)

Didn't answer that part because I'm not sure what's best for that
given the way we handle "out of transaction" statements (the
other I remembered from past readings and rechecked).

Neil Conway <nconway@klamath.dyndns.org> writes:
> 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?

Well, what definition do you propose?  I don't think there's currently
any usefulness to SET CONSTRAINTS outside a transaction block, so we
could change its behavior without breaking anything.

Given that we don't define transaction boundaries the same way SQL92
does (BEGIN isn't SQL), I'm not sure that exact spec compliance is
the right consideration here anyway.

Note however that there are proposals floating around to allow a more
spec-compliant transaction behavior --- eg, a SET variable to cause an
"implicit BEGIN" on any SQL command outside a transaction block.
It'd be a good idea to keep that in mind while thinking about how SET
CONSTRAINTS ought to behave.
        regards, tom lane