Re: [HACKERS] TRUNCATE TABLE with IDENTITY

Zoltan Boszormenyi írta:
> Zoltan Boszormenyi írta:
>> Decibel! írta:
>>> On Apr 3, 2008, at 12:52 AM, Zoltan Boszormenyi wrote:
>>>> Where is the info in the sequence to provide restarting with
>>>> the _original_ start value?
>>>
>>> There isn't any. If you want the sequence to start at some magic 
>>> value, adjust the minimum value.
>>
>> There's the START WITH option for IDENTITY columns and this below
>> is paragraph 8 under General rules of 14.10 <truncate table statement>
>> in 6WD2_02_Foundation_2007-12.pdf (page 902):
>>
>> 8) If RESTART IDENTITY is specified and the table descriptor of T 
>> includes a column descriptor IDCD of
>>   an identity column, then:
>>   a) Let CN be the column name included in IDCD and let SV be the 
>> start value included in IDCD.
>>   b) The following <alter table statement> is effectively executed 
>> without further Access Rule checking:
>>       ALTER TABLE TN ALTER COLUMN CN RESTART WITH SV
>>
>> This says that the original start value is used, not the minimum value.
>> IDENTITY has the same options as CREATE SEQUENCE. In fact the
>> "identity column specification" links to "11.63 <sequence generator 
>> definition>"
>> when it comes to IDENTITY sequence options. And surprise, surprise,
>> "11.64 <alter sequence generator statement>" now defines
>> ALTER SEQUENCE sn RESTART [WITH newvalue]
>> where omitting the "WITH newval" part also uses the original start 
>> value.
>>
>> Best regards,
>> Zoltán Böszörményi
>
> Attached patch implements the extension found in the current SQL200n 
> draft,
> implementing stored start value and supporting ALTER SEQUENCE seq 
> RESTART;
> Some error check are also added to prohibit CREATE SEQUENCE ... 
> RESTART ...
> and ALTER SEQUENCE ... START ...
>
> Best regards,
> Zoltán Böszörményi

Updated patch implements TRUNCATE ... RESTART IDENTITY
which restarts all owned sequences for the truncated table(s).
Regression tests updated, documentation added. pg_dump was
also extended to output original[1] START value for creating SEQUENCEs.

[1] For 8.3 and below I could only guesstimate it as MINVALUE for ascending
      and MAXVALUE for descending sequences.

Best regards,
Zoltán Böszörményi

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