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