Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Hi, Zoltan Boszormenyi írta: 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 I just saw this on the CommitFest:May page: alvherre says: I'm not sure if this is the same patch in the previous entry, or a different feature I wanted to clarify, the second patch contains two features. 1. stored start value for sequences, ALTER SEQUENCE ... RESTART; 2. (builds on 1.) TRUNCATE ... RESTART IDENTITY; Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Zoltan Boszormenyi wrote: I just saw this on the CommitFest:May page: alvherre says: I'm not sure if this is the same patch in the previous entry, or a different feature I wanted to clarify, the second patch contains two features. 1. stored start value for sequences, ALTER SEQUENCE ... RESTART; 2. (builds on 1.) TRUNCATE ... RESTART IDENTITY; Does this mean that the first patch can be removed? Please do so in that case, and remove my comment too. And perhaps the description of the patch will need a little fixing, too. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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/ sql2008-compliant-seq-v2.patch.gz Description: Unix tar archive -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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/
diff -dcrpN pgsql.orig/src/backend/commands/sequence.c pgsql/src/backend/commands/sequence.c
*** pgsql.orig/src/backend/commands/sequence.c 2008-01-01 20:45:49.0 +0100
--- pgsql/src/backend/commands/sequence.c 2008-04-08 10:51:27.0 +0200
*** static Relation open_share_lock(SeqTable
*** 88,94
static void init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel);
static Form_pg_sequence read_info(SeqTable elm, Relation rel, Buffer *buf);
static void init_params(List *options, bool isInit,
! Form_pg_sequence new, List **owned_by);
static void do_setval(Oid relid, int64 next, bool iscalled);
static void process_owned_by(Relation seqrel, List *owned_by);
--- 88,94
static void init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel);
static Form_pg_sequence read_info(SeqTable elm, Relation rel, Buffer *buf);
static void init_params(List *options, bool isInit,
! Form_pg_sequence new, Form_pg_sequence old, List **owned_by);
static void do_setval(Oid relid, int64 next, bool iscalled);
static void process_owned_by(Relation seqrel, List *owned_by);
*** DefineSequence(CreateSeqStmt *seq)
*** 116,122
NameData name;
/* Check and set all option values */
! init_params(seq-options, true, new, owned_by);
/*
* Create relation (and fill *null *value)
--- 116,122
NameData name;
/* Check and set all option values */
! init_params(seq-options, true, new, NULL, owned_by);
/*
* Create relation (and fill *null *value)
*** DefineSequence(CreateSeqStmt *seq)
*** 143,148
--- 143,153
namestrcpy(name, seq-sequence-relname);
value[i - 1] = NameGetDatum(name);
break;
+ case SEQ_COL_STARTVAL:
+ coldef-typename = makeTypeNameFromOid(INT8OID, -1);
+ coldef-colname = start_value;
+ value[i - 1] = Int64GetDatumFast(new.start_value);
+ break;
case SEQ_COL_LASTVAL:
coldef-typename = makeTypeNameFromOid(INT8OID, -1);
coldef-colname = last_value;
*** AlterSequence(AlterSeqStmt *stmt)
*** 336,342
memcpy(new, seq, sizeof(FormData_pg_sequence));
/* Check and set new values */
! init_params(stmt-options, false, new, owned_by);
/* Clear local cache so that we don't think we have cached numbers */
/* Note that we do not change the currval() state */
--- 341,347
memcpy(new, seq, sizeof(FormData_pg_sequence));
/* Check and set new values */
! init_params(stmt-options, false, new, seq, owned_by);
/* Clear local cache so that we don't think we have cached numbers */
/* Note that we do not change the currval() state */
*** read_info(SeqTable elm, Relation rel, Bu
*** 967,973
*/
static void
init_params(List *options, bool isInit,
! Form_pg_sequence new, List **owned_by)
{
DefElem*last_value = NULL;
DefElem*increment_by = NULL;
--- 972,978
*/
static void
init_params(List *options, bool isInit,
! Form_pg_sequence new, Form_pg_sequence old, List **owned_by)
{
DefElem*last_value = NULL;
DefElem*increment_by = NULL;
*** init_params(List *options, bool isInit,
*** 995,1003
/*
*
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
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. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
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 -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
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. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Simon Riggs [EMAIL PROTECTED] writes: SQL200n specifies a new qualifier on a TRUNCATE command TRUNCATE TABLE foo [ CONTINUE IDENTITY | RESTART IDENTITY ] CONTINUE IDENTITY is the default and does nothing, like now. RESTART IDENTITY will reset the SERIAL sequences back to the original start value. Seems like a % project for the TODO list Seems like copying syntax from a *draft* standard is a bit premature, especially when the amount of functionality added is nil. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
On Tue, 2008-03-25 at 11:48 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: SQL200n specifies a new qualifier on a TRUNCATE command TRUNCATE TABLE foo [ CONTINUE IDENTITY | RESTART IDENTITY ] CONTINUE IDENTITY is the default and does nothing, like now. RESTART IDENTITY will reset the SERIAL sequences back to the original start value. Seems like a % project for the TODO list Seems like copying syntax from a *draft* standard is a bit premature, especially when the amount of functionality added is nil. It's at the final yes-or-no vote stage. Seems unlikely to be no to me, and it would be good to be seen to be proactive on standards support. The added functionality in this case isn't nil. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Simon Riggs wrote: RESTART IDENTITY will reset the SERIAL sequences back to the original start value. Assuming this feature were to be added In cases where the same sequence has been used across multiple tables, what will be the appropriate response when a user attempts to TRUNCATE one of those tables with RESTART IDENTITY? Cheers, Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
On Tue, 2008-03-25 at 09:08 -0700, Steve Crawford wrote: Simon Riggs wrote: RESTART IDENTITY will reset the SERIAL sequences back to the original start value. Assuming this feature were to be added In cases where the same sequence has been used across multiple tables, what will be the appropriate response when a user attempts to TRUNCATE one of those tables with RESTART IDENTITY? Well, I'm suggesting it as a TODO item, based on the standard. It would be for whoever took this up to unravel that. Since that's a weak answer, I'd say it should only reset sequences that have been placed there automatically through the use of SERIAL or BIGSERIAL datatypes. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Simon Riggs írta: On Tue, 2008-03-25 at 09:08 -0700, Steve Crawford wrote: Simon Riggs wrote: RESTART IDENTITY will reset the SERIAL sequences back to the original start value. Assuming this feature were to be added In cases where the same sequence has been used across multiple tables, what will be the appropriate response when a user attempts to TRUNCATE one of those tables with RESTART IDENTITY? Well, I'm suggesting it as a TODO item, based on the standard. It would be for whoever took this up to unravel that. Since that's a weak answer, I'd say it should only reset sequences that have been placed there automatically through the use of SERIAL or BIGSERIAL datatypes. 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. -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
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 do think we need to wait for the standard to be accepted before adding them to the TODO list as standard-compliant additions, especially because no one is asking for the syntax yet. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Simon Riggs [EMAIL PROTECTED] writes: SQL200n specifies a new qualifier on a TRUNCATE command TRUNCATE TABLE foo [ CONTINUE IDENTITY | RESTART IDENTITY ] CONTINUE IDENTITY is the default and does nothing, like now. RESTART IDENTITY will reset the SERIAL sequences back to the original start value. Seems like a % project for the TODO list I think we need SQL standard IDENTITY columns before we can consider adding SQL standard CONTINUE IDENTITY or RESTART IDENTITY clauses. The reason the last attempt to add them petered out was precisely because they *don't* exactly line up with the semantics of sequences so I don't imagine attempting to shoehorn sequences into these clauses is likely to pan out. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE TABLE with IDENTITY
Zoltan Boszormenyi írta: Simon Riggs írta: On Tue, 2008-03-25 at 09:08 -0700, Steve Crawford wrote: Simon Riggs wrote: RESTART IDENTITY will reset the SERIAL sequences back to the original start value. Assuming this feature were to be added In cases where the same sequence has been used across multiple tables, what will be the appropriate response when a user attempts to TRUNCATE one of those tables with RESTART IDENTITY? Well, I'm suggesting it as a TODO item, based on the standard. It would be for whoever took this up to unravel that. Since that's a weak answer, I'd say it should only reset sequences that have been placed there automatically through the use of SERIAL or BIGSERIAL datatypes. 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, ...); of course CREATE TABLE t1 (id1 integer, ...); 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. -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
