Re: Transactional-DDL DROP/CREATE TABLE

Hi Geoff:

On Thu, Oct 6, 2016 at 1:58 PM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:
> But surely Transactional DDL implies that (it should appear that) nothing
> happens until transaction-commit. That means "drop table if exists" should
> drop the table if it exists at commit time, not drop the table if it didn't
> exist when the code was first run.

I'm not sure even transactional DML works that way. Bear in mind you
are supposed to be informed of the result of your commands. I mean,
you issue drop if exists and then you are allowed to issue a different
command depending on the result of the drop, i.e., you may be willing
to recreate the table if it existed and not create it if not, so the
drop must record your intentions and lock the table definition, like a
delete does with data rows.

> If the other transaction hasn't committed, then it should either fail with
> rollback when committed (because it tried to create a table that exists at
> commit time) or drop the new table (because it also has a drop clause).

It depends on the implementation. IIRC with serializable isolation
level you are guaranteeed a final result coherent with some serial
order of execution of all the completed transactions, but even there
you are not guaranteed it will find an order of execution for all of
them, some may be aborted. Trying to do what you pretend will result
in an extremely deadlock-prone system.

And anyway, what isolation level are you working on? Because it seems
you are using a weaker one than serializable, as I think serializable
should give you more or less what you are expecting ( not on commit
time, but second drop could probably get stuck until first transaction
commits ).

And surely Transactional D*L does not imply what you state in all
isolation levels. And drop table if exsits means if it exists when the
server executes your command, not on the future ( the server cannot
know if it will exist then, your own transaction may recreate it or
not. Maybe you know your command sequence is not going to depend on
intermediate results, the server does not ).

Francisco Olarte.