Re: Reducing some DDL Locks to ShareLock

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Tue, 2008-10-07 at 10:35 -0400, Tom Lane wrote:
>> I wonder whether this could be helped if we refactored pg_constraint.

> Sounds better. Doesn't make much sense as it is now.

I looked at the code a bit, and it seems the only place where the
current design makes any sense is in ChooseConstraintName, which
explains itself thusly:

* Select a nonconflicting name for a new constraint.
*
* The objective here is to choose a name that is unique within the
* specified namespace. Postgres does not require this, but the SQL
* spec does, and some apps depend on it. Therefore we avoid choosing
* default names that so conflict.
*
* Note: it is theoretically possible to get a collision anyway, if someone
* else chooses the same name concurrently. This is fairly unlikely to be
* a problem in practice, especially if one is holding an exclusive lock on
* the relation identified by name1.

(The last bit of the comment falls flat when you consider constraints
on domains...)

Note that this policy is used for system-selected constraint names;
it's not enforced against user-selected names. We do attempt (in
ConstraintNameIsUsed) to reject duplicate user-selected constraint names
*on the same object*, but that test is not bulletproof against
concurrent additions. The refactoring I suggested would make for
bulletproof enforcement via the unique indexes.

To preserve the same behavior for system-selected constraint names with
the new design, we'd still need to store namespace OIDs in the two new
tables (I had been thinking those columns would go away), and still have
nonunique indexes on (conname, connamespace), and probe both of the new
catalogs via these indexes to look for a match to a proposed constraint
name. So that's a bit of a PITA but certainly doable. Again, it's not
bulletproof against concurrent insertions, but the existing code is not
either.

regards, tom lane