On 10/06/2016 02:21 AM, Geoff Winkless wrote:
Hi
I have code that does (inside a single transaction)
DROP TABLE IF EXISTS mytable; CREATE TABLE mytable ....
Occasionally this produces
ERROR: duplicate key value violates unique constraint
"pg_type_typname_nsp_index" DETAIL: Key (typname,
typnamespace)=(mytable, 2200) already exists.
I can get away from this by using CREATE TABLE IF NOT EXISTS in the
same code, but there's the potential that the wrong data will end up
in the table if that happens, and it also seems a little.... odd.
Would you not expect this transaction to be atomic? ie at commit time,
the transaction should drop any table with the same name that has been
created by another transaction.
This is how I can trigger the ERROR:
Session 1:
test=# begin ;
BEGIN
test=# drop table if exists ddl_test;
NOTICE: table "ddl_test" does not exist, skipping
DROP TABLE
test=# create table ddl_test(id int);
CREATE TABLE
test=# commit ;
COMMIT
Session 2 (concurrent to session1):
test=# begin ;
BEGIN
test=# drop table if exists ddl_test;
NOTICE: table "ddl_test" does not exist, skipping
DROP TABLE
test=# create table ddl_test(id int);
ERROR: duplicate key value violates unique constraint
"pg_type_typname_nsp_index"
DETAIL: Key (typname, typnamespace)=(ddl_test, 2200) already exists.
test=# commit ;
ROLLBACK
So not having the table when you start both sessions seems to be the issue.
Geoff
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
