Re: Transactional-DDL DROP/CREATE TABLE

On Thu, Oct 6, 2016 at 9:31 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:

> 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

I recommend using a transactional advisory lock to serialize these.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company