Skip site navigation (1) Skip section navigation (2)

Re: Reducing some DDL Locks to ShareLock

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Reducing some DDL Locks to ShareLock
Date: 2008-10-07 18:38:42
Message-ID: 1223404722.4747.248.camel@ebony.2ndQuadrant (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On Tue, 2008-10-07 at 11:46 -0400, Tom Lane wrote:
> 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.

How about we put a partial unique index on instead?

Dunno if its possible, but the above begins to sound too much froth for
such a small error.

 Simon Riggs 
 PostgreSQL Training, Services and Support

In response to


pgsql-hackers by date

Next:From: Simon RiggsDate: 2008-10-07 18:38:46
Subject: Re: Reducing some DDL Locks to ShareLock
Previous:From: Emmanuel CecchetDate: 2008-10-07 17:00:39
Subject: Re: Transactions and temp tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group