Re: TRUNCATE SERIALIZABLE and frozen COPY

On 9 November 2012 10:28, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> On Wed, Nov 7, 2012 at 5:34 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> I was unhappy with changing the behaviour of TRUNCATE, and still am.
>> So the proposal here is to have a specific modifier on TRUNCATE
>> command that makes it MVCC safe by throwing a serialization error.
>> That new behaviour should be requestable by adding the SERIALIZABLE
>> keyword.
>> i.e. TRUNCATE foo SERIALIZABLE;
>> This then allows a new style of TRUNCATE, yet without modiying
>> behaviour of earlier programs (ugh!).
>
> Personally I think the behavior should be dictated by the *reader*.
> The one doing the truncation may not know about the consistency
> requirements of particular readers. Especially when you do the
> truncate via pg_restore or some other generic tool. And indeed
> different readers may have different consistency requirements.
>
> So I'd prefer if it were a GUC variable; readers that accept relaxed
> consistency can set truncate_conflict=off

I proposed something similar earlier, but Robert said he didn't like
that. The way you've described it here makes more sense, whereas my
parameter name made it seem more arbitrary. So I think that looks like
the way to go.

I'm trying to use this as a way to optimize COPY, so we'd need to make
a Seq Scan return zero rows if the truncatexid is seen as running by
the snapshot, which is the current behaviour. That seems easy enough.

> Granted, making this part of the TRUNCATE statement does give you
> table-level granularity. But if a reader can already handle
> serialization conflicts, it doesn't really matter which table they
> came from.

> But making TRUNCATE behave in a MVCC-safe way, like proposed by Hannu,
> would be much better.

Maybe, but one of the reasons for having a separate TRUNCATE command
rather than DELETE is the immediately removal of space. Changing
TRUNCATE so it suddenly holds on to space for longer will force us to
create a new command that acts like the old TRUNCATE.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services