ALTER SEQUENCE

Hi,

I have sent in a few patches in the last couple of days (and resent) and
they haven't appeared on the lists - what's going on?

Chris

I think the mail server was busted over the last couple of days
(Thursday till Sunday afternoon).

Read archives of -hackers, you'll see there were a few conversations
going on that were never actually distributed.

Anyway, nothing wrong with re-posting them.

On Mon, 2003-03-03 at 02:06, Christopher Kings-Lynne wrote:

Hi,

I have sent in a few patches in the last couple of days (and resent) and
they haven't appeared on the lists - what's going on?

Chris

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

On Mon, 3 Mar 2003, Rod Taylor wrote:

I think the mail server was busted over the last couple of days
(Thursday till Sunday afternoon).

Read archives of -hackers, you'll see there were a few conversations
going on that were never actually distributed.

Am still looking at it ... I'm seeing a very high # of 'relay deniued'
messages on messages going out, yet the maillog files are showing nothing
to match it ...

Can those that haven't been seeing much of the threads check their logs
(if possible) to see if they are seeing such in their log files?

I did an upgraded to Postfix 2.0.4 on Friday, and suspect one of its
behaviours has changed, but I can't find what it is that I have
mis-configured ...

Hey, with this new ALTER SEQUENCE patch, how about this for an idea:

I submitted a patch to always generate non-colliding index and sequence
names. Seemed like an excellent idea. However, 7.3 dumps tables like this:

CREATE TABLE blah
a SERIAL
);

SELECT SETVAL('blah_a_seq', 10);

Sort of thing...

How about we add a new form to ALTER SEQUENCE <sequence> ...?

ALTER SEQUENCE ON blah(a) CURRVAL 10.... (or whatever the syntax is)

or even

ALTER SERIAL ON blah(a)...

Which would allow us to dump tables in an environment where you do now know
exactly what the generated name will be...

Chris

On Mon, 2003-03-03 at 20:47, Christopher Kings-Lynne wrote:

Hey, with this new ALTER SEQUENCE patch, how about this for an idea:

I submitted a patch to always generate non-colliding index and sequence
names. Seemed like an excellent idea. However, 7.3 dumps tables like this:

CREATE TABLE blah
a SERIAL
);

SELECT SETVAL('blah_a_seq', 10);

Sort of thing...

How about we add a new form to ALTER SEQUENCE <sequence> ...?

ALTER SEQUENCE ON blah(a) CURRVAL 10.... (or whatever the syntax is)

The spec proposes:

ALTER SEQUENCE <sequence> RESTART WITH <value>;

I suppose (since SERIAL is nonstandard anyway) we could do:

ALTER SEQUENCE ON table(column) RESTART WITH <value>;

The problem is that we really don't have an easy way of determining if
there is a sequence on table(column) to start with and ONLY that table.

I don't think I'd want to allow that on user sequences at all because
they're often used in stranger ways, and the user doing the alteration
may not know that.

As far as getting dependencies on the sequence, the currently proposed
method of retrieving the next value of a sequence generator is 'NEXT
VALUE FOR <sequence>' -- but Tom isn't going to like that :)

Might get somewhere by making a special domain thats marked as being
serial, and using that in the column. Create the sequence and tie it to
the domain. Now you know the sequence tied to the column (because it's
on the domain). Just disallow 'special' serial sequences & domains to
be used in other ways.

Prevention of the domain from being altered would also help, as you can
then prevent the default from changing.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Rod Taylor <rbt@rbt.ca> writes:

Might get somewhere by making a special domain thats marked as being
serial, and using that in the column.

I recall some discussion last year about making serial et al. into
domains over int4 and int8, rather than their current utter-hack
implementation. Can't recall if we found a problem with the idea,
or no one got around to doing it, or it just didn't seem to clean
things up enough to be worth the trouble. (AFAICS you'd still need
special-case code to set up the appropriate default expression for
each column; the domain constraint mechanism wouldn't handle that
for you.)

Seems worth looking at, though.

regards, tom lane

On Tue, 2003-03-04 at 19:14, Tom Lane wrote:

Rod Taylor <rbt@rbt.ca> writes:

Might get somewhere by making a special domain thats marked as being
serial, and using that in the column.

I recall some discussion last year about making serial et al. into
domains over int4 and int8, rather than their current utter-hack
implementation. Can't recall if we found a problem with the idea,
or no one got around to doing it, or it just didn't seem to clean
things up enough to be worth the trouble. (AFAICS you'd still need
special-case code to set up the appropriate default expression for
each column; the domain constraint mechanism wouldn't handle that
for you.)

Slightly different thought. I had actually submitted a patch for the
above, but would have to dig through the archives to determine what the
problem was.

CREATE TABLE tab (column SERIAL);

Generates:

table(column serial_table_column)

domain serial_table_column
as int4 default nextval("serial_table_column")

Sequence serial_table_column.

Now, rather than having knowedge of the contents of nextval, we simply
trace the dependencies of the column through the domain to the sequence
-- since these will be known to exist.

Thus the below command could function fairly easily on serials:

ALTER SEQUENCE ON table(column)

The alternative is to simply implement the proposed 200N sequence
generator spec, which includes 'NEXT VALUE FOR <sequence>'.

With that in place, our default would then depend on the sequence, and
the ALTER SEQUENCE ON table(column) would function.
--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Does it make sense to avoid sequence name collisions if applications
have to refer to sequence names directly? I mean, I can imagine a case
where a restore would return a sequence name that is different from the
one that dumped it. pg_dump may be hacked to fix that (look up the
sequence for the column) but what about applications. Seems any real
solution is going to need removal of direct sequence name references in
applications.

---------------------------------------------------------------------------

Rod Taylor wrote:
-- Start of PGP signed section.

On Mon, 2003-03-03 at 20:47, Christopher Kings-Lynne wrote:

Hey, with this new ALTER SEQUENCE patch, how about this for an idea:

I submitted a patch to always generate non-colliding index and sequence
names. Seemed like an excellent idea. However, 7.3 dumps tables like this:

CREATE TABLE blah
a SERIAL
);

SELECT SETVAL('blah_a_seq', 10);

Sort of thing...

How about we add a new form to ALTER SEQUENCE <sequence> ...?

ALTER SEQUENCE ON blah(a) CURRVAL 10.... (or whatever the syntax is)

The spec proposes:

ALTER SEQUENCE <sequence> RESTART WITH <value>;

I suppose (since SERIAL is nonstandard anyway) we could do:

ALTER SEQUENCE ON table(column) RESTART WITH <value>;

The problem is that we really don't have an easy way of determining if
there is a sequence on table(column) to start with and ONLY that table.

I don't think I'd want to allow that on user sequences at all because
they're often used in stranger ways, and the user doing the alteration
may not know that.

As far as getting dependencies on the sequence, the currently proposed
method of retrieving the next value of a sequence generator is 'NEXT
VALUE FOR <sequence>' -- but Tom isn't going to like that :)

Might get somewhere by making a special domain thats marked as being
serial, and using that in the column. Create the sequence and tie it to
the domain. Now you know the sequence tied to the column (because it's
on the domain). Just disallow 'special' serial sequences & domains to
be used in other ways.

Prevention of the domain from being altered would also help, as you can
then prevent the default from changing.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

-- End of PGP section, PGP failed!

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Fri, 2003-03-07 at 11:27, Bruce Momjian wrote:

Does it make sense to avoid sequence name collisions if applications
have to refer to sequence names directly? I mean, I can imagine a case

Not at all. Hence the thought that we might create syntax to allow
applications to refer to the table / column that the sequence (SERIAL)
is on. This would hide the internal representation of a SERIAL...

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Rod Taylor wrote:
-- Start of PGP signed section.

Todo items:
Add ALTER SEQUENCE to modify min/max/increment/cache/cycle values

Also updated create sequence docs to mention NO MINVALUE, & NO MAXVALUE.

New Files:
doc/src/sgml/ref/alter_sequence.sgml
src/test/regress/expected/sequence.out
src/test/regress/sql/sequence.sql

ALTER SEQUENCE is NOT transactional. It behaves similarly to setval().
It matches the proposed SQL200N spec, as well as Oracle in most ways --
Oracle lacks RESTART WITH for some strange reason.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

[ Attachment, skipping... ]

[ Attachment, skipping... ]

[ Attachment, skipping... ]

[ Attachment, skipping... ]
-- End of PGP section, PGP failed!

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Patch applied. Thanks.

TODO updated.

---------------------------------------------------------------------------

Rod Taylor wrote:
-- Start of PGP signed section.

Todo items:
Add ALTER SEQUENCE to modify min/max/increment/cache/cycle values

Also updated create sequence docs to mention NO MINVALUE, & NO MAXVALUE.

New Files:
doc/src/sgml/ref/alter_sequence.sgml
src/test/regress/expected/sequence.out
src/test/regress/sql/sequence.sql

ALTER SEQUENCE is NOT transactional. It behaves similarly to setval().
It matches the proposed SQL200N spec, as well as Oracle in most ways --
Oracle lacks RESTART WITH for some strange reason.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

[ Attachment, skipping... ]

[ Attachment, skipping... ]

[ Attachment, skipping... ]

[ Attachment, skipping... ]
-- End of PGP section, PGP failed!

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073