On 10 October 2016 at 14:49, Merlin Moncure <mmonc...@gmail.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):

   CREATE TABLE mytable (test int);

   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.


Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:

Reply via email to