Thread: 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
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
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
"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
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
"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
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 >
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).
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
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.
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 constraintnamesoverlap. What's more of a problem for me is those pesky <unnamed>constraints. -Josh Berkus
> 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
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 >