Thread: 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. 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
"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
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 >
-- 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
"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 > > the name and use it. > > Sure. But what's this have to do with what pg_dump should emit? > > regards, tom lane >
"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 > with that. > > regards, tom lane >
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.