Thread: set constraints docs page
Hi, I notice on the SET CONSTRAINTS doc page, it says SET CONSTRAINTS <constraint> ... But it doesn't at all make it clear what <constraint> is, since cosntraint names are per-relation I thought? Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> I notice on the SET CONSTRAINTS doc page, it says SET CONSTRAINTS
> <constraint> ...
> But it doesn't at all make it clear what <constraint> is, since cosntraint
> names are per-relation I thought?
Looking at the code, it will set the mode for *all* FKs with the same
constraint name, regardless of which relation they are on.
Feel free to send a doc patch...
regards, tom lane
On Wed, 13 Aug 2003, Christopher Kings-Lynne wrote: > Hi, > > I notice on the SET CONSTRAINTS doc page, it says SET CONSTRAINTS > <constraint> ... > > But it doesn't at all make it clear what <constraint> is, since cosntraint > names are per-relation I thought? It's a constraint name. IIRC, it happens to affect all such named constraints currently. We should probably allow <tablename>.<constraint> (and <schema>.<tablename>.<constraint>) as well. Too late for 7.4, but this can happen for 7.5 if there aren't any objections.
Stephan Szabo writes: > It's a constraint name. IIRC, it happens to affect all such named > constraints currently. We should probably allow <tablename>.<constraint> > (and <schema>.<tablename>.<constraint>) as well. Too late for 7.4, but > this can happen for 7.5 if there aren't any objections. I object. -- Peter Eisentraut peter_e@gmx.net
> > It's a constraint name. IIRC, it happens to affect all such named > > constraints currently. We should probably allow <tablename>.<constraint> > > (and <schema>.<tablename>.<constraint>) as well. Too late for 7.4, but > > this can happen for 7.5 if there aren't any objections. > > I object. Thanks for the helpful objection. To what do you object specifically and why? Chris
Christopher Kings-Lynne writes: > > > It's a constraint name. IIRC, it happens to affect all such named > > > constraints currently. We should probably allow <tablename>.<constraint> > > > (and <schema>.<tablename>.<constraint>) as well. Too late for 7.4, but > > > this can happen for 7.5 if there aren't any objections. > > > > I object. > > Thanks for the helpful objection. To what do you object specifically and > why? I object to creating gratuitous incompatibilities with the SQL standard, which will obstruct legitimate features down the road. The SQL standard says it is <schema>.<constraint>. -- Peter Eisentraut peter_e@gmx.net
----- Original Message ----- From: "Peter Eisentraut" <peter_e@gmx.net> To: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> Cc: "Stephan Szabo" <sszabo@megazone.bigpanda.com>; "Hackers" <pgsql-hackers@postgresql.org> Sent: Tuesday, August 19, 2003 3:51 AM Subject: Re: [HACKERS] set constraints docs page > Christopher Kings-Lynne writes: > > > > > It's a constraint name. IIRC, it happens to affect all such named > > > > constraints currently. We should probably allow <tablename>.<constraint> > > > > (and <schema>.<tablename>.<constraint>) as well. Too late for 7.4, but > > > > this can happen for 7.5 if there aren't any objections. > > > > > > I object. > > > > Thanks for the helpful objection. To what do you object specifically and > > why? > > I object to creating gratuitous incompatibilities with the SQL standard, > which will obstruct legitimate features down the road. The SQL standard > says it is <schema>.<constraint>. > Is there a case for enforcing uniqueness on constraint names, then? andrew
"Andrew Dunstan" <andrew@dunslane.net> writes:
>> I object to creating gratuitous incompatibilities with the SQL standard,
>> which will obstruct legitimate features down the road. The SQL standard
>> says it is <schema>.<constraint>.
> Is there a case for enforcing uniqueness on constraint names, then?
Other than "SQL92 says so"? Very little. This seems to me to be a
design error in the spec. Per-table constraint names are easier to
work with --- if they're global across a schema then you have a serious
problem avoiding collisions.
The spec does have a notion of "assertions", which are constraints not
tied to any specific table; for those I suppose you need a
schema-wide namespace. I do not foresee us supporting such things
anytime soon though.
regards, tom lane
On Tue, 19 Aug 2003, Peter Eisentraut wrote: > Christopher Kings-Lynne writes: > > > > > It's a constraint name. IIRC, it happens to affect all such named > > > > constraints currently. We should probably allow <tablename>.<constraint> > > > > (and <schema>.<tablename>.<constraint>) as well. Too late for 7.4, but > > > > this can happen for 7.5 if there aren't any objections. > > > > > > I object. > > > > Thanks for the helpful objection. To what do you object specifically and > > why? > > I object to creating gratuitous incompatibilities with the SQL standard, > which will obstruct legitimate features down the road. The SQL standard > says it is <schema>.<constraint>. We *already* have the incompatibility with the SQL standard because of the fact we allow non-unique constraint names in the same schema; <schema>.<constraint> does not uniquely identify a constraint in PostgreSQL currently. Tom objected to following the spec on this regard back when the issue was brought up for adding checks on the constraint names on the grounds that table based constraint names were more natural.
Tom Lane wrote: > "Andrew Dunstan" <andrew@dunslane.net> writes: > >> I object to creating gratuitous incompatibilities with the SQL standard, > >> which will obstruct legitimate features down the road. The SQL standard > >> says it is <schema>.<constraint>. > > > Is there a case for enforcing uniqueness on constraint names, then? > > Other than "SQL92 says so"? Very little. This seems to me to be a > design error in the spec. Per-table constraint names are easier to > work with --- if they're global across a schema then you have a serious > problem avoiding collisions. I assume that SQL99 and later don't specify anything different than what SQL92 calls for in this regard? Without any meaningful guidance from the spec, the best we can do is support per-table constraint names and provide optional (via a GUC variable) support for SQL92-compliant constraint names. Let the DBA decide which (if not both) is best for his situation. Inasmuch as one of our "selling points" is our compliance with the SQL spec, I see little reason to entirely avoid compliance with the spec on this issue -- just make it possible to do something else when/if necessary. The two approaches aren't necessarily mutually exclusive (though SQL99 compliance on constraint names would obviously make it unnecessary to specify a tablename along with a constraint name), so I see little problem here. But the current arrangement is obviously untenable, because it allows you to create a situation (multiple constraints by the same name) that you can't reasonably extricate yourself from. -- Kevin Brown kevin@sysexperts.com
Kevin Brown wrote: > Tom Lane wrote: > > "Andrew Dunstan" <andrew@dunslane.net> writes: > > >> I object to creating gratuitous incompatibilities with the SQL standard, > > >> which will obstruct legitimate features down the road. The SQL standard > > >> says it is <schema>.<constraint>. > > > > > Is there a case for enforcing uniqueness on constraint names, then? > > > > Other than "SQL92 says so"? Very little. This seems to me to be a > > design error in the spec. Per-table constraint names are easier to > > work with --- if they're global across a schema then you have a serious > > problem avoiding collisions. > > I assume that SQL99 and later don't specify anything different than what > SQL92 calls for in this regard? > > Without any meaningful guidance from the spec, the best we can do is > support per-table constraint names and provide optional (via a GUC > variable) support for SQL92-compliant constraint names. Let the DBA > decide which (if not both) is best for his situation. Inasmuch as > one of our "selling points" is our compliance with the SQL spec, I see > little reason to entirely avoid compliance with the spec on this issue -- > just make it possible to do something else when/if necessary. > > The two approaches aren't necessarily mutually exclusive (though SQL99 > compliance on constraint names would obviously make it unnecessary to > specify a tablename along with a constraint name), so I see little > problem here. But the current arrangement is obviously untenable, > because it allows you to create a situation (multiple constraints by > the same name) that you can't reasonably extricate yourself from. Well, it seems if we want to continue to allow the same constraint name to be used by different tables in the same schema, we have to print the tablename in the error message. Would someone actually be looking for a standards-compliant error string? We have already extended the standard --- either we revert that, or we have to go the entire way and print the table name. -- 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 wrote: > Kevin Brown wrote: > > The two approaches aren't necessarily mutually exclusive (though SQL99 > > compliance on constraint names would obviously make it unnecessary to > > specify a tablename along with a constraint name), so I see little > > problem here. But the current arrangement is obviously untenable, > > because it allows you to create a situation (multiple constraints by > > the same name) that you can't reasonably extricate yourself from. > > Well, it seems if we want to continue to allow the same constraint name > to be used by different tables in the same schema, we have to print the > tablename in the error message. Would someone actually be looking for a > standards-compliant error string? We have already extended the standard > --- either we revert that, or we have to go the entire way and print the > table name. If PG were configurable in terms of how it manages constraint names, then it would depend on how the DBA had the database configured. With it configured to disallow name collisions, it would obviously be unnecessary to report the table name, though I still think it would be useful (if only because it gives a little extra context to work with). But if it's configured to allow name collisions, then it doesn't make sense not to print the table name in an error message, because that's the only way to guarantee that the DBA can identify which constraint is being referred to. The problem as things stand now is that even if we printed the table name involved, the DBA is placed in a difficult position if the constraint in question isn't uniquely named -- which is the only case where printing the table name would really matter. That's because he can't actually refer to the constraint in any unique way short of playing with the system tables; he'd have to rename the constraint first before being able to really do something with it (is this even possible for him to do without manipulating system tables? Is there an ALTER CONSTRAINT?). -- Kevin Brown kevin@sysexperts.com
Kevin Brown wrote: > Bruce Momjian wrote: > > Kevin Brown wrote: > > > The two approaches aren't necessarily mutually exclusive (though SQL99 > > > compliance on constraint names would obviously make it unnecessary to > > > specify a tablename along with a constraint name), so I see little > > > problem here. But the current arrangement is obviously untenable, > > > because it allows you to create a situation (multiple constraints by > > > the same name) that you can't reasonably extricate yourself from. > > > > Well, it seems if we want to continue to allow the same constraint name > > to be used by different tables in the same schema, we have to print the > > tablename in the error message. Would someone actually be looking for a > > standards-compliant error string? We have already extended the standard > > --- either we revert that, or we have to go the entire way and print the > > table name. > > If PG were configurable in terms of how it manages constraint names, > then it would depend on how the DBA had the database configured. With it > configured to disallow name collisions, it would obviously be unnecessary > to report the table name, though I still think it would be useful (if > only because it gives a little extra context to work with). But if it's > configured to allow name collisions, then it doesn't make sense not to > print the table name in an error message, because that's the only way to > guarantee that the DBA can identify which constraint is being referred to. > > > The problem as things stand now is that even if we printed the table name > involved, the DBA is placed in a difficult position if the constraint in > question isn't uniquely named -- which is the only case where printing > the table name would really matter. That's because he can't actually > refer to the constraint in any unique way short of playing with the > system tables; he'd have to rename the constraint first before being > able to really do something with it (is this even possible for him to > do without manipulating system tables? Is there an ALTER CONSTRAINT?). Added to TODO: * Print table names with constraint names in error messages, or make constraint names unique within a schema -- 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 wrote: > Added to TODO: > > * Print table names with constraint names in error messages, or make > constraint names unique within a schema Should the TODO also include adding "ALTER TABLE x ALTER CONSTRAINT y RENAME TO z" functionality if we don't make constraint names unique within a schema? -- Kevin Brown kevin@sysexperts.com
Kevin Brown wrote: > Bruce Momjian wrote: > > > Added to TODO: > > > > * Print table names with constraint names in error messages, or make > > constraint names unique within a schema > > > Should the TODO also include adding "ALTER TABLE x ALTER CONSTRAINT > y RENAME TO z" functionality if we don't make constraint names unique > within a schema? Added to TODO: o Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME -- 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