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

"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

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

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)---------------------------

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 constraint names
overlap. 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

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

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