Re: TRUNCATE TABLE with IDENTITY

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

Decibel! írta:
> On Mar 25, 2008, at 11:40 AM, Zoltan Boszormenyi wrote:
>> 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.
>
>
> I think you misunderstand what ALTER SEQUENCE RESTART does; it only
> changes the current value of the sequence.

I didn't misunderstood, I know that. I quoted both
because (currently) CREATE SEQUENCE ... START WITH does the same.

zozo=> create sequence seq1 start with 327;
CREATE SEQUENCE
zozo=> select * from seq1;
sequence_name | last_value | increment_by | max_value |
min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
seq1 | 327 | 1 | 9223372036854775807
| 1 | 1 | 1 | f | f
(1 row)

Note the difference between "min_value" and "last_value".
Using the standard syntax of

CREATE TABLE (
id integer IDENTITY GENERATED ALWAYS AS (START WITH 327),
...
);

and assuming you use the existing sequence infrastructure
there's a problem with TRUNCATE ... RESTART IDENTITY;
Where is the info in the sequence to provide restarting with
the _original_ start value?

--
----------------------------------
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 Magnus Hagander 2008-04-03 07:12:58 Re: modules
Previous Message Bruce Momjian 2008-04-03 01:29:55 Re: Patch for pg_dump (function dumps)

Browse pgsql-patches by date

  From Date Subject
Next Message Pavel Stehule 2008-04-03 06:09:31 Re: actualized SQL/PSM patch
Previous Message Tom Lane 2008-04-03 05:04:15 Re: actualized SQL/PSM patch