Re: [HACKERS] 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Making serial survive pg_dump
Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Making serial survive pg_dump
Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Making serial survive pg_dump
Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 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 [EMAIL PROTECTED] To: Rod Taylor [EMAIL PROTECTED] Cc: Hackers List [EMAIL PROTECTED] Sent: Thursday, June 13, 2002 5:41 PM Subject: Re: [HACKERS] Making serial survive pg_dump Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Making serial survive pg_dump
-- Rod - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Rod Taylor [EMAIL PROTECTED] Cc: Hackers List [EMAIL PROTECTED] Sent: Thursday, June 13, 2002 9:46 AM Subject: Re: [HACKERS] Making serial survive pg_dump Rod Taylor [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Making serial survive pg_dump
Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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 [EMAIL PROTECTED] To: Rod Taylor [EMAIL PROTECTED] Cc: Hackers List [EMAIL PROTECTED] Sent: Thursday, June 13, 2002 6:05 PM Subject: Re: [HACKERS] Making serial survive pg_dump Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 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
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 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. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Making serial survive pg_dump
Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
