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

Re: [HACKERS] TRUNCATE TABLE with IDENTITY

From: Neil Conway <neilc(at)samurai(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Zoltan Boszormenyi <zb(at)cybertec(dot)at>, 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-17 01:35:52
Message-ID: 1210988152.10381.12.camel@goldbach (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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



In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-05-17 01:50:01
Subject: Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Previous:From: Tom LaneDate: 2008-05-17 00:15:37
Subject: Re: [PATCHES] TRUNCATE TABLE with IDENTITY

pgsql-patches by date

Next:From: Tom LaneDate: 2008-05-17 01:50:01
Subject: Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Previous:From: Tom LaneDate: 2008-05-17 00:15:37
Subject: Re: [PATCHES] TRUNCATE TABLE with IDENTITY

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