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: Zoltan Boszormenyi <zb(at)cybertec(dot)at>
Cc: List pgsql-patches <pgsql-patches(at)postgresql(dot)org>, "Decibel!" <decibel(at)decibel(dot)org>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Steve Crawford <scrawford(at)pinpointresearch(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Date: 2008-05-16 23:41:15
Message-ID: 3919.1210981275@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Zoltan Boszormenyi <zb(at)cybertec(dot)at> writes:
>> Attached patch implements the extension found in the current SQL200n draft,
>> implementing stored start value and supporting ALTER SEQUENCE seq RESTART;

> Updated patch implements TRUNCATE ... RESTART IDENTITY
> which restarts all owned sequences for the truncated table(s).

Applied with corrections.  Most notably, since ALTER SEQUENCE RESTART
is nontransactional like most other ALTER SEQUENCE operations, I
rearranged things to try to ensure that foreseeable failures like
deadlock and lack of permissions would be detected before TRUNCATE
starts to issue any RESTART commands.

One interesting point here is that the patch as submitted allowed
ALTER SEQUENCE MINVALUE/MAXVALUE to be used to set a sequence range
that the original START value was outside of.  This would result in
a failure at ALTER SEQUENCE RESTART.  Since, as stated above, we
really don't want that happening during TRUNCATE, I adjusted the
patch to make such an ALTER SEQUENCE fail.  This is at least potentially
an incompatible change: command sequences that used to be legal could
now fail.  I doubt it's very likely to bite anyone in practice, though.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Andrew ChernowDate: 2008-05-17 00:00:07
Subject: Re: libpq object hooks
Previous:From: Andrew DunstanDate: 2008-05-16 22:22:42
Subject: Re: missing $PostgreSQL:$

pgsql-patches by date

Next:From: Andrew ChernowDate: 2008-05-17 00:00:07
Subject: Re: libpq object hooks
Previous:From: Merlin MoncureDate: 2008-05-16 21:38:02
Subject: Re: libpq object hooks

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