Currently serial is dumped as a sequence and appropriate default
statement.

With my upcoming dependency patch serials depend on the appropriate
column.  Drop the column (or table) and the sequence goes with it.
The depencency information does not survive the pg_dump / restore
process however as it's recreated as the table and individual
sequence.

I see 2 options for carrying the information.

Store sequence information in the SERIAL creation statement:
CREATE TABLE tab (col1 SERIAL(<start num>, <sequence name>));

Or store the dependency information in the sequence:
CREATE SEQUENCE ... REQUIRES COLUMN <column>;


The former makes a lot more sense, and it's nice that the sequence
information is in one place.
--
Rod


Thu, 13 Jun 2002 08:10:01 -0400
"Rod Taylor" <rbt@zort.ca>
Making serial survive pg_dump
"Rod Taylor" <rbt@zort.ca> writes:
> Store sequence information in the SERIAL creation statement:
> CREATE TABLE tab (col1 SERIAL(<start num>, <sequence name>));

This is wrong because it loses the separation between schema and data.
I do agree that it would be nice if pg_dump recognized serial columns
and dumped them as such --- but the separate setval call is still the
appropriate technique for messing with the sequence contents.  We do
not need a syntax extension in CREATE.

			regards, tom lane

Thu, 13 Jun 2002 09:46:21 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Making serial survive pg_dump 

--
Rod
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Rod Taylor" <rbt@zort.ca>
Cc: "Hackers List" <pgsql-hackers@postgresql.org>
Sent: Thursday, June 13, 2002 9:46 AM
Subject: Re: [HACKERS] Making serial survive pg_dump


> "Rod Taylor" <rbt@zort.ca> writes:
> > Store sequence information in the SERIAL creation statement:
> > CREATE TABLE tab (col1 SERIAL(<start num>, <sequence name>));
>
> This is wrong because it loses the separation between schema and
data.
> I do agree that it would be nice if pg_dump recognized serial
columns
> and dumped them as such --- but the separate setval call is still
the
> appropriate technique for messing with the sequence contents.  We do
> not need a syntax extension in CREATE.

Ok, keeping the setval is appropriate.  Are there any problems with a
SERIAL(<sequence name>) implementation?




Thu, 13 Jun 2002 17:30:30 -0400
"Rod Taylor" <rbt@zort.ca>
Re: Making serial survive pg_dump 
"Rod Taylor" <rbt@zort.ca> writes:
> Ok, keeping the setval is appropriate.  Are there any problems with a
> SERIAL(<sequence name>) implementation?

What for?  The sequence name is an implementation detail, not something
we want to expose (much less let users modify).

			regards, tom lane

Thu, 13 Jun 2002 17:41:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Making serial survive pg_dump 
Normally I'd agree, but I've found a few people who use normal
sequence operations with serial sequences.  That is, they track down
the name and use it.

I'd prefer to force these people to make it manually, but would be
surprised if that was a concensus.

--
Rod
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Rod Taylor" <rbt@zort.ca>
Cc: "Hackers List" <pgsql-hackers@postgresql.org>
Sent: Thursday, June 13, 2002 5:41 PM
Subject: Re: [HACKERS] Making serial survive pg_dump


> "Rod Taylor" <rbt@zort.ca> writes:
> > Ok, keeping the setval is appropriate.  Are there any problems
with a
> > SERIAL(<sequence name>) implementation?
>
> What for?  The sequence name is an implementation detail, not
something
> we want to expose (much less let users modify).
>
> regards, tom lane
>


Thu, 13 Jun 2002 17:45:14 -0400
"Rod Taylor" <rbt@zort.ca>
Re: Making serial survive pg_dump 
"Rod Taylor" <rbt@zort.ca> writes:
> Normally I'd agree, but I've found a few people who use normal
> sequence operations with serial sequences.  That is, they track down
> the name and use it.

Sure.  But what's this have to do with what pg_dump should emit?

			regards, tom lane

Thu, 13 Jun 2002 17:52:34 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Making serial survive pg_dump 
If we have sequences pick new names automatically, it may not pick the
same name after dump / restore as it had earlier -- especially across
versions (see TODO entry).

So don't we need a way to suggest the *right* name to SERIAL?

--
Rod
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Rod Taylor" <rbt@zort.ca>
Cc: "Hackers List" <pgsql-hackers@postgresql.org>
Sent: Thursday, June 13, 2002 5:52 PM
Subject: Re: [HACKERS] Making serial survive pg_dump


> "Rod Taylor" <rbt@zort.ca> writes:
> > Normally I'd agree, but I've found a few people who use normal
> > sequence operations with serial sequences.  That is, they track
down
> > the name and use it.
>
> Sure.  But what's this have to do with what pg_dump should emit?
>
> regards, tom lane
>


Thu, 13 Jun 2002 17:56:42 -0400
"Rod Taylor" <rbt@zort.ca>
Re: Making serial survive pg_dump 
"Rod Taylor" <rbt@zort.ca> writes:
> If we have sequences pick new names automatically, it may not pick the
> same name after dump / restore as it had earlier -- especially across
> versions (see TODO entry).
> So don't we need a way to suggest the *right* name to SERIAL?

No.  IMHO, if we change the naming convention for serial sequences (which
seems unlikely, except that it might be indirectly affected by changing
NAMEDATALEN), then we'd *want* the new naming convention to take effect,
not to have pg_dump scripts force an old naming convention to be
preserved.

I realize there's a potential for failing to restore the setval()
information if the name actually does change, but I'm willing to live
with that.

			regards, tom lane

Thu, 13 Jun 2002 18:05:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Making serial survive pg_dump 
Thats fair, and makes the job a heck of a lot simpler.

We do need to change the sequence naming once.  They have a tendency
to conflict at the moment.

--
Rod
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Rod Taylor" <rbt@zort.ca>
Cc: "Hackers List" <pgsql-hackers@postgresql.org>
Sent: Thursday, June 13, 2002 6:05 PM
Subject: Re: [HACKERS] Making serial survive pg_dump


> "Rod Taylor" <rbt@zort.ca> writes:
> > If we have sequences pick new names automatically, it may not pick
the
> > same name after dump / restore as it had earlier -- especially
across
> > versions (see TODO entry).
> > So don't we need a way to suggest the *right* name to SERIAL?
>
> No.  IMHO, if we change the naming convention for serial sequences
(which
> seems unlikely, except that it might be indirectly affected by
changing
> NAMEDATALEN), then we'd *want* the new naming convention to take
effect,
> not to have pg_dump scripts force an old naming convention to be
> preserved.
>
> I realize there's a potential for failing to restore the setval()
> information if the name actually does change, but I'm willing to
live
> with that.
>
> regards, tom lane
>


Thu, 13 Jun 2002 18:11:05 -0400
"Rod Taylor" <rbt@zort.ca>
Re: Making serial survive pg_dump 

Folks,

> No.  IMHO, if we change the naming convention for serial sequences (which
> seems unlikely, except that it might be indirectly affected by changing
> NAMEDATALEN), then we'd *want* the new naming convention to take effect,
> not to have pg_dump scripts force an old naming convention to be
> preserved.
> 
> I realize there's a potential for failing to restore the setval()
> information if the name actually does change, but I'm willing to live
> with that.

IMNHO, if this is such a concern for the developer, then what about using 
explicitly named sequences?  I almost never use the SERIAL data type, because 
I feel that I need naming control as well as explicit permissions.  SERIAL is 
a convenience for those who don't want to be bothered ... serious developers 
hould use DEFAULT NEXTVAL('sequence_name').

-- 
-Josh Berkus

Thu, 13 Jun 2002 16:55:52 -0700
Josh Berkus <josh@agliodbs.com>
Re: Making serial survive pg_dump
> Currently serial is dumped as a sequence and appropriate default
> statement.
>
> With my upcoming dependency patch serials depend on the appropriate
> column.  Drop the column (or table) and the sequence goes with it.
> The depencency information does not survive the pg_dump / restore
> process however as it's recreated as the table and individual
> sequence.

What happens is the sequence is shared between several tables (eg. invoice
numbers or something)

Chris


Fri, 14 Jun 2002 10:15:06 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Making serial survive pg_dump
> What happens is the sequence is shared between several tables (eg.
invoice
> numbers or something)

You cannot accomplish this situation by strictly using the SERIAL
type.


Thu, 13 Jun 2002 22:20:18 -0400
"Rod Taylor" <rbt@zort.ca>
Re: Making serial survive pg_dump
"Rod Taylor" <rbt@zort.ca> writes:
>> What happens is the sequence is shared between several tables (eg.
>> invoice numbers or something)

> You cannot accomplish this situation by strictly using the SERIAL
> type.

But Chris is correct that there are borderline cases where we might
do the wrong thing if we're not careful.  The real question here,
I suspect, is what rules pg_dump will use to decide that it ought
to suppress a CREATE SEQUENCE command, DEFAULT clause, etc, in
favor of emitting a SERIAL column datatype.  In particular, ought it
to depend on looking at the form of the name of the sequence?
I can see arguments both ways on that...

			regards, tom lane

Thu, 13 Jun 2002 23:11:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Making serial survive pg_dump 
Tom Lane wrote:
> "Rod Taylor" <rbt@zort.ca> writes:
> 
>>>What happens is the sequence is shared between several tables (eg.
>>>invoice numbers or something)
>>
> 
>>You cannot accomplish this situation by strictly using the SERIAL
>>type.
> 
> 
> But Chris is correct that there are borderline cases where we might
> do the wrong thing if we're not careful.  The real question here,
> I suspect, is what rules pg_dump will use to decide that it ought
> to suppress a CREATE SEQUENCE command, DEFAULT clause, etc, in
> favor of emitting a SERIAL column datatype.  In particular, ought it
> to depend on looking at the form of the name of the sequence?
> I can see arguments both ways on that...
> 

I think that when SERIAL is used, the sequence should be tied 
inextricably to the table which created it, and it should be hidden from 
use for other purposes (perhaps similar to the way a toast table is). If 
you *want* to use a sequence across several tables, then you don't use 
SERIAL, you create a sequence.

Many people who come from an MS SQL Server background are used to an 
IDENTITY column being tied transparently to the table in this fashion, 
and they initially find sequences confusing. Conversely, people coming 
from an Oracle background are quite comfortable with sequences, and 
don't understand why it is necessary to have an IDENTITY type column at 
all -- they seem too restrictive. We have people from both backgrounds 
where I work, and both databases in use for various applications, and 
this is at least what I have observed.

This is a chance for PostgreSQL to support people from both camps 
equally well.

Anyway, just my 2c :-)

Joe


Thu, 13 Jun 2002 21:11:55 -0700
Joe Conway <mail@joeconway.com>
Re: Making serial survive pg_dump
> I think that when SERIAL is used, the sequence should be tied 
> inextricably to the table which created it, and it should be hidden from 
> use for other purposes (perhaps similar to the way a toast table is). If 
> you *want* to use a sequence across several tables, then you don't use 
> SERIAL, you create a sequence.

Agreed.  Maybe an extra column in pg_attribute or something?

Chris


Fri, 14 Jun 2002 12:43:42 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Making serial survive pg_dump
> > I think that when SERIAL is used, the sequence should be tied
> > inextricably to the table which created it, and it should be
hidden from
> > use for other purposes (perhaps similar to the way a toast table
is). If
> > you *want* to use a sequence across several tables, then you don't
use
> > SERIAL, you create a sequence.
>
> Agreed.  Maybe an extra column in pg_attribute or something?

Since no other sequence will depend on a column, I could base it on
that.


Fri, 14 Jun 2002 19:07:56 -0400
"Rod Taylor" <rbt@zort.ca>
Re: Making serial survive pg_dump