Currently we have a rather confusing mismash of behaviors for the names
of rules, constraints, and triggers.  I'd like to unify the rules
so that these objects all have the same naming behavior; and the only
behavior that makes sense to me now is that of triggers.

The current behavior is:

1. Rules are required to have a name that is unique within the current
database.  The rule can be named without reference to the table it is
on.  Dropping a rule is done with "DROP RULE name".

2. Constraints are not required to have any unique name at all.
Dropping constraints is done with "ALTER TABLE tablename DROP CONSTRAINT
constraintname", which will drop all constraints on that table that
match the given name.

3. Triggers are required to have names that are unique among the
triggers on a given table.  Dropping a trigger is done with "DROP
TRIGGER name ON table".

The SQL spec is not a great deal of help on this, since it doesn't
have rules or triggers at all.  For constraints, it requires
database-wide uniqueness of constraint names --- a rule I doubt
anyone is going to favor adopting for Postgres.

I think that all three object types should have names that are unique
among the objects associated with a particular table, but not unique
across a whole database.  So, triggers are okay already, but rules
and constraints need work.

For rules, we'd need to change the syntax of DROP RULE to be "DROP RULE
name ON table", much like DROP TRIGGER.  This seems unlikely to cause
problems for existing applications, since I doubt rule-dropping is done
much by application code.

For constraints, we'd need to change the code to be more careful to
generate unique names for unnamed constraints.  That doesn't seem
difficult, but I'm a little worried about the possibility of errors
in loading schemas from existing databases, where there might be
non-unique constraint names.  Perhaps it'd be safer to maintain the
current behavior (no uniqueness required for constraint names).

Comments?

			regards, tom lane

Mon, 04 Mar 2002 14:24:08 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Uniqueness of rule, constraint, and trigger names
On 4 Mar 2002 at 14:24, Tom Lane wrote:

> For constraints, we'd need to change the code to be more careful to
> generate unique names for unnamed constraints.  That doesn't seem
> difficult, but I'm a little worried about the possibility of errors
> in loading schemas from existing databases, where there might be
> non-unique constraint names.

Create a tool to generate unique constraint names during a dump.  Sounds 
like a pg_dump[all] switch to me.

> Perhaps it'd be safer to maintain the
> current behavior (no uniqueness required for constraint names).

I would rather see a simple and unambigous way to maintain a single 
constraint.  Perhaps a "unique/not-unique" knob is appropriate.
-- 
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples


Mon, 4 Mar 2002 15:04:45 -0500
"Dan Langille" <dan@langille.org>
Re: Uniqueness of rule, constraint, and trigger names
"Dan Langille" <dan@langille.org> writes:
> On 4 Mar 2002 at 14:24, Tom Lane wrote:
>> ... but I'm a little worried about the possibility of errors
>> in loading schemas from existing databases, where there might be
>> non-unique constraint names.

> Create a tool to generate unique constraint names during a dump.

And then all we need is a time machine, so we can make existing
instances of pg_dump contain the tool?  It's not that easy ...

I am not sure that there's really a problem here, because I don't
think duplicate constraint names will be generated during plain
CREATE operations.  However, an ALTER TABLE might leave you with
a problem.  Hard to tell if this is critical enough to worry about.

			regards, tom lane

Mon, 04 Mar 2002 15:38:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Uniqueness of rule, constraint, and trigger names 
I agree with your statement: "I think that all three object types should
have names that are unique
among the objects associated with a particular table, but not unique across
a whole database."

To address the potential problem of "loading schemas of existing databases,"
why not let the new proposed behavior be the default behavior and provide a
configuration option and/or command line option that would enable the old
behavior - at least for some period of time.

Tim

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: <pgsql-hackers@postgreSQL.org>; <pgsql-sql@postgreSQL.org>
Sent: Monday, March 04, 2002 11:24 AM
Subject: [SQL] Uniqueness of rule, constraint, and trigger names


> Currently we have a rather confusing mismash of behaviors for the names
> of rules, constraints, and triggers.  I'd like to unify the rules
> so that these objects all have the same naming behavior; and the only
> behavior that makes sense to me now is that of triggers.
>
> The current behavior is:
>
> 1. Rules are required to have a name that is unique within the current
> database.  The rule can be named without reference to the table it is
> on.  Dropping a rule is done with "DROP RULE name".
>
> 2. Constraints are not required to have any unique name at all.
> Dropping constraints is done with "ALTER TABLE tablename DROP CONSTRAINT
> constraintname", which will drop all constraints on that table that
> match the given name.
>
> 3. Triggers are required to have names that are unique among the
> triggers on a given table.  Dropping a trigger is done with "DROP
> TRIGGER name ON table".
>
> The SQL spec is not a great deal of help on this, since it doesn't
> have rules or triggers at all.  For constraints, it requires
> database-wide uniqueness of constraint names --- a rule I doubt
> anyone is going to favor adopting for Postgres.
>
> I think that all three object types should have names that are unique
> among the objects associated with a particular table, but not unique
> across a whole database.  So, triggers are okay already, but rules
> and constraints need work.
>
> For rules, we'd need to change the syntax of DROP RULE to be "DROP RULE
> name ON table", much like DROP TRIGGER.  This seems unlikely to cause
> problems for existing applications, since I doubt rule-dropping is done
> much by application code.
>
> For constraints, we'd need to change the code to be more careful to
> generate unique names for unnamed constraints.  That doesn't seem
> difficult, but I'm a little worried about the possibility of errors
> in loading schemas from existing databases, where there might be
> non-unique constraint names.  Perhaps it'd be safer to maintain the
> current behavior (no uniqueness required for constraint names).
>
> Comments?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>


Mon, 4 Mar 2002 12:41:42 -0800
"Tim Barnard" <tbarnard@povn.com>
Re: Uniqueness of rule, constraint, and trigger names
On 4 Mar 2002 at 15:38, Tom Lane wrote:

> "Dan Langille" <dan@langille.org> writes:
> > On 4 Mar 2002 at 14:24, Tom Lane wrote:
> >> ... but I'm a little worried about the possibility of errors
> >> in loading schemas from existing databases, where there might be
> >> non-unique constraint names.
> 
> > Create a tool to generate unique constraint names during a dump.
> 
> And then all we need is a time machine, so we can make existing
> instances of pg_dump contain the tool?  It's not that easy ...

*sigh*

You don't have to modify all previous versions of pg_dump.  They use this 
tool if they want to upgrade to this version.  There is no perfect 
solution.  Even a script to check for uniqueness might help.  I'm just 
trying to help the best way I can; by providing suggestions.  As 
requested.

> I am not sure that there's really a problem here, because I don't
> think duplicate constraint names will be generated during plain
> CREATE operations.  However, an ALTER TABLE might leave you with
> a problem.  Hard to tell if this is critical enough to worry about.

It would be easily detected at load time.  Then you direct the user to the 
tool mentioned above.
-- 
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples


Mon, 4 Mar 2002 15:43:38 -0500
"Dan Langille" <dan@langille.org>
Re: Uniqueness of rule, constraint, and trigger names 
"Dan Langille" <dan@langille.org> writes:
> It would be easily detected at load time.  Then you direct the user to the 
> tool mentioned above.

But if the user has already dumped and flushed his old installation,
he's still in need of a time machine.

Hmm, maybe what we need is a tool that can be applied during load.
Essentially, alter incoming constraint names as needed to make them
unique.  We could have this enabled by a SET switch...

			regards, tom lane

Mon, 04 Mar 2002 15:47:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Uniqueness of rule, constraint, and trigger names 
On 4 Mar 2002 at 15:47, Tom Lane wrote:

> "Dan Langille" <dan@langille.org> writes:
> > It would be easily detected at load time.  Then you direct the user to
> > the tool mentioned above.
> 
> But if the user has already dumped and flushed his old installation,
> he's still in need of a time machine.

Agreed.  I had that same problem with upgrading to 7.2.  My solution was 
to install 7.1.3 on another box, load, massage, dump.  Next time, I'll 
keep the old version around a bit longer.

> Hmm, maybe what we need is a tool that can be applied during load.
> Essentially, alter incoming constraint names as needed to make them
> unique.  We could have this enabled by a SET switch...

I think that is an eloquent solution.
-- 
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples


Mon, 4 Mar 2002 15:50:39 -0500
"Dan Langille" <dan@langille.org>
Re: Uniqueness of rule, constraint, and trigger names 
If pgupgrade can be fixed to work, perhaps it could set off warnings
on items that need to be corrected in a 'schema upgradability test'
which will ensure that the user can upgrade it properly -- it
shouldn't upgrade if it can't guarentee an upgrade will succeed.

This should include full schema test (that whole bad schema entry
stuff that pg_dump is supposed to work around) too.

Something I'm thinking about digging into.

--
Rod Taylor

This message represents the official view of the voices in my head

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: <dan@langille.org>
Cc: <pgsql-hackers@postgreSQL.org>; <pgsql-sql@postgreSQL.org>
Sent: Monday, March 04, 2002 3:38 PM
Subject: Re: [HACKERS] [SQL] Uniqueness of rule, constraint, and
trigger names


> "Dan Langille" <dan@langille.org> writes:
> > On 4 Mar 2002 at 14:24, Tom Lane wrote:
> >> ... but I'm a little worried about the possibility of errors
> >> in loading schemas from existing databases, where there might be
> >> non-unique constraint names.
>
> > Create a tool to generate unique constraint names during a dump.
>
> And then all we need is a time machine, so we can make existing
> instances of pg_dump contain the tool?  It's not that easy ...
>
> I am not sure that there's really a problem here, because I don't
> think duplicate constraint names will be generated during plain
> CREATE operations.  However, an ALTER TABLE might leave you with
> a problem.  Hard to tell if this is critical enough to worry about.
>
> regards, tom lane
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


Mon, 4 Mar 2002 16:40:16 -0500
"Rod Taylor" <rbt@zort.ca>
Re: [HACKERS] Uniqueness of rule, constraint, and trigger names 

On Mon, 4 Mar 2002, Tom Lane wrote:

> For constraints, we'd need to change the code to be more careful to
> generate unique names for unnamed constraints.  That doesn't seem

Another question would be what to do with inherited constraints that
conflict in multiple inheritance cases.  It'd probably be safe to rename
those on the child table to be unique, but then drop constraint may
become more involved (and the error messages no longer use the name
given by the user for either constraint).



Mon, 4 Mar 2002 14:13:18 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Uniqueness of rule, constraint, and trigger names
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Mon, 4 Mar 2002, Tom Lane wrote:
>> For constraints, we'd need to change the code to be more careful to
>> generate unique names for unnamed constraints.  That doesn't seem

> Another question would be what to do with inherited constraints that
> conflict in multiple inheritance cases.  It'd probably be safe to rename
> those on the child table to be unique,

I'd just raise an error, I think, unless perhaps the constraints are
identical (for some definition of identical).  We don't allow
conflicting column definitions to be inherited, so why constraints?

			regards, tom lane

Mon, 04 Mar 2002 17:18:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Uniqueness of rule, constraint, and trigger names 
On Mon, 4 Mar 2002, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > On Mon, 4 Mar 2002, Tom Lane wrote:
> >> For constraints, we'd need to change the code to be more careful to
> >> generate unique names for unnamed constraints.  That doesn't seem
>
> > Another question would be what to do with inherited constraints that
> > conflict in multiple inheritance cases.  It'd probably be safe to rename
> > those on the child table to be unique,
>
> I'd just raise an error, I think, unless perhaps the constraints are
> identical (for some definition of identical).  We don't allow
> conflicting column definitions to be inherited, so why constraints?

Good point.  That's probably better than autorenaming them.



Mon, 4 Mar 2002 14:51:16 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Uniqueness of rule, constraint, and trigger names 
Tom,

> Currently we have a rather confusing mismash of behaviors for the
>  names
> of rules, constraints, and triggers.  I'd like to unify the rules
> so that these objects all have the same naming behavior; and the only
> behavior that makes sense to me now is that of triggers.

I agree.  

Regarding prioritization:  As a heavy user of constraints and triggers,
 on two commercial projects, I have yet to have constraint names
 overlap.  What's more of a problem for me is those pesky <unnamed>
 constraints.


-Josh Berkus

Mon, 04 Mar 2002 15:05:07 -0800
"Josh Berkus" <josh@agliodbs.com>
Re: Uniqueness of rule, constraint, and trigger names
> 2. Constraints are not required to have any unique name at all.
> Dropping constraints is done with "ALTER TABLE tablename DROP CONSTRAINT
> constraintname", which will drop all constraints on that table that
> match the given name.

Personally, I'd like to see CHECK contraints given decent names (eg.
mytable_fullname_chk) instead of '$1', '$2', etc.  This makes it much easier
to use DROP CONSTRAINT...

Also, when it comes time to let people drop FOREIGN KEY constraints, it
might be a problem that they're all generated as '<unnamed>' at the
moment...

Chris


Tue, 5 Mar 2002 10:36:10 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Uniqueness of rule, constraint, and trigger names
Tom Lane writes:

> The SQL spec is not a great deal of help on this, since it doesn't
> have rules or triggers at all.

The SQL spec has triggers, and their names are supposed to be
globally unique.

> For constraints, it requires database-wide uniqueness of constraint
> names --- a rule I doubt anyone is going to favor adopting for
> Postgres.

This should probably be schema-wide, which poses much less of a problem.

-- 
Peter Eisentraut   peter_e@gmx.net


Sun, 10 Mar 2002 20:33:43 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: Uniqueness of rule, constraint, and trigger names
> > The SQL spec is not a great deal of help on this, since it doesn't
> > have rules or triggers at all.
> The SQL spec has triggers, and their names are supposed to be
> globally unique.

Speaking of which, I was looking at

  CREATE ASSERTION/DROP ASSERTION

in (what I think is) SQL99. It has enough restrictions on use that I'm
not yet sure what exactly it is supposed to do, and am not sure if it is
close to equivalent to something we already have. Anyone know?

                   - Thomas

Sun, 10 Mar 2002 17:37:43 -0800
Thomas Lockhart <thomas@fourpalms.org>
Re: Uniqueness of rule, constraint, and trigger names