Re: [HACKERS] TRUNCATE TABLE with IDENTITY

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