Re: [HACKERS] [PATCHES] Non-colliding auto generated names
Rod Taylor [EMAIL PROTECTED] writes: 200N spec proposes 'NEXT VALUE FOR sequence'. Tom will shoot me if I submit that though (VALUE as a keyword again). I suppose one could make it a variable, and confirm it's value is VALUE? Anyway, once again we could extend to include: NEXT VALUE ON table(column)? This is looking messier and messier. And, you are all conveniently ignoring the fact that any change in sequence naming conventions will break existing applications. Offering some completely new syntax that they're supposed to use instead won't make people any happier. I think we should stick with the existing naming convention. The only actual problem that's been pointed out here is that an ALTER TABLE (or COLUMN) RENAME on a serial column doesn't update the sequence name to match. Seems to me we could fix that with less effort than any of these solutions would take, and it wouldn't break existing applications. regards, tom lane ---(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] [PATCHES] Non-colliding auto generated names
On Wed, 2003-03-05 at 10:36, Bruce Momjian wrote:
Ewe. There would no longer be a guaranteed name for the serial column
sequence. Of course, pg_depend has the information, but how do you get
at that when you create the dump file, and be _sure_ you are going to
hit the right name, especially if you restore only part of the dump.
Seems this kills the idea of this patch. With 64-byte names, let's see
if we still get complaints about name conflicts.
We are :)
CREATE TABLE tab (col SERIAL);
ALTER TABLE tab RENAME TO tab2;
CREATE TABLE tab (col SERIAL);
There is a chance we could do something like:
ALTER SEQUENCE ON table(col) RESTART WITH value
instead of
SELECT setval(sequence, value);
The ALTER SEQUENCE syntax isn't any worse than setval... I'd be willing
to implement the feature in order to get this patch accepted.
Oh, and welcome back!
---
Christopher Kings-Lynne wrote:
OK,
I have discovered a problem with my auto-naming patch. It's do to with
dumping serial columns with pg_dump, eg:
--
-- TOC entry 2 (OID 1004551)
-- Name: users_users; Type: TABLE; Schema: public; Owner: chriskl
--
CREATE TABLE users_users (
userid serial NOT NULL,
firstname character varying(255) NOT NULL,
lastname character varying(255) NOT NULL,
email character varying(255) NOT NULL
);
-- DATA DUMPED HERE
--
-- TOC entry 4 (OID 1004305)
-- Name: users_users_userid_seq; Type: SEQUENCE SET; Schema: public; Owner:
chriskl
--
SELECT pg_catalog.setval ('users_users_userid_seq', 126, true);
How do we fix this problem?? Perhaps instead of a hard-coded sequence
string, we can sub-SELECT for it...?
Chris
--
Rod Taylor [EMAIL PROTECTED]
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] [PATCHES] Non-colliding auto generated names
Ewe. There would no longer be a guaranteed name for the serial column
sequence. Of course, pg_depend has the information, but how do you get
at that when you create the dump file, and be _sure_ you are going to
hit the right name, especially if you restore only part of the dump.
Seems this kills the idea of this patch. With 64-byte names, let's see
if we still get complaints about name conflicts.
---
Christopher Kings-Lynne wrote:
OK,
I have discovered a problem with my auto-naming patch. It's do to with
dumping serial columns with pg_dump, eg:
--
-- TOC entry 2 (OID 1004551)
-- Name: users_users; Type: TABLE; Schema: public; Owner: chriskl
--
CREATE TABLE users_users (
userid serial NOT NULL,
firstname character varying(255) NOT NULL,
lastname character varying(255) NOT NULL,
email character varying(255) NOT NULL
);
-- DATA DUMPED HERE
--
-- TOC entry 4 (OID 1004305)
-- Name: users_users_userid_seq; Type: SEQUENCE SET; Schema: public; Owner:
chriskl
--
SELECT pg_catalog.setval ('users_users_userid_seq', 126, true);
How do we fix this problem?? Perhaps instead of a hard-coded sequence
string, we can sub-SELECT for it...?
Chris
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Non-colliding auto generated names
Interesting idea. The bigger problem is that apps who use the sequence
name also would have problems running after the restore. Seems we need
column.nextval() so you can increment the sequence without knowing the
sequence name, just the column name. Of course, this related to this
TODO item:
* Have sequence dependency track use of DEFAULT sequences,
seqname.nextval
Comments?
---
Rod Taylor wrote:
-- Start of PGP signed section.
On Wed, 2003-03-05 at 10:36, Bruce Momjian wrote:
Ewe. There would no longer be a guaranteed name for the serial column
sequence. Of course, pg_depend has the information, but how do you get
at that when you create the dump file, and be _sure_ you are going to
hit the right name, especially if you restore only part of the dump.
Seems this kills the idea of this patch. With 64-byte names, let's see
if we still get complaints about name conflicts.
We are :)
CREATE TABLE tab (col SERIAL);
ALTER TABLE tab RENAME TO tab2;
CREATE TABLE tab (col SERIAL);
There is a chance we could do something like:
ALTER SEQUENCE ON table(col) RESTART WITH value
instead of
SELECT setval(sequence, value);
The ALTER SEQUENCE syntax isn't any worse than setval... I'd be willing
to implement the feature in order to get this patch accepted.
Oh, and welcome back!
---
Christopher Kings-Lynne wrote:
OK,
I have discovered a problem with my auto-naming patch. It's do to with
dumping serial columns with pg_dump, eg:
--
-- TOC entry 2 (OID 1004551)
-- Name: users_users; Type: TABLE; Schema: public; Owner: chriskl
--
CREATE TABLE users_users (
userid serial NOT NULL,
firstname character varying(255) NOT NULL,
lastname character varying(255) NOT NULL,
email character varying(255) NOT NULL
);
-- DATA DUMPED HERE
--
-- TOC entry 4 (OID 1004305)
-- Name: users_users_userid_seq; Type: SEQUENCE SET; Schema: public; Owner:
chriskl
--
SELECT pg_catalog.setval ('users_users_userid_seq', 126, true);
How do we fix this problem?? Perhaps instead of a hard-coded sequence
string, we can sub-SELECT for it...?
Chris
--
Rod Taylor [EMAIL PROTECTED]
PGP Key: http://www.rbt.ca/rbtpub.asc
-- End of PGP section, PGP failed!
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(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] [PATCHES] Non-colliding auto generated names
On Wed, 2003-03-05 at 14:37, Bruce Momjian wrote: Interesting idea. The bigger problem is that apps who use the sequence name also would have problems running after the restore. Seems we need column.nextval() so you can increment the sequence without knowing the sequence name, just the column name. Of course, this related to this TODO item: * Have sequence dependency track use of DEFAULT sequences, seqname.nextval 200N spec proposes 'NEXT VALUE FOR sequence'. Tom will shoot me if I submit that though (VALUE as a keyword again). I suppose one could make it a variable, and confirm it's value is VALUE? Other than that it should be a fairly simple task. Anyway, once again we could extend to include: NEXT VALUE ON table(column)? An application that was simply interested in the next value of a table column could simply evaluate the default value -- which should be easily retrievable and more portable in most interfaces (jdbc, odbc, etc.). -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [PATCHES] Non-colliding auto generated names
Yes, our sequence name/column linkage should be much more automatic than it is now, and if we do that, we can start to think about sequence name collision avoidance. --- Rod Taylor wrote: -- Start of PGP signed section. On Wed, 2003-03-05 at 14:37, Bruce Momjian wrote: Interesting idea. The bigger problem is that apps who use the sequence name also would have problems running after the restore. Seems we need column.nextval() so you can increment the sequence without knowing the sequence name, just the column name. Of course, this related to this TODO item: * Have sequence dependency track use of DEFAULT sequences, seqname.nextval 200N spec proposes 'NEXT VALUE FOR sequence'. Tom will shoot me if I submit that though (VALUE as a keyword again). I suppose one could make it a variable, and confirm it's value is VALUE? Other than that it should be a fairly simple task. Anyway, once again we could extend to include: NEXT VALUE ON table(column)? An application that was simply interested in the next value of a table column could simply evaluate the default value -- which should be easily retrievable and more portable in most interfaces (jdbc, odbc, etc.). -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc -- End of PGP section, PGP failed! -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] [PATCHES] Non-colliding auto generated names
I think we should stick with the existing naming convention. The only actual problem that's been pointed out here is that an ALTER TABLE (or COLUMN) RENAME on a serial column doesn't update the sequence name to match. Seems to me we could fix that with less effort than any of these solutions would take, and it wouldn't break existing applications. Non-colliding? Otherwise, it'd be ludicrous to fail a table rename because a sequence with the new name already exists... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Non-colliding auto generated names
Christopher Kings-Lynne [EMAIL PROTECTED] writes: I think we should stick with the existing naming convention. ^^ Non-colliding? No; see above. Otherwise, it'd be ludicrous to fail a table rename because a sequence with the new name already exists... Why? We already rename the table's rowtype, ergo you can fail a table rename because there is a conflicting datatype name. I don't see anything much wrong with failing a table or column rename because there is a conflicting sequence name. The whole point here is to have a non-surprising mapping between the names of serial columns and the names of their associated sequences. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Non-colliding auto generated names
Added to TODO: o Have ALTER TABLE rename SERIAL sequences Seems we at least need this. Doesn't dependency tracking make this easy to do now? --- Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: I think we should stick with the existing naming convention. ^^ Non-colliding? No; see above. Otherwise, it'd be ludicrous to fail a table rename because a sequence with the new name already exists... Why? We already rename the table's rowtype, ergo you can fail a table rename because there is a conflicting datatype name. I don't see anything much wrong with failing a table or column rename because there is a conflicting sequence name. The whole point here is to have a non-surprising mapping between the names of serial columns and the names of their associated sequences. regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Non-colliding auto generated names
OK,
I have discovered a problem with my auto-naming patch. It's do to with
dumping serial columns with pg_dump, eg:
--
-- TOC entry 2 (OID 1004551)
-- Name: users_users; Type: TABLE; Schema: public; Owner: chriskl
--
CREATE TABLE users_users (
userid serial NOT NULL,
firstname character varying(255) NOT NULL,
lastname character varying(255) NOT NULL,
email character varying(255) NOT NULL
);
-- DATA DUMPED HERE
--
-- TOC entry 4 (OID 1004305)
-- Name: users_users_userid_seq; Type: SEQUENCE SET; Schema: public; Owner:
chriskl
--
SELECT pg_catalog.setval ('users_users_userid_seq', 126, true);
How do we fix this problem?? Perhaps instead of a hard-coded sequence
string, we can sub-SELECT for it...?
Chris
---(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
