set constraints docs page

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

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, Pennsylvania 19073

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, Pennsylvania 19073

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, Pennsylvania 19073