From: Matteo Beccati <php@beccati.com>
To: pgsql-bugs@postgresql.org
Subject: Errors on CREATE TABLE IF NOT EXISTS
Date: Mon, 23 Apr 2012 13:49:39 +0200
Message-ID: <4F9541D3.1000202@beccati.com> (raw)
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: php@beccati.com
Subject: Re: Errors on CREATE TABLE IF NOT EXISTS
In-Reply-To: <4F9541D3.1000202@beccati.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox