Thread: Re: [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
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 pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
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 <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
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 <rbt@rbt.ca>
>
> PGP Key: http://www.rbt.ca/rbtpub.asc
-- End of PGP section, PGP failed!
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
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 <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
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 <rbt@rbt.ca> > > PGP Key: http://www.rbt.ca/rbtpub.asc -- End of PGP section, PGP failed! -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Rod Taylor <rbt@rbt.ca> 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
> 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. It does, but if Rod's domain / ALTER SEQUENCE idea goes ahead, this patch becomes useful again. We will still get complains because the problem occurs when you _rename_ a table and then re-create it. Chris
> 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
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> 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
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" <chriskl@familyhealth.com.au> 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 pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073