Re: TRUNCATE TABLE with IDENTITY

From: Zoltan Boszormenyi <zb(at)cybertec(dot)at>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Steve Crawford <scrawford(at)pinpointresearch(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: TRUNCATE TABLE with IDENTITY
Date: 2008-03-25 16:40:49
Message-ID: 47E92B11.4040006@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

Simon Riggs írta:
> On Tue, 2008-03-25 at 09:08 -0700, Steve Crawford wrote:
>
>> Simon Riggs wrote:
>>
>>> RESTART IDENTITY will reset the SERIAL sequences back to the original
>>> start value.
>>>
>>>
>> Assuming this feature were to be added....
>>
>> In cases where the same sequence has been used across multiple tables,
>> what will be the appropriate response when a user attempts to TRUNCATE
>> one of those tables with RESTART IDENTITY?
>>
>
> Well, I'm suggesting it as a TODO item, based on the standard. It would
> be for whoever took this up to unravel that.
>
> Since that's a weak answer, I'd say it should only reset sequences that
> have been placed there automatically through the use of SERIAL or
> BIGSERIAL datatypes.
>

All of them? PostgreSQL allow multiple SERIALs to be present,
the standard allows only one IDENTITY column in a table.
And what about this case below?

CREATE TABLE t1 (id1 serial, ...);
ALTER SEQUENCE seq_t1_id1 RESTART WITH 5432 CYCLE;

or the equivalent

CREATE SEQUENCE seq_t1_id1 START WITH 5432 CYCLE;
CREATE TABLE t1 (id1 serial, ...);
ALTER SEQUENCE seq_t1_id1 OWNED BY t1.id1;

PostgreSQL doesn't keep the START WITH information.
But it should to perform a "restart" on the sequence,
using the minval in this case wouldn't be correct.

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Zoltan Boszormenyi 2008-03-25 16:42:33 Re: TRUNCATE TABLE with IDENTITY
Previous Message Tom Lane 2008-03-25 16:34:43 Re: Text <-> C string

Browse pgsql-patches by date

  From Date Subject
Next Message Zoltan Boszormenyi 2008-03-25 16:42:33 Re: TRUNCATE TABLE with IDENTITY
Previous Message Tom Lane 2008-03-25 16:34:43 Re: Text <-> C string