On 6 October 2016 at 15:04, Francisco Olarte <folarte@peoplecall.com> wrote:
> 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 ).
Nope. Serializable ignores the DROP, and then freezes on CREATE (and
then fails when the first transaction COMMITs). 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.
> 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.
> 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.
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"). 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.
Geoff