Re: RFC: Making TRUNCATE more "MVCC-safe"

On Fri, Feb 10, 2012 at 01:59:18PM -0500, Robert Haas wrote:
> On Fri, Feb 10, 2012 at 6:42 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> > I like the design you have chosen. ?It would find applications beyond
> > TRUNCATE, so your use of non-specific naming is sound. ?For example, older
> > snapshots will see an empty table "t" after "CREATE TABLE t AS SELECT 1"
> > commits; that's a comparable MVCC anomaly. ?Some of our non-MVCC-safe commands
> > should perhaps just become MVCC-safe, but there will always be use cases for
> > operations that shortcut MVCC. ?When one truly does want that, your proposal
> > for keeping behavior consistent makes plenty of sense.
>
> I guess I'm not particularly excited by the idea of trying to make
> TRUNCATE MVCC-safe. I notice that the example involves the REPEATABLE
> READ isolation level, which is already known to be busted in a variety
> of ways; that's why we now have SERIALIZABLE, and why most people use
> READ COMMITTED. Are there examples of this behavior at other
> isolation levels?

I've yet to see an MVCC anomaly that one can reproduce at REPEATABLE READ and
not at READ COMMITTED. They tend to be narrow race conditions at READ
COMMITTED, yet easy to demonstrate at REPEATABLE READ. Related:
http://archives.postgresql.org/pgsql-performance/2011-02/msg00451.php

Incidentally, people use READ COMMITTED because they don't question the
default, not because they know hazards of REPEATABLE READ. I don't know the
bustedness you speak of; could we improve the documentation to inform folks?

> But I have to admit I'm intrigued by the idea of extending this to
> other cases, if there's a reasonable way to do that. For example, if
> we could fix things up so that we don't see a table at all if it was
> created after we took our snapshot, that would remove one of the
> obstacles to marking pages bulk-loaded into that table with FrozenXID
> and PD_ALL_VISIBLE from the get-go. I think a lot of people would be
> mighty happy about that.

Exactly.

> But the necessary semantics seem somewhat different. For TRUNCATE,
> you want to throw a serialization error; but is that also what you
> want for CREATE TABLE? Or do you just want it to appear empty?

I think an error helps just as much there. If you create a table and populate
it with data in the same transaction, letting some snapshot see an empty table
is an atomicity failure.

Your comment illustrates a helpful point: this is just another kind of
ordinary serialization failure, one that can happen at any isolation level.
No serial transaction sequence can yield one of these errors.

Thanks,
nm