Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id CF5ED12DEAC9 for ; Mon, 23 Apr 2012 08:50:01 -0300 (ADT) Received: from spritz.beccati.com ([176.9.114.167] helo=mail.beccati.com) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SMHmI-0004xo-O0 for pgsql-bugs@postgresql.org; Mon, 23 Apr 2012 11:50:00 +0000 Received: (qmail 3772 invoked from network); 23 Apr 2012 11:49:42 -0000 Received: from home.beccati.com (HELO ?192.168.1.202?) (88.149.176.119) by mail.beccati.com with SMTP; 23 Apr 2012 11:49:42 -0000 Message-ID: <4F9541D3.1000202@beccati.com> Date: Mon, 23 Apr 2012 13:49:39 +0200 From: Matteo Beccati User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20120327 Thunderbird/11.0.1 MIME-Version: 1.0 To: pgsql-bugs@postgresql.org Subject: Errors on CREATE TABLE IF NOT EXISTS Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201204/126 X-Sequence-Number: 32877 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/