Errors on CREATE TABLE IF NOT EXISTS

Hi,

I've recently seen a few errors on our continuous integration system
during a test using a badly written partitioning trigger. The function
was basically checking for the existence of the partition table at every
insert and was running a CREATE TABLE IF NOT EXISTS statement in case it
was needed. What baffled me was that the function was exiting with an
ERROR, rather than succeeding with a NOTICE, e.g.:

ERROR:  relation "orders_2012_03" already exists
CONTEXT:  SQL statement "CREATE UNLOGGED TABLE IF NOT EXISTS
history.orders_2012_03(CHECK (store_t_stamp >= '2012-03-01 00:00:00' AND
store_t_stamp < '2012-04-01 00:00:00')) INHERITS (history.orders)"

Since then I've made the partitioning functions a bit smarter and I'm
also catching the exception just in case.

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).

postgres@spritz:~$ cat crtest.sh
#!/bin/sh

for i in `seq 1 10`; do
   psql -c 'CREATE TABLE IF NOT EXISTS _foo (x int PRIMARY KEY)' 2>&1 &
done

sleep 2
psql -c 'DROP TABLE _foo'
postgres@spritz:~$ ./crtest.sh
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"_foo_pkey" for table "_foo"
CREATE TABLE
ERROR:  duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(_foo, 2200) already exists.
ERROR:  duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(_foo, 2200) already exists.
ERROR:  duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(_foo, 2200) already exists.
ERROR:  duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(_foo, 2200) already exists.
ERROR:  duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(_foo, 2200) already exists.
ERROR:  duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(_foo, 2200) already exists.
ERROR:  duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(_foo, 2200) already exists.
ERROR:  duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(_foo, 2200) already exists.
ERROR:  duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(_foo, 2200) already exists.
DROP TABLE

I'm not sure if the two failures are related in some way, but I thought
it was good to report them both anyway.


Cheers
--
Matteo Beccati

Development & Consulting - http://www.beccati.com/