Horton

Thread

  1. TRUNCATE TABLE with IDENTITY

    Simon Riggs <simon@2ndquadrant.com> — 2008-03-25T14:44:58Z

    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

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com 

  PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk

  2. Re: TRUNCATE TABLE with IDENTITY

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-03-25T15:48:48Z

    Simon Riggs <simon@2ndquadrant.com> 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

  3. Re: TRUNCATE TABLE with IDENTITY

    Simon Riggs <simon@2ndquadrant.com> — 2008-03-25T16:02:25Z

    On Tue, 2008-03-25 at 11:48 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> 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

  4. Re: TRUNCATE TABLE with IDENTITY

    Steve Crawford <scrawford@pinpointresearch.com> — 2008-03-25T16:08:40Z

    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

  5. Re: TRUNCATE TABLE with IDENTITY

    Simon Riggs <simon@2ndquadrant.com> — 2008-03-25T16:18:50Z

    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

  6. Re: TRUNCATE TABLE with IDENTITY

    Boszormenyi Zoltan <zb@cybertec.at> — 2008-03-25T16:40:49Z

    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/

  7. Re: TRUNCATE TABLE with IDENTITY

    Boszormenyi Zoltan <zb@cybertec.at> — 2008-03-25T16:42:33Z

    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/

  8. Re: TRUNCATE TABLE with IDENTITY

    Bruce Momjian <bruce@momjian.us> — 2008-03-25T16:43:36Z

    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  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

  9. Re: TRUNCATE TABLE with IDENTITY

    Gregory Stark <stark@enterprisedb.com> — 2008-03-25T16:45:45Z

    "Simon Riggs" <simon@2ndquadrant.com> 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!

  10. Re: TRUNCATE TABLE with IDENTITY

    Decibel! <decibel@decibel.org> — 2008-04-02T22:53:10Z

    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  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

  11. Re: TRUNCATE TABLE with IDENTITY

    Boszormenyi Zoltan <zb@cybertec.at> — 2008-04-03T05:52:25Z

    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/

  12. Re: TRUNCATE TABLE with IDENTITY

    Decibel! <decibel@decibel.org> — 2008-04-07T18:26:52Z

    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  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

  13. Re: TRUNCATE TABLE with IDENTITY

    Boszormenyi Zoltan <zb@cybertec.at> — 2008-04-07T19:37:40Z

    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/

  14. Re: [HACKERS] TRUNCATE TABLE with IDENTITY

    Boszormenyi Zoltan <zb@cybertec.at> — 2008-04-08T09:09:39Z

    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/

  15. Re: [HACKERS] TRUNCATE TABLE with IDENTITY

    Boszormenyi Zoltan <zb@cybertec.at> — 2008-04-21T20:42:30Z

    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/

  16. Re: TRUNCATE TABLE with IDENTITY

    Boszormenyi Zoltan <zb@cybertec.at> — 2008-04-22T15:19:11Z

    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/

  17. Re: TRUNCATE TABLE with IDENTITY

    Alvaro Herrera <alvherre@commandprompt.com> — 2008-04-22T16:01:02Z

    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 Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

  18. Re: [HACKERS] TRUNCATE TABLE with IDENTITY

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-05-16T23:41:15Z

    Zoltan Boszormenyi <zb@cybertec.at> writes:
>> Attached patch implements the extension found in the current SQL200n draft,
>> implementing stored start value and supporting ALTER SEQUENCE seq RESTART;

> Updated patch implements TRUNCATE ... RESTART IDENTITY
> which restarts all owned sequences for the truncated table(s).

Applied with corrections.  Most notably, since ALTER SEQUENCE RESTART
is nontransactional like most other ALTER SEQUENCE operations, I
rearranged things to try to ensure that foreseeable failures like
deadlock and lack of permissions would be detected before TRUNCATE
starts to issue any RESTART commands.

One interesting point here is that the patch as submitted allowed
ALTER SEQUENCE MINVALUE/MAXVALUE to be used to set a sequence range
that the original START value was outside of.  This would result in
a failure at ALTER SEQUENCE RESTART.  Since, as stated above, we
really don't want that happening during TRUNCATE, I adjusted the
patch to make such an ALTER SEQUENCE fail.  This is at least potentially
an incompatible change: command sequences that used to be legal could
now fail.  I doubt it's very likely to bite anyone in practice, though.

			regards, tom lane

  19. Re: [PATCHES] TRUNCATE TABLE with IDENTITY

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-05-17T00:15:37Z

    I wrote:
> One interesting point here is that the patch as submitted allowed
> ALTER SEQUENCE MINVALUE/MAXVALUE to be used to set a sequence range
> that the original START value was outside of.  This would result in
> a failure at ALTER SEQUENCE RESTART.  Since, as stated above, we
> really don't want that happening during TRUNCATE, I adjusted the
> patch to make such an ALTER SEQUENCE fail.  This is at least potentially
> an incompatible change: command sequences that used to be legal could
> now fail.  I doubt it's very likely to bite anyone in practice, though.

It occurs to me that we could define
	ALTER SEQUENCE s START WITH x
(which is syntactically legal, but rejected by sequence.c at the moment)
as updating the stored start_value and thus affecting what future
ALTER SEQUENCE RESTART commands will do.  Right now there is simply
no way to change start_value after sequence creation, which is pretty
strange considering we let you change every other sequence parameter.
It would also provide a way out for anyone who does want to change the
minval/maxval as sketched above.

I think this is about a ten-line change as far as the code goes...
any objections?

			regards, tom lane

  20. Re: [HACKERS] TRUNCATE TABLE with IDENTITY

    Neil Conway <neilc@samurai.com> — 2008-05-17T01:35:52Z

    On Fri, 2008-05-16 at 19:41 -0400, Tom Lane wrote:
> Applied with corrections.  Most notably, since ALTER SEQUENCE RESTART
> is nontransactional like most other ALTER SEQUENCE operations, I
> rearranged things to try to ensure that foreseeable failures like
> deadlock and lack of permissions would be detected before TRUNCATE
> starts to issue any RESTART commands.

Ugh. The fact that the RESTART IDENTITY part of TRUNCATE is
non-transactional is a pretty unsightly wort. I would also quarrel with
your addition to the docs that suggests this is only an issue "in
practice" if TRUNCATE RESTART IDENTITY is used in a transaction block:
unpredictable failures (such as OOM or query cancellation) can certainly
occur in practice, and would be very disruptive (e.g. if the sequence
values are stored into a column with a UNIQUE constraint, it would break
all inserting transactions until the DBA intervenes).

I wonder if it would be possible to make the sequence operations
performed by TRUNCATE transactional: while the TRUNCATE remains
uncommitted, it should be okay to block concurrent access to the
sequence.

-Neil

  21. Re: [HACKERS] TRUNCATE TABLE with IDENTITY

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-05-17T01:50:01Z

    Neil Conway <neilc@samurai.com> writes:
> Ugh. The fact that the RESTART IDENTITY part of TRUNCATE is
> non-transactional is a pretty unsightly wort.

Actually, I agree.  Shall we just revert that feature?  The ALTER
SEQUENCE part of this patch is clean and useful, but I'm less than
enamored of the TRUNCATE part.

			regards, tom lane

  22. Re: [HACKERS] TRUNCATE TABLE with IDENTITY

    Simon Riggs <simon@2ndquadrant.com> — 2008-05-17T08:58:10Z

    On Fri, 2008-05-16 at 21:50 -0400, Tom Lane wrote:
> Neil Conway <neilc@samurai.com> writes:
> > Ugh. The fact that the RESTART IDENTITY part of TRUNCATE is
> > non-transactional is a pretty unsightly wort.
> 
> Actually, I agree.  Shall we just revert that feature?  The ALTER
> SEQUENCE part of this patch is clean and useful, but I'm less than
> enamored of the TRUNCATE part.

Perhaps, but we should also take into account that TRUNCATE is not and
never will be MVCC compliant, so its not something you'd expect to run
except as a maintenance action. If we do keep it, I would suggest that
we add a WARNING so that the effects are clearly recorded.

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support

  23. Re: [HACKERS] TRUNCATE TABLE with IDENTITY

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-05-17T16:04:55Z

    Simon Riggs <simon@2ndquadrant.com> writes:
> On Fri, 2008-05-16 at 21:50 -0400, Tom Lane wrote:
>> Actually, I agree.  Shall we just revert that feature?

> Perhaps, but we should also take into account that TRUNCATE is not and
> never will be MVCC compliant, so its not something you'd expect to run
> except as a maintenance action.

Good point.  I had a couple of further thoughts this morning:

1. The case Neil is worried about is something like

	BEGIN;
	TRUNCATE TABLE foo RESTART IDENTITY;
	COPY foo FROM ...;
	COMMIT;

If the COPY fails partway through, the old table contents are restored,
but the sequences are not.  However removing RESTART IDENTITY will not
remove the hazard, because there is no difference between this and

	BEGIN;
	TRUNCATE TABLE foo;
	SELECT setval('foo_id', 1);
	COPY foo FROM ...;
	COMMIT;

other than the latter adding a little extra chance for pilot error in
resetting the wrong sequence.  So if we revert the patch we haven't
accomplished much except to take away an opportunity to point out the
risk.  I vote for leaving the patch in and rewriting the <warning>
to point out this risk.

2. I had first dismissed Neil's idea of transactional sequence updates
as impossible, but on second look it could be done.  Suppose RESTART
IDENTITY does this for each sequence;

	* obtain AccessExclusiveLock;
	* assign a new relfilenode;
	* insert a sequence row with all parameters copied except
	  last_value copies start_value;
	* hold AccessExclusiveLock till commit.

IOW just like truncate-and-reload, but for a sequence.  Within the
current backend, subsequent operations see the new sequence values.
If the transaction rolls back, the old sequence relfilenode is still
there and untouched.

It's slightly annoying to need to lock out other backends' nextval
operations, but for the use-case of TRUNCATE this doesn't seem
like it's really much of a problem.

I'm not sure if it'd be worth exposing this behavior as a separate
user-visible command (CREATE OR REPLACE SEQUENCE, maybe?), but it seems
worth doing to make TRUNCATE-and-reload less of a foot gun.

So what I think we should do is leave the patch there, revise the
warning per Neil's complaint, and add a TODO item to reimplement RESTART
IDENTITY transactionally.

			regards, tom lane

  24. Re: [HACKERS] TRUNCATE TABLE with IDENTITY

    Simon Riggs <simon@2ndquadrant.com> — 2008-05-17T16:23:58Z

    On Sat, 2008-05-17 at 12:04 -0400, Tom Lane wrote:

> So what I think we should do is leave the patch there, revise the
> warning per Neil's complaint, and add a TODO item to reimplement
> RESTART IDENTITY transactionally.

Sounds good.

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support

  25. Re: [HACKERS] TRUNCATE TABLE with IDENTITY

    Alvaro Herrera <alvherre@commandprompt.com> — 2008-06-08T00:30:50Z

    Tom Lane wrote:

> 2. I had first dismissed Neil's idea of transactional sequence updates
> as impossible, but on second look it could be done.  Suppose RESTART
> IDENTITY does this for each sequence;
> 
> 	* obtain AccessExclusiveLock;
> 	* assign a new relfilenode;
> 	* insert a sequence row with all parameters copied except
> 	  last_value copies start_value;
> 	* hold AccessExclusiveLock till commit.

Hmm, this kills the idea of moving sequence data to a single
non-transactional catalog :-(

> So what I think we should do is leave the patch there, revise the
> warning per Neil's complaint, and add a TODO item to reimplement RESTART
> IDENTITY transactionally.

I think the TODO item did not make it, but the docs do seem updated.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

  26. Re: [HACKERS] TRUNCATE TABLE with IDENTITY

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-06-08T16:26:06Z

    Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> 2. I had first dismissed Neil's idea of transactional sequence updates
>> as impossible, but on second look it could be done.  Suppose RESTART
>> IDENTITY does this for each sequence;
>> 
>> * obtain AccessExclusiveLock;
>> * assign a new relfilenode;
>> * insert a sequence row with all parameters copied except
>> last_value copies start_value;
>> * hold AccessExclusiveLock till commit.

> Hmm, this kills the idea of moving sequence data to a single
> non-transactional catalog :-(

Well, there are a number of holes in our ideas of how to do that anyway.
But offhand I don't see why we couldn't distinguish regular heap_update
from update_in_place on single rows within a catalog.

			regards, tom lane

  27. Re: [PATCHES] TRUNCATE TABLE with IDENTITY

    Bruce Momjian <bruce@momjian.us> — 2008-07-01T18:09:28Z

    Alvaro Herrera wrote:
> Tom Lane wrote:
> 
> > 2. I had first dismissed Neil's idea of transactional sequence updates
> > as impossible, but on second look it could be done.  Suppose RESTART
> > IDENTITY does this for each sequence;
> > 
> > 	* obtain AccessExclusiveLock;
> > 	* assign a new relfilenode;
> > 	* insert a sequence row with all parameters copied except
> > 	  last_value copies start_value;
> > 	* hold AccessExclusiveLock till commit.
> 
> Hmm, this kills the idea of moving sequence data to a single
> non-transactional catalog :-(
> 
> > So what I think we should do is leave the patch there, revise the
> > warning per Neil's complaint, and add a TODO item to reimplement RESTART
> > IDENTITY transactionally.
> 
> I think the TODO item did not make it, but the docs do seem updated.

Done:

* Fix TRUNCATE ... RESTART IDENTITY so its affect on sequences is rolled
  back on transaction abort

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +