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/

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Reply via email to