[BUGS] Errors on CREATE TABLE IF NOT EXISTS

Matteo Beccati Mon, 23 Apr 2012 04:50:18 -0700

Hi,

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


ERROR:  relation "orders_2012_03" already exists

CONTEXT: SQL statement "CREATE UNLOGGED TABLE IF NOT EXISTShistory.orders_2012_03(CHECK (store_t_stamp >= '2012-03-01 00:00:00' ANDstore_t_stamp < '2012-04-01 00:00:00')) INHERITS (history.orders)"

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

I've tried to come up with a self-contained test case but I haven't beenable to replicate the error above. However the following script performsa few concurrent CREATE TABLE IF NOT EXISTS statements that produce someunexpected 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