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

On Mon, Feb 13, 2012 at 09:29:56AM -0500, Robert Haas wrote:
> On Fri, Feb 10, 2012 at 11:46 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> > 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
>
> Yeah. Well, that's actually an interesting example, because it
> illustrates how general this problem is. We could potentially get
> ourselves into a situation where just about every system catalog table
> needs an xmin field to store the point at which the object came into
> existence - or for that matter, was updated.

I can see this strategy applying to many relation-pertinent system catalogs.
Do you foresee applications to non-relation catalogs?

In any event, I think a pg_class.relvalidxmin is the right starting point.
One might imagine a family of relvalidxmin, convalidxmin, indcheckxmin
(already exists), inhvalidxmin, and attvalidxmin. relvalidxmin is like the
AccessExclusiveLock of that family; it necessarily blocks everything that
might impugn the others. The value in extending this to more catalogs is the
ability to narrow the impact of failing the check. A failed indcheckxmin
comparison merely excludes plans involving the index. A failed inhvalidxmin
check might just skip recursion to the table in question. Those are further
refinements, much like using weaker heavyweight lock types.

> But it's not quite the
> same as the xmin of the row itself, because some updates might be
> judged not to matter. There could also be intermediate cases where
> updates are invalidating for some purposes but not others. I think
> we'd better get our hands around more of the problem space before we
> start trying to engineer solutions.

I'm not seeing that problem. Any operation that would update some xmin
horizon should set it to the greater of its current value and the value the
operation needs for its own correctness. If you have something in mind that
needs more, could you elaborate?

> > 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?
>
> The example that I remember was related to SELECT FOR UPDATE/SELECT
> FOR SHARE. The idea of those statements is that you want to prevent
> the row from being updated or deleted until some other concurrent
> action is complete; for example, in the case of a foreign key, we'd
> like to prevent the referenced row from being deleted or updated in
> the relevant columns until the inserting transaction is committed.
> But it doesn't work, because when the updating or deleting process
> gets done with the lock wait, they are still using the same snapshot
> as before, and merrily do exactly the the thing that the lock-wait was
> supposed to prevent. If an actual UPDATE is used, it's safe (I
> think): anyone who was going to UPDATE or DELETE the row will fail
> with some kind of serialization error. But a SELECT FOR UPDATE that
> commits is treated more like an UPDATE that rolls back: it's as if the
> lock never existed. Someone (Florian?) proposed a patch to change
> this, but it seemed problematic for reasons I no longer exactly
> remember.

Thanks. I vaguely remember that thread.

nm