Re: Transactional-DDL DROP/CREATE TABLE

On 10 October 2016 at 14:49, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> MVCC rules (which DDL generally fall under) try to interleave work as
> much as possible which is the problem you're facing.

Mmff. Yes, that exposes a fundamental misunderstanding on my part: I
had thought that under MVCC things were done independently and
resolved at COMMIT time, as opposed to potentially-conflicting
transactions resulting in one transaction blocking _mid-transaction_
until the other resolves, as it does.

So I suppose the logic goes, it's not clear that the DROP / CREATE
results in a potential conflict until the table is created, so since
both transactions drop a non-existent table, they both then try to
create the table, and I get my error. I had thought that the DDL would
simply have its own view on the data and therefore be able to do all
of its work up to COMMIT, but on reconsidering I can see that the
amount of overhead involved in the COMMIT would be phenomenal.

Having said all of that, I'm confused as to why CREATE TABLE in tr1
doesn't block a subsequent DROP TABLE IF EXISTS in tr2.

So if, in two psql sessions you run (shown in order of execution):

tr1:
BEGIN;
DROP TABLE IF EXISTS mytable;
CREATE TABLE mytable (test int);

tr2:
BEGIN;
DROP TABLE IF EXISTS mytable; -- could block here, no?
CREATE TABLE mytable (test int); -- actually blocks here

> Also, this is not a good pattern. You ought to be using temp tables
> or other mechanics to store transaction local data.

The data isn't transaction-local. Having said that, the _actual_
pattern is much worse than that, but it's not my design, I just have
to work with it.

Geoff