Re: [PATCHES] [HACKERS] TRUNCATE TABLE with IDENTITY

[Zoltan Boszormenyi]

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):


  an identity column, then:

      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

when it comes to IDENTITY sequence options. And surprise, surprise,
"11.64 <alter sequence generator statement>" now defines
ALTER SEQUENCE sn RESTART [WITH newvalue]


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

8) If RESTART IDENTITY is specified and the table descriptor of T includes a column descriptor IDCD of 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: "identity column specification" links to "11.63 <sequence generator definition>" where omitting the "WITH newval" part also uses the original start value.

and ALTER SEQUENCE ... START ...

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 ...

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/

sql2008-compliant-seq-v2.patch.gz
Description: Unix tar archive

-- 
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches