Re: [HACKERS] TRUNCATE TABLE with IDENTITY

Simon Riggs <simon(at)2ndquadrant(dot)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