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

Re: [HACKERS] TRUNCATE TABLE with IDENTITY

From: Zoltan Boszormenyi <zb(at)cybertec(dot)at>
To: List pgsql-patches <pgsql-patches(at)postgresql(dot)org>
Cc: "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-04-21 20:42:30
Message-ID: 480CFC36.30407@cybertec.at (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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/


Attachment: sql2008-compliant-seq-v2.patch.gz
Description: application/x-tar (8.3 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Tino WildenhainDate: 2008-04-21 21:01:43
Subject: Re: TODO, FAQs to Wiki?
Previous:From: Chris BrowneDate: 2008-04-21 20:40:02
Subject: Problem with server/utils/snapmgr.h

pgsql-patches by date

Next:From: Simon RiggsDate: 2008-04-22 01:18:09
Subject: Re: Improve shutdown during online backup, take 2
Previous:From: Andrew DunstanDate: 2008-04-21 20:21:53
Subject: column level privileges

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