Re: Errors on CREATE TABLE IF NOT EXISTS - Mailing list pgsql-bugs

From Andrey Lepikhov
Subject Re: Errors on CREATE TABLE IF NOT EXISTS
Date
Msg-id 0b3bf24c-cc44-b25e-244e-cd740a8f8a67@postgrespro.ru
Whole thread Raw
In response to Errors on CREATE TABLE IF NOT EXISTS  (Matteo Beccati <php@beccati.com>)
List pgsql-bugs
On 4/23/12 17:49, Matteo Beccati wrote:
> On Mon, Apr 23, 2012 at 7:49 AM, Matteo Beccati <php@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: =A0duplicate 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

Working on a problem with conflicts in type names [1] I played with the 
script (6727v.sql) and pgbench with many clients/threads.
Using "IF NOT EXISTS" we can get many different ERROR messages because 
of races in table creation, type creation, index insertion and so on. It 
may be not a critical problem, but may be it can be solved by some 
simplistic way? See poc.diff as a demo of a solution.

[1] 
https://www.postgresql.org/message-id/b84cd82c-cc67-198a-8b1c-60f44e1259ad@postgrespro.ru

-- 
Regards
Andrey Lepikhov
Postgres Professional
Attachment

pgsql-bugs by date:

Previous
From: Jeff Janes
Date:
Subject: Re: BUG #17529: SQL Error [57P01]: FATAL: terminating connection due to administrator command
Next
From: Julien Rouhaud
Date:
Subject: Re: dropdb utility command prompts for password despite valid .pgpass file in home directory