Re: [HACKERS] TRUNCATE TABLE with IDENTITY

On Fri, 2008-05-16 at 19:41 -0400, Tom Lane wrote:
> 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.

Ugh. The fact that the RESTART IDENTITY part of TRUNCATE is
non-transactional is a pretty unsightly wort. I would also quarrel with
your addition to the docs that suggests this is only an issue "in
practice" if TRUNCATE RESTART IDENTITY is used in a transaction block:
unpredictable failures (such as OOM or query cancellation) can certainly
occur in practice, and would be very disruptive (e.g. if the sequence
values are stored into a column with a UNIQUE constraint, it would break
all inserting transactions until the DBA intervenes).

I wonder if it would be possible to make the sequence operations
performed by TRUNCATE transactional: while the TRUNCATE remains
uncommitted, it should be okay to block concurrent access to the
sequence.

-Neil