Thread: SET CONSTRAINTS not schema-aware
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
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, Pennsylvania19073
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, Pennsylvania19073
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>
iseffectively
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.
>
>
"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, Pennsylvania19073
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,
Pennsylvania19073