On Mon, Apr 23, 2012 at 7:49 AM, Matteo Beccati <p...@beccati.com> wrote: > I've tried to come up with a self-contained test case but I haven't been > able to replicate the error above. However the following script performs a > few concurrent CREATE TABLE IF NOT EXISTS statements that produce some > unexpected errors (using 9.1.2). > ERROR: duplicate key value violates unique constraint > "pg_type_typname_nsp_index"
This is normal behavior for CREATE TABLE either with or without IF NOT EXISTS. CREATE TABLE does a preliminary check to see whether a name conflict exists. If so, it either errors out (normally) or exits with a notice (in the IF NOT EXISTS case). But there's a race condition: a conflicting transaction can create the table after we make that check and before we create it ourselves. If this happens, then you get the failure you're seeing, because the btree index machinery catches the problem when we do the actual system catalog inserts. Now, this is not very user-friendly, but we have no API to allow inserting into a table with a "soft" error if uniqueness would be violated. Had we such an API we could handle a number of situations more gracefully, including this one. Since we don't, the only option is to let the btree machinery error out if it must. The bottom line is that CREATE TABLE IF NOT EXISTS doesn't pretend to handle concurrency issues any better than regular old CREATE TABLE, which is to say not very well. You should use some other system to coordinate near-simultaneous creation of tables, such as perhaps doing pg_advisory_lock/CINE/pg_advisory_unlock. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
