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.

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

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

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

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

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

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

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

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.

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:

"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

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

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

Peter Eisentraut wrote:

Bruce Momjian writes:

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?

No. SET CONSTRAINTS on a domain constraint should affect all constraints
on columns that use the domain.

Oh, wow. That is interesting. Does it effect only the current
transaction? Current manual page text is:

SET CONSTRAINTS sets the behavior of constraint evaluation
in the current transaction. In IMMEDIATE mode, constraints
...

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