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

--
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?

"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

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

"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

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

"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

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

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

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

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.

"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

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

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

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.