Simon Riggs <[EMAIL PROTECTED]> 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 -- Sent via pgsql-patches mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches