Re: Errors on CREATE TABLE IF NOT EXISTS

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