Re: [PATCHES] TRUNCATE TABLE with IDENTITY - Mailing list pgsql-hackers

From Tom Lane
Subject Re: [PATCHES] TRUNCATE TABLE with IDENTITY
Date
Msg-id 26734.1211040295@sss.pgh.pa.us
Whole thread Raw
In response to Re: [PATCHES] TRUNCATE TABLE with IDENTITY  (Simon Riggs <simon@2ndquadrant.com>)
Responses Re: [PATCHES] TRUNCATE TABLE with IDENTITY  (Simon Riggs <simon@2ndquadrant.com>)
Re: [PATCHES] TRUNCATE TABLE with IDENTITY  (Alvaro Herrera <alvherre@commandprompt.com>)
List pgsql-hackers
Simon Riggs <simon@2ndquadrant.com> writes:
> On Fri, 2008-05-16 at 21:50 -0400, Tom Lane wrote:
>> Actually, I agree.  Shall we just revert that feature?

> Perhaps, but we should also take into account that TRUNCATE is not and
> never will be MVCC compliant, so its not something you'd expect to run
> except as a maintenance action.

Good point.  I had a couple of further thoughts this morning:

1. The case Neil is worried about is something like

    BEGIN;
    TRUNCATE TABLE foo RESTART IDENTITY;
    COPY foo FROM ...;
    COMMIT;

If the COPY fails partway through, the old table contents are restored,
but the sequences are not.  However removing RESTART IDENTITY will not
remove the hazard, because there is no difference between this and

    BEGIN;
    TRUNCATE TABLE foo;
    SELECT setval('foo_id', 1);
    COPY foo FROM ...;
    COMMIT;

other than the latter adding a little extra chance for pilot error in
resetting the wrong sequence.  So if we revert the patch we haven't
accomplished much except to take away an opportunity to point out the
risk.  I vote for leaving the patch in and rewriting the <warning>
to point out this risk.

2. I had first dismissed Neil's idea of transactional sequence updates
as impossible, but on second look it could be done.  Suppose RESTART
IDENTITY does this for each sequence;

    * obtain AccessExclusiveLock;
    * assign a new relfilenode;
    * insert a sequence row with all parameters copied except
      last_value copies start_value;
    * hold AccessExclusiveLock till commit.

IOW just like truncate-and-reload, but for a sequence.  Within the
current backend, subsequent operations see the new sequence values.
If the transaction rolls back, the old sequence relfilenode is still
there and untouched.

It's slightly annoying to need to lock out other backends' nextval
operations, but for the use-case of TRUNCATE this doesn't seem
like it's really much of a problem.

I'm not sure if it'd be worth exposing this behavior as a separate
user-visible command (CREATE OR REPLACE SEQUENCE, maybe?), but it seems
worth doing to make TRUNCATE-and-reload less of a foot gun.

So what I think we should do is leave the patch there, revise the
warning per Neil's complaint, and add a TODO item to reimplement RESTART
IDENTITY transactionally.

            regards, tom lane

pgsql-hackers by date:

Previous
From: Euler Taveira de Oliveira
Date:
Subject: Re: Adding variables for segment_size, wal_segment_size and block sizes
Next
From: Simon Riggs
Date:
Subject: Re: [PATCHES] TRUNCATE TABLE with IDENTITY