Re: [PATCHES] Non-colliding auto generated names

This patch makes all forms of autogenerated unique, primary key and sequence
names avoid collisions. (Well, as much as you can do without "locking" your
chosen name).

This addresses a long standing annoyance I experience whenever I rename a
table to table_old and then try to create a table with the same name. It
also addresses a concern raised at linux.conf.au.

I suggest that someone inspect the patch to determine if my "overloading" of
CreateIndexName is appropriate (for sequences, etc.)

All regression tests pass. The attached SQL script will run fine under the
patch, whereas before it would have failed miserably.

Chris

I thought folks wanted them to fail if they conflicted so that they
could know for sure how to derive such names definitively. Is that
accurate?

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> This patch makes all forms of autogenerated unique, primary key and sequence
> names avoid collisions. (Well, as much as you can do without "locking" your
> chosen name).
>
> This addresses a long standing annoyance I experience whenever I rename a
> table to table_old and then try to create a table with the same name. It
> also addresses a concern raised at linux.conf.au.
>
> I suggest that someone inspect the patch to determine if my "overloading" of
> CreateIndexName is appropriate (for sequences, etc.)
>
> All regression tests pass. The attached SQL script will run fine under the
> patch, whereas before it would have failed miserably.
>
> Chris
>

[ Attachment, skipping... ]

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Well, I can't imagine why anyone would rely on the auto name generation of a
database... I don't know anyone (3 out of 3) who wants name collision.
What on earth is the point of generating a colliding name? It's especially
confusing for newbies.

Chrsi

----- Original Message -----
From: "Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>
Cc: "Patches" <pgsql-patches(at)postgresql(dot)org>
Sent: Tuesday, February 18, 2003 1:18 PM
Subject: Re: [PATCHES] Non-colliding auto generated names

>
> I thought folks wanted them to fail if they conflicted so that they
> could know for sure how to derive such names definitively. Is that
> accurate?
>
> --------------------------------------------------------------------------
-
>
> Christopher Kings-Lynne wrote:
> > This patch makes all forms of autogenerated unique, primary key and
sequence
> > names avoid collisions. (Well, as much as you can do without "locking"
your
> > chosen name).
> >
> > This addresses a long standing annoyance I experience whenever I rename
a
> > table to table_old and then try to create a table with the same name.
It
> > also addresses a concern raised at linux.conf.au.
> >
> > I suggest that someone inspect the patch to determine if my
"overloading" of
> > CreateIndexName is appropriate (for sequences, etc.)
> >
> > All regression tests pass. The attached SQL script will run fine under
the
> > patch, whereas before it would have failed miserably.
> >
> > Chris
> >
>
> [ Attachment, skipping... ]
>
> [ Attachment, skipping... ]
>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania
19073
>

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I thought folks wanted them to fail if they conflicted so that they
> could know for sure how to derive such names definitively. Is that
> accurate?

It sort of bothers me that this patch would make it impossible to
predict with certainty the index names associated with a table.
But I haven't got a better idea...

regards, tom lane

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I thought folks wanted them to fail if they conflicted so that they
> > could know for sure how to derive such names definitively. Is that
> > accurate?
>
> It sort of bothers me that this patch would make it impossible to
> predict with certainty the index names associated with a table.
> But I haven't got a better idea...

OK, but why would you need to be able to do that? Also, it tells you in the
notice. And if your 'name prediction' code can't deal with collisions, then
it needs help. Also, what's stopping you specifying the name explicitly?

Maybe we could put it to pgsql-general?

Chris

"Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> writes:
> And if your 'name prediction' code can't deal with collisions, then
> it needs help.

THe point is that right now, if you know the CREATE TABLE command, then
you can compute exactly what index names it will assign --- no outside
knowledge about the previous state of the database is required.

I'm not sure how significant that really is to anyone, but it is
something that we'd be giving up.

And as I said, I don't have a better answer. I'm just expressing
vague unease, in hopes that it might spur someone to think of another
way. I'm willing to go with this way if we don't find another.

regards, tom lane

> THe point is that right now, if you know the CREATE TABLE command, then
> you can compute exactly what index names it will assign --- no outside
> knowledge about the previous state of the database is required.

Yes, but how is that at all helpful? I mean, you can know perfectly well
what the database might assign the name, but you still will not know if
it's going to conflict or not! You still have to try it to see if it
fails and if it fails, you need to choose a new name.

> I'm not sure how significant that really is to anyone, but it is
> something that we'd be giving up.

I can't think of any reason someone would be _relying_ in their app on the
name that the database might generate for them...we should ask -general.

But imagine what we're gaining...

I mean, imagine the poor newbie who goes to create a table with a SERIAL
column. It fails with a conflict. The error message is cryptic, going on
about 'sequences'. The newbie's going 'what's a sequence, i haven't said
anything about a sequence'. Then they try renaming a table to something
else and then create a table with the same name as the renamed table and
it fails for some mysterious reason!

If we wanted to stay consistent, then we should be renaming the constraint
indexes whenever the table is renamed!

Surely, surely anyone who relies on auto-generated names will just be
specifying the name explicitly anyway...?

> And as I said, I don't have a better answer. I'm just expressing
> vague unease, in hopes that it might spur someone to think of another
> way. I'm willing to go with this way if we don't find another.

There is a vague possibility that someone might be using it, but I think
it's very unlikely....

Chris

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> writes:
> If we wanted to stay consistent, then we should be renaming the constraint
> indexes whenever the table is renamed!

I thought about that, too, and I'd be in favor of it if it didn't have
problems of its own. (The big one being that if the new main-table
name is longer or shorter than the old, you would have to redo the
whole derived-name-generation process, or run into possible truncation
problems. So it leads to the same kind of loss of predictability I'm
griping about.)

regards, tom lane

Just to not let this die :)

> I thought about that, too, and I'd be in favor of it if it didn't have
> problems of its own. (The big one being that if the new main-table
> name is longer or shorter than the old, you would have to redo the
> whole derived-name-generation process, or run into possible truncation
> problems. So it leads to the same kind of loss of predictability I'm
> griping about.)

More arguments from me:

Why should this fail?:

User 1: CREATE TABLE a (test int4);
User 1: CREATE INDEX my_test_key ON a(test);

User 2: (blithely unaware of user1)
User 2: CREATE TABLE my (test int4, unique(test));
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'my_test_key' for
table 'my'
ERROR: relation named "my_test_key" already exists

It breaks the "principle of least surprise" rule. There's no way that that
should be a total failure condition! That's a confusing error for a newbie
especially...they then have to look up the manual and see that they need to
go:

CREATE TABLE my (test int4, constraint "my_key" unique(test));

Plus they have to understand all about constraints vs. indexes, names, and
all sort of internal shenanigans that they should be insulated from. Heck,
even I get annoyed that I can't rename a table and then rerun my creation
script without it failing!!!! (As did a guy at Linux.conf.au) It's a
pretty normal way of doing large schema changes. Renaming constraint names
is an even worse idea - no way should you be renaming things for the user!

What does it mean if someone does not specify a name of a constraint? They
are saying "I don't care what name you give it, you decide for me.". In
that case, how can they possibly rely on the generated name? They won't
even know if it's going to collide or not. Anyone who needs a certain name
always has the option of specifying it explicitly.

Chris

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)rbt(dot)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(at)rbt(dot)ca>
>
> PGP Key: http://www.rbt.ca/rbtpub.asc
-- End of PGP section, PGP failed!

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)rbt(dot)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(at)rbt(dot)ca>
>
> PGP Key: http://www.rbt.ca/rbtpub.asc
-- End of PGP section, PGP failed!

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Rod Taylor <rbt(at)rbt(dot)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(at)familyhealth(dot)com(dot)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(at)familyhealth(dot)com(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Just for confirmation, this patch will not be applied because it would
cause too many problems in accessing the sequence names after the
renaming. If we get auto-sequence naming, we can revisit this idea.

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> This patch makes all forms of autogenerated unique, primary key and sequence
> names avoid collisions. (Well, as much as you can do without "locking" your
> chosen name).
>
> This addresses a long standing annoyance I experience whenever I rename a
> table to table_old and then try to create a table with the same name. It
> also addresses a concern raised at linux.conf.au.
>
> I suggest that someone inspect the patch to determine if my "overloading" of
> CreateIndexName is appropriate (for sequences, etc.)
>
> All regression tests pass. The attached SQL script will run fine under the
> patch, whereas before it would have failed miserably.
>
> Chris
>

[ Attachment, skipping... ]

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073