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

Errors on CREATE TABLE IF NOT EXISTS

From: Matteo Beccati <php(at)beccati(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Errors on CREATE TABLE IF NOT EXISTS
Date: 2012-04-23 11:49:39
Message-ID: 4F9541D3.1000202@beccati.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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(at)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(at)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/

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2012-04-23 13:22:42
Subject: Re: BUG #6605: wrong type cast from timestamp to timestamptz
Previous:From: duncan.burkeDate: 2012-04-23 05:32:43
Subject: BUG #6608: SELECT FOR UPDATE not obtaining row exclusive locks in CTEs

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