SEO

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


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

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

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

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


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

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

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

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

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

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

"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!

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

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

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