SET CONSTRAINTS doesn't allow you to schema-qualify a constraint name.
Here's an example:

create schema test;
create table test.foo (a int primary key);
create table test.bar (b int primary key, c int constraint myconstraint references test.foo);

-- This succeeds even though schema "test" is not in the search path:
set constraints myconstraint immediate;

-- This is what should work:
set constraints test.myconstraint immediate;
ERROR:  parser: parse error at or near "." at character 21

Comments?

-- 
Peter Eisentraut   peter_e@gmx.net


Sun, 11 May 2003 18:46:32 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
SET CONSTRAINTS not schema-aware
On Sun, May 11, 2003 at 18:46:32 +0200,
  Peter Eisentraut <peter_e@gmx.net> wrote:
> SET CONSTRAINTS doesn't allow you to schema-qualify a constraint name.
> Here's an example:
> 
> create schema test;
> create table test.foo (a int primary key);
> create table test.bar (b int primary key, c int constraint myconstraint references test.foo);
> 
> -- This succeeds even though schema "test" is not in the search path:
> set constraints myconstraint immediate;
> 
> -- This is what should work:
> set constraints test.myconstraint immediate;
> ERROR:  parser: parse error at or near "." at character 21
> 
> Comments?

I am pretty sure I saw some comments in the discussion about sequence
naming that constraints are per table and giving them a schema name
makes no sense. The table they are for has the schema name in it.


Sun, 11 May 2003 12:49:44 -0500
Bruno Wolff III <bruno@wolff.to>
Re: SET CONSTRAINTS not schema-aware
Bruno Wolff III <bruno@wolff.to> writes:
>   Peter Eisentraut <peter_e@gmx.net> wrote:
>> SET CONSTRAINTS doesn't allow you to schema-qualify a constraint name.

> I am pretty sure I saw some comments in the discussion about sequence
> naming that constraints are per table and giving them a schema name
> makes no sense. The table they are for has the schema name in it.

Yeah.  We had that discussion at some point during the 7.3 development
cycle, and concluded we liked table-local naming for constraints better
than the SQL spec's global constraint names.

SET CONSTRAINTS still does what it used to do, which is to alter the
behavior of all constraints with the given name.  We should probably
expand the syntax so that a particular table name can be mentioned.

			regards, tom lane


Sun, 11 May 2003 16:01:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: SET CONSTRAINTS not schema-aware 
Tom Lane writes:

> Yeah.  We had that discussion at some point during the 7.3 development
> cycle, and concluded we liked table-local naming for constraints better
> than the SQL spec's global constraint names.

Then why does pg_constraint store a schema OID?  The comments say it's for
SQL compatibility, so where else does it need to be compatible besides
this case?

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 12 May 2003 12:04:44 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: SET CONSTRAINTS not schema-aware 
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> Yeah.  We had that discussion at some point during the 7.3 development
>> cycle, and concluded we liked table-local naming for constraints better
>> than the SQL spec's global constraint names.

> Then why does pg_constraint store a schema OID?

I think we left it there for possible future use with assertions, which
are not tied to any particular table.

			regards, tom lane


Mon, 12 May 2003 09:44:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: SET CONSTRAINTS not schema-aware 
Tom Lane wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
> >   Peter Eisentraut <peter_e@gmx.net> wrote:
> >> SET CONSTRAINTS doesn't allow you to schema-qualify a constraint name.
> 
> > I am pretty sure I saw some comments in the discussion about sequence
> > naming that constraints are per table and giving them a schema name
> > makes no sense. The table they are for has the schema name in it.
> 
> Yeah.  We had that discussion at some point during the 7.3 development
> cycle, and concluded we liked table-local naming for constraints better
> than the SQL spec's global constraint names.
> 
> SET CONSTRAINTS still does what it used to do, which is to alter the
> behavior of all constraints with the given name.  We should probably
> expand the syntax so that a particular table name can be mentioned.

Is this a TODO?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 14 May 2003 23:13:48 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: SET CONSTRAINTS not schema-aware
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> SET CONSTRAINTS still does what it used to do, which is to alter the
>> behavior of all constraints with the given name.  We should probably
>> expand the syntax so that a particular table name can be mentioned.

> Is this a TODO?

Nobody objected to my statement, so I guess so ...

			regards, tom lane

Wed, 14 May 2003 23:24:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: SET CONSTRAINTS not schema-aware 
Tom Lane writes:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> SET CONSTRAINTS still does what it used to do, which is to alter the
> >> behavior of all constraints with the given name.  We should probably
> >> expand the syntax so that a particular table name can be mentioned.
>
> > Is this a TODO?
>
> Nobody objected to my statement, so I guess so ...

I just hate to see us breaking the SQL standard for no technical reason.

-- 
Peter Eisentraut   peter_e@gmx.net


Thu, 15 May 2003 17:48:32 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: SET CONSTRAINTS not schema-aware 
Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Tom Lane wrote:
> > >> SET CONSTRAINTS still does what it used to do, which is to alter the
> > >> behavior of all constraints with the given name.  We should probably
> > >> expand the syntax so that a particular table name can be mentioned.
> >
> > > Is this a TODO?
> >
> > Nobody objected to my statement, so I guess so ...
> 
> I just hate to see us breaking the SQL standard for no technical reason.

Does it actually break the standard of just extend it.  I don't see any
problem with extending it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 15 May 2003 12:07:30 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: SET CONSTRAINTS not schema-aware
On Thu, May 15, 2003 at 12:07:30 -0400,
  Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> Peter Eisentraut wrote:
> > Tom Lane writes:
> > 
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > Tom Lane wrote:
> > > >> SET CONSTRAINTS still does what it used to do, which is to alter the
> > > >> behavior of all constraints with the given name.  We should probably
> > > >> expand the syntax so that a particular table name can be mentioned.
> > >
> > > > Is this a TODO?
> > >
> > > Nobody objected to my statement, so I guess so ...
> > 
> > I just hate to see us breaking the SQL standard for no technical reason.
> 
> Does it actually break the standard of just extend it.  I don't see any
> problem with extending it.

I think one way to extend it would be to allow three formats for naming
the constraint.
Using just the constraint name would do all tables with that constraint
name. Using table_name.constraint_name would only check in the first
table named table_name in the current schema path for a matching constraint.
Using schema.table.constraint would only look at the specific table for
a matching constraint.

Thu, 15 May 2003 11:57:05 -0500
Bruno Wolff III <bruno@wolff.to>
Re: SET CONSTRAINTS not schema-aware
from the prelim sql92 standard:

Syntax Rules

         1) If a <constraint name definition> is contained in a <schema 
def-
            inition>, and if the <constraint name> contains a <schema 
name>,
            then that <schema name> shall be the same as the specified or
            implicit <schema name> of the containing <schema definition>.

         2) The <qualified identifier> of <constraint name> shall be 
differ-
            ent from the <qualified identifier> of the <constraint name> 
of
            any other constraint defined in the same schema.

Further along, talking about drop schema, it says:

4) Let A be the <constraint name> of any assertion contained in
            S. The following <drop assertion statement> is effectively 
exe-
            cuted:

              DROP ASSERTION A

S is the schema, so it would appear they do mean that constraints are 
"contained" so to speak by a schema.

On Thu, 15 May 2003, Bruce Momjian wrote:

> Peter Eisentraut wrote:
> > Tom Lane writes:
> > 
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > Tom Lane wrote:
> > > >> SET CONSTRAINTS still does what it used to do, which is to alter the
> > > >> behavior of all constraints with the given name.  We should probably
> > > >> expand the syntax so that a particular table name can be mentioned.
> > >
> > > > Is this a TODO?
> > >
> > > Nobody objected to my statement, so I guess so ...
> > 
> > I just hate to see us breaking the SQL standard for no technical reason.
> 
> Does it actually break the standard of just extend it.  I don't see any
> problem with extending it.
> 
> 


Thu, 15 May 2003 11:00:05 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: SET CONSTRAINTS not schema-aware
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> S is the schema, so it would appear they do mean that constraints are 
> "contained" so to speak by a schema.

Right.  In SQL92 constraint names have to be unique within the table's
schema.  Postgres allows two different tables to have similarly-named
constraints, and that difference is the root of the issue.

			regards, tom lane

Thu, 15 May 2003 13:15:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: SET CONSTRAINTS not schema-aware 
Tom Lane writes:

> Right.  In SQL92 constraint names have to be unique within the table's
> schema.  Postgres allows two different tables to have similarly-named
> constraints, and that difference is the root of the issue.

But that should not prevent us from assigning an explicit schema to each
constraint, as we in fact currently do.  This issue is a bit more tricky
than it seems.  For example, constraints may also belong to a domain, so
even if we allowed SET CONSTRAINTS a.b.c it is still not clear that "b" is
a table.

-- 
Peter Eisentraut   peter_e@gmx.net


Sat, 17 May 2003 00:00:19 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: SET CONSTRAINTS not schema-aware 

OK, added to TODO:

	Allow SET CONSTRAINTS to be qualified by schema/table

Peter, I assume SET CONSTRAINTS can't control a domain's constraints ---
it isn't actually a data object in the transaction.  Am I right?

---------------------------------------------------------------------------

Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > Right.  In SQL92 constraint names have to be unique within the table's
> > schema.  Postgres allows two different tables to have similarly-named
> > constraints, and that difference is the root of the issue.
> 
> But that should not prevent us from assigning an explicit schema to each
> constraint, as we in fact currently do.  This issue is a bit more tricky
> than it seems.  For example, constraints may also belong to a domain, so
> even if we allowed SET CONSTRAINTS a.b.c it is still not clear that "b" is
> a table.
> 
> -- 
> Peter Eisentraut   peter_e@gmx.net
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 2 Jun 2003 13:26:17 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: SET CONSTRAINTS not schema-aware