Thread: Foreign key UI bug
I found a UI bug when creating a foreign key.
When creating a primary key, all you have to do is select the column in the Columns tab of the dialog. You don't have to fill out any other fields; pgadmin appears to take care of this for you.
It seems then that with a foreign key, all you should have to fill out is fill out the References field in the Properties tab, then in the Columns tab just select the relevant columns. If I do this and press OK, I get this error:
An error has occurred:ERROR: relation "fki_" already exists.
If I go back to the Properties tab and enter something into the Name field, then press OK, it works.
The problem: I don't need to enter anything in the Name field of primary key dialogs, but I do with foreign key dialogs. Can it be consistent where I don't need to enter a Name field of the foreign key, either.
Aren
Le 20/11/2010 04:50, Aren Cambre a écrit : > I found a UI bug when creating a foreign key. > > When creating a primary key, all you have to do is select the column in the > *Columns* tab of the dialog. You don't have to fill out any other fields; > pgadmin appears to take care of this for you. > > It seems then that with a foreign key, all you should have to fill out is > fill out the *References* field in the *Properties *tab, then in the * > Columns* tab just select the relevant columns. If I do this and press OK, I > get this error: > > *An error has occurred:* > *ERROR: relation "fki_" already exists.* > > * > * > If I go back to the *Properties* tab and enter something into the *Name* field, > then press *OK*, it works. > > The problem: I don't need to enter anything in the *Name* field of primary > key dialogs, but I do with foreign key dialogs. Can it be consistent where I > don't need to enter a *Name* field of the foreign key, either. > The issue is not really with the creation of the foreign key, but more with the automatically created index. If you don't put a name to the foreign key, pgAdmin has no idea on the name of index. The code says the index name would be "fki_" followed by the name of the foreign key. But, when pgadmin fires the SQL, it has no idea what the foreign key name will look like if you didn't specify it. So if you tries to create more than one foreign key, they end ud with index of the same name, which PostgreSQL won't allow (hence the "relation fki_ already exists). I see one main solution to it: disallow the automatic index creation if there is no name given to the foreign key. I'll write a patch for this, but if you see a better way to deal with this, tell us. -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
On Sat, Nov 20, 2010 at 12:24, Guillaume Lelarge <guillaume@lelarge.info> wrote: > Le 20/11/2010 04:50, Aren Cambre a écrit : >> I found a UI bug when creating a foreign key. >> >> When creating a primary key, all you have to do is select the column in the >> *Columns* tab of the dialog. You don't have to fill out any other fields; >> pgadmin appears to take care of this for you. >> >> It seems then that with a foreign key, all you should have to fill out is >> fill out the *References* field in the *Properties *tab, then in the * >> Columns* tab just select the relevant columns. If I do this and press OK, I >> get this error: >> >> *An error has occurred:* >> *ERROR: relation "fki_" already exists.* >> >> * >> * >> If I go back to the *Properties* tab and enter something into the *Name* field, >> then press *OK*, it works. >> >> The problem: I don't need to enter anything in the *Name* field of primary >> key dialogs, but I do with foreign key dialogs. Can it be consistent where I >> don't need to enter a *Name* field of the foreign key, either. >> > > The issue is not really with the creation of the foreign key, but more > with the automatically created index. If you don't put a name to the > foreign key, pgAdmin has no idea on the name of index. > > The code says the index name would be "fki_" followed by the name of the > foreign key. But, when pgadmin fires the SQL, it has no idea what the > foreign key name will look like if you didn't specify it. So if you > tries to create more than one foreign key, they end ud with index of the > same name, which PostgreSQL won't allow (hence the "relation fki_ > already exists). > > I see one main solution to it: disallow the automatic index creation if > there is no name given to the foreign key. I'll write a patch for this, > but if you see a better way to deal with this, tell us. Well, you could generate the name of the FOREIGN KEY in pgadmin as well, then you know what it'll be... Even when the user doesn't specify one. Another one would be to name the index fki_<tablename>_<columnname> or something like that instead. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Le 20/11/2010 12:27, Magnus Hagander a écrit : > On Sat, Nov 20, 2010 at 12:24, Guillaume Lelarge <guillaume@lelarge.info> wrote: >> Le 20/11/2010 04:50, Aren Cambre a écrit : >>> I found a UI bug when creating a foreign key. >>> >>> When creating a primary key, all you have to do is select the column in the >>> *Columns* tab of the dialog. You don't have to fill out any other fields; >>> pgadmin appears to take care of this for you. >>> >>> It seems then that with a foreign key, all you should have to fill out is >>> fill out the *References* field in the *Properties *tab, then in the * >>> Columns* tab just select the relevant columns. If I do this and press OK, I >>> get this error: >>> >>> *An error has occurred:* >>> *ERROR: relation "fki_" already exists.* >>> >>> * >>> * >>> If I go back to the *Properties* tab and enter something into the *Name* field, >>> then press *OK*, it works. >>> >>> The problem: I don't need to enter anything in the *Name* field of primary >>> key dialogs, but I do with foreign key dialogs. Can it be consistent where I >>> don't need to enter a *Name* field of the foreign key, either. >>> >> >> The issue is not really with the creation of the foreign key, but more >> with the automatically created index. If you don't put a name to the >> foreign key, pgAdmin has no idea on the name of index. >> >> The code says the index name would be "fki_" followed by the name of the >> foreign key. But, when pgadmin fires the SQL, it has no idea what the >> foreign key name will look like if you didn't specify it. So if you >> tries to create more than one foreign key, they end ud with index of the >> same name, which PostgreSQL won't allow (hence the "relation fki_ >> already exists). >> >> I see one main solution to it: disallow the automatic index creation if >> there is no name given to the foreign key. I'll write a patch for this, >> but if you see a better way to deal with this, tell us. > > Well, you could generate the name of the FOREIGN KEY in pgadmin as > well, then you know what it'll be... Even when the user doesn't > specify one. > > Another one would be to name the index fki_<tablename>_<columnname> or > something like that instead. > I thought about it, but rejected it on the idea that you can't be sure the index creation will work (the same index name can already exist). But, thinking more about it, the old algorithm wasn't better at it anyway. So, perhaps we can do this. And use our double sql textboxes to make sure the foreign key gets created even if the index creation fails. -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
On Sat, Nov 20, 2010 at 12:37, Guillaume Lelarge <guillaume@lelarge.info> wrote: > Le 20/11/2010 12:27, Magnus Hagander a écrit : >> On Sat, Nov 20, 2010 at 12:24, Guillaume Lelarge <guillaume@lelarge.info> wrote: >>> Le 20/11/2010 04:50, Aren Cambre a écrit : >>>> I found a UI bug when creating a foreign key. >>>> >>>> When creating a primary key, all you have to do is select the column in the >>>> *Columns* tab of the dialog. You don't have to fill out any other fields; >>>> pgadmin appears to take care of this for you. >>>> >>>> It seems then that with a foreign key, all you should have to fill out is >>>> fill out the *References* field in the *Properties *tab, then in the * >>>> Columns* tab just select the relevant columns. If I do this and press OK, I >>>> get this error: >>>> >>>> *An error has occurred:* >>>> *ERROR: relation "fki_" already exists.* >>>> >>>> * >>>> * >>>> If I go back to the *Properties* tab and enter something into the *Name* field, >>>> then press *OK*, it works. >>>> >>>> The problem: I don't need to enter anything in the *Name* field of primary >>>> key dialogs, but I do with foreign key dialogs. Can it be consistent where I >>>> don't need to enter a *Name* field of the foreign key, either. >>>> >>> >>> The issue is not really with the creation of the foreign key, but more >>> with the automatically created index. If you don't put a name to the >>> foreign key, pgAdmin has no idea on the name of index. >>> >>> The code says the index name would be "fki_" followed by the name of the >>> foreign key. But, when pgadmin fires the SQL, it has no idea what the >>> foreign key name will look like if you didn't specify it. So if you >>> tries to create more than one foreign key, they end ud with index of the >>> same name, which PostgreSQL won't allow (hence the "relation fki_ >>> already exists). >>> >>> I see one main solution to it: disallow the automatic index creation if >>> there is no name given to the foreign key. I'll write a patch for this, >>> but if you see a better way to deal with this, tell us. >> >> Well, you could generate the name of the FOREIGN KEY in pgadmin as >> well, then you know what it'll be... Even when the user doesn't >> specify one. >> >> Another one would be to name the index fki_<tablename>_<columnname> or >> something like that instead. >> > > I thought about it, but rejected it on the idea that you can't be sure > the index creation will work (the same index name can already exist). > But, thinking more about it, the old algorithm wasn't better at it anyway. But you can check for the index existance beforehand if you know what name you are going to be using. Do it in a transaction, and you should be fine, no? > So, perhaps we can do this. And use our double sql textboxes to make > sure the foreign key gets created even if the index creation fails. But do you *want* to create the foreign key if the index creation fails? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Le 20/11/2010 12:39, Magnus Hagander a écrit : > On Sat, Nov 20, 2010 at 12:37, Guillaume Lelarge <guillaume@lelarge.info> wrote: >> Le 20/11/2010 12:27, Magnus Hagander a écrit : >>> On Sat, Nov 20, 2010 at 12:24, Guillaume Lelarge <guillaume@lelarge.info> wrote: >>>> Le 20/11/2010 04:50, Aren Cambre a écrit : >>>>> I found a UI bug when creating a foreign key. >>>>> >>>>> When creating a primary key, all you have to do is select the column in the >>>>> *Columns* tab of the dialog. You don't have to fill out any other fields; >>>>> pgadmin appears to take care of this for you. >>>>> >>>>> It seems then that with a foreign key, all you should have to fill out is >>>>> fill out the *References* field in the *Properties *tab, then in the * >>>>> Columns* tab just select the relevant columns. If I do this and press OK, I >>>>> get this error: >>>>> >>>>> *An error has occurred:* >>>>> *ERROR: relation "fki_" already exists.* >>>>> >>>>> * >>>>> * >>>>> If I go back to the *Properties* tab and enter something into the *Name* field, >>>>> then press *OK*, it works. >>>>> >>>>> The problem: I don't need to enter anything in the *Name* field of primary >>>>> key dialogs, but I do with foreign key dialogs. Can it be consistent where I >>>>> don't need to enter a *Name* field of the foreign key, either. >>>>> >>>> >>>> The issue is not really with the creation of the foreign key, but more >>>> with the automatically created index. If you don't put a name to the >>>> foreign key, pgAdmin has no idea on the name of index. >>>> >>>> The code says the index name would be "fki_" followed by the name of the >>>> foreign key. But, when pgadmin fires the SQL, it has no idea what the >>>> foreign key name will look like if you didn't specify it. So if you >>>> tries to create more than one foreign key, they end ud with index of the >>>> same name, which PostgreSQL won't allow (hence the "relation fki_ >>>> already exists). >>>> >>>> I see one main solution to it: disallow the automatic index creation if >>>> there is no name given to the foreign key. I'll write a patch for this, >>>> but if you see a better way to deal with this, tell us. >>> >>> Well, you could generate the name of the FOREIGN KEY in pgadmin as >>> well, then you know what it'll be... Even when the user doesn't >>> specify one. >>> >>> Another one would be to name the index fki_<tablename>_<columnname> or >>> something like that instead. >>> >> >> I thought about it, but rejected it on the idea that you can't be sure >> the index creation will work (the same index name can already exist). >> But, thinking more about it, the old algorithm wasn't better at it anyway. > > But you can check for the index existance beforehand if you know what > name you are going to be using. Do it in a transaction, and you should > be fine, no? > Well, if I do this, it would mean I'll to check each time the user changes the index name textbox. Not pretty. But, actually, the real issue is that I'm afraid that, if we do this, we'll have to do it for every other info. If we check for the index name, we should also do so for the foreign key name. And all dialogs should check for a lot of things. Stuff we don't do right now. I'm not against it but it would be a major patch. >> So, perhaps we can do this. And use our double sql textboxes to make >> sure the foreign key gets created even if the index creation fails. > > But do you *want* to create the foreign key if the index creation fails? > Good question :) -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
Theoretically, you could have the same problem with the primary key -- there could be an index with that name already.<br/>But in practice it doesn't happen. You could generate a name for the foreign key based on similar pattern forthe pk<br /> and if it fails, then it falls on the user to provide a name. Seem like it would work 99% of the time. <br/><br /><div class="gmail_quote">On Sat, Nov 20, 2010 at 7:49 AM, Guillaume Lelarge <span dir="ltr"><<a href="mailto:guillaume@lelarge.info">guillaume@lelarge.info</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><br /> >>> Well,you could generate the name of the FOREIGN KEY in pgadmin as<br /> >>> well, then you know what it'll be...Even when the user doesn't specify one.<br /> >>><br /> >>> Another one would be to name the indexfki_<tablename>_<columnname> or<br /> >>> something like that instead.<br /> >>><br />>><br /> >> I thought about it, but rejected it on the idea that you can't be sure<br /> >> the indexcreation will work (the same index name can already exist).<br /> >> But, thinking more about it, the old algorithmwasn't better at it anyway.<br /> ><br /></blockquote></div>
Le 20/11/2010 15:15, Michael Shapiro a écrit : > Theoretically, you could have the same problem with the primary key -- there > could be an index with that name already. If it happens, it would be PostgreSQL fault, not pgAdmin. The name of the constraint and the name of the index, in a primary key and in a unique contraint, are determined by PostgreSQL, not pgAdmin. On the contrario, the name of the index of a foreign key is determined by pgAdmin because this is not a PostgreSQL feature. > But in practice it doesn't happen. You could generate a name for the foreign > key based on similar pattern for the pk > and if it fails, then it falls on the user to provide a name. Seem like it > would work 99% of the time. > I guess Magnus's idea is the good one (fk_tablename_columnname). -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
Le 20/11/2010 16:09, Guillaume Lelarge a écrit : > Le 20/11/2010 15:15, Michael Shapiro a écrit : >> Theoretically, you could have the same problem with the primary key -- there >> could be an index with that name already. > > If it happens, it would be PostgreSQL fault, not pgAdmin. The name of > the constraint and the name of the index, in a primary key and in a > unique contraint, are determined by PostgreSQL, not pgAdmin. On the > contrario, the name of the index of a foreign key is determined by > pgAdmin because this is not a PostgreSQL feature. > >> But in practice it doesn't happen. You could generate a name for the foreign >> key based on similar pattern for the pk >> and if it fails, then it falls on the user to provide a name. Seem like it >> would work 99% of the time. >> > > I guess Magnus's idea is the good one (fk_tablename_columnname). > The issue in this idea is that you can have more than one column in a foreignkey. Yes, I know we can put all of them till we reach 63 characters. But it feels quite a burden. The simple patch attached forces one to type either the constraint name or the index name. Which seems enough for me. Any new objections? :) -- Guillaume http://www.postgresql.fr http://dalibo.com
The simple patch attached forces one to type either the constraint name
or the index name. Which seems enough for me.
That sounds like a good resolution.
Aren
Le 21/11/2010 00:12, Aren Cambre a écrit : >> >> The simple patch attached forces one to type either the constraint name >> or the index name. Which seems enough for me. >> > > That sounds like a good resolution. > Thanks. Commited. Should be in 1.12.2. -- Guillaumehttp://www.postgresql.frhttp://dalibo.com