Re: Transactional-DDL DROP/CREATE TABLE

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(dot)klaver(at)aklaver(dot)com