Re: Transactional-DDL DROP/CREATE TABLE

Geoff:

On Thu, Oct 6, 2016 at 5:43 PM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:
> Nope. Serializable ignores the DROP, and then freezes on CREATE (and
> then fails when the first transaction COMMITs).

Yep, I tested it too.

> Which is also broken,
> because the transaction should fail if (at COMMIT time) the table
> cannot be CREATEd, but that's no reason to not create a table within a
> temporary namespace and perform actions against that table until the
> COMMIT, at which point the table can either be validated systemwide or
> the transaction rolled back.

Well, that maybe a nice new addition to the standard, but I doubt it
would fly. If you want that kind of behaviour you should implement
them app-side, they are not that difficult.

>
>> 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 ).
> Then that effectively makes the IF EXISTS useless, because it might in
> fact exist by the time the transaction is committed.

Lots of people find it useful as it is. Is just that normally people
do not try to interleave conditional drop plus create on interleaved
transactions without an upper level retrying loop and expect it to
magically work as they think it should.

And, following that train of thought unconditional drop is useless,
because by commit time table may not exists, and select is useless,
because at commit time rows may not exist or have other values.

> The point of a DROP ... IF EXISTS should surely be that after the
> command, that table should no longer exist, either because it didn't
> exist or because it has been dropped (indeed, the result of "DROP...IF
> EXISTS" is "DROP TABLE").

That exactly what is does ( unless your transaction aborts in the command ).

> The idea that this can't be done at
> commit-time because people might use the NOTICE response as some sort
> of branch is (IMO) logically bankrupt: you can quite happily test for
> existence without requiring any sort of atomic DROP, if that's your
> intention.

You are contradicting yourself. First you say after the command it
must not exist. Then you say to do it at commit time. If it is done at
commit time you cannot guarantee it does not exist after the command.
And using the branch for testing is not logically bankrupt, atomic
operations with responses are there for a second, this is why
processors have 'test and set' and 'compare-exchange' and similar.
This one is similar to a test and set, you set existence to false and
test whether it existed before. I can easily test and then set, but is
not the same as TAS. And the notice is not the reason it is not done
at commit time, the reason is the one you said, action must be taken
when you issue the command, not a magic convenient time in the future.

Francisco Olarte.