Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] TRUNCATE TABLE with IDENTITY

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Neil Conway <neilc(at)samurai(dot)com>, Zoltan Boszormenyi <zb(at)cybertec(dot)at>, List pgsql-patches <pgsql-patches(at)postgresql(dot)org>, Decibel! <decibel(at)decibel(dot)org>, Steve Crawford <scrawford(at)pinpointresearch(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Date: 2008-05-17 16:04:55
Message-ID: 26734.1211040295@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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

In response to

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2008-05-17 16:23:58
Subject: Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Previous:From: Euler Taveira de OliveiraDate: 2008-05-17 15:14:58
Subject: Re: Adding variables for segment_size, wal_segment_size and block sizes

pgsql-patches by date

Next:From: Simon RiggsDate: 2008-05-17 16:23:58
Subject: Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Previous:From: Andrew ChernowDate: 2008-05-17 12:28:18
Subject: Re: libpq object hooks (libpq events)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group