Thread: 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
Attachment
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@candle.pha.pa.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@candle.pha.pa.us> To: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> Cc: "Patches" <pgsql-patches@postgresql.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@candle.pha.pa.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@candle.pha.pa.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@candle.pha.pa.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@familyhealth.com.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@familyhealth.com.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
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@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073