Re: [HACKERS] TRUNCATE TABLE with IDENTITY

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

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