Re: Transactional-DDL DROP/CREATE TABLE

Adrian:

On Thu, Oct 6, 2016 at 4:31 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> This is how I can trigger the ERROR:

This is how you can trigger the ISSUE, IMO it is a correct behaviour.

Anyway, your example lacks some important details:
1.- SHOW your isolation level.
2.- SHOW your command interleaving.

Here is an example session where IT WORKS like you pretend, and the
table exists before starting showing those details:

\set PROMPT1 'session1-%`date +%H:%M:%S` [%x]'
session1-17:27:26 []start transaction isolation level serializable;
START TRANSACTION
session1-17:27:35 [*]drop table if exists ddl_test;
DROP TABLE
session1-17:27:44 [*]create table ddl_test(id int);
CREATE TABLE
session1-17:28:03 [*]commit;
COMMIT
session1-17:28:10
-----
\set PROMPT1 'session2-%`date +%H:%M:%S` [%x]'
session2-17:27:29 []start transaction isolation level serializable;
START TRANSACTION
session2-17:27:39 [*]drop table if exists ddl_test;
****GAP****
DROP TABLE
session2-17:28:10 [*]create table ddl_test(id int);
CREATE TABLE
session2-17:28:23 [*]commit;
COMMIT
session2-17:28:28

in the ***GAP*** mark session 2 was blocked, and it unblocked when I
issued commit in session 1. ( note the timestamps of command end are
the ones starting the next line, and except the one I marked they were
nearly instant )

Note how you can follow the command interleaving and the isolation level.

OTOH, as you point, not having the table shows the issue again:

session1-17:33:56 []start transaction isolation level serializable;
START TRANSACTION
session1-17:33:59 [*]drop table if exists ddl_test;
NOTICE: table "ddl_test" does not exist, skipping
DROP TABLE
session1-17:34:08 [*]create table ddl_test(id int);
CREATE TABLE
session1-17:34:19 [*]commit;
COMMIT

session2-17:28:28 []start transaction isolation level serializable;
START TRANSACTION
session2-17:34:04 [*]drop table if exists ddl_test;
NOTICE: table "ddl_test" does not exist, skipping
DROP TABLE
session2-17:34:13 [*]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.
session2-17:34:30 [!]

This time session 2 stopped at the create table and direcly aborted
when session1 commited. Correct, IMO, although antiestetic behaviour.
I think it is due to drop being a no-op if table did not exist, as
commands are not postponed ( it must show you the notice or not before
completing ), so you are just issuing to create commands for the same
table.

Your serial postponed execution is a nice desire, but I doubt it is necessary .

Francisco Olarte.