[pgadmin-support] 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
Re: [pgadmin-support] Foreign key UI bug
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. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
Re: [pgadmin-support] Foreign key UI bug
On Sat, Nov 20, 2010 at 12:24, Guillaume Lelarge 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__ or something like that instead. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
Re: [pgadmin-support] Foreign key UI bug
Le 20/11/2010 12:27, Magnus Hagander a écrit : > On Sat, Nov 20, 2010 at 12:24, Guillaume Lelarge > 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__ 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. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
Re: [pgadmin-support] Foreign key UI bug
On Sat, Nov 20, 2010 at 12:37, Guillaume Lelarge wrote: > Le 20/11/2010 12:27, Magnus Hagander a écrit : >> On Sat, Nov 20, 2010 at 12:24, Guillaume Lelarge >> 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__ 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/ -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
Re: [pgadmin-support] Foreign key UI bug
Le 20/11/2010 12:39, Magnus Hagander a écrit : > On Sat, Nov 20, 2010 at 12:37, Guillaume Lelarge > wrote: >> Le 20/11/2010 12:27, Magnus Hagander a écrit : >>> On Sat, Nov 20, 2010 at 12:24, Guillaume Lelarge >>> 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__ 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 :) -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
Re: [pgadmin-support] Foreign key UI bug
Theoretically, you could have the same problem with the primary key -- there could be an index with that name already. 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. On Sat, Nov 20, 2010 at 7:49 AM, Guillaume Lelarge wrote: > > >>> 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__ 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. > > >
Re: [pgadmin-support] Foreign key UI bug
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). -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
Re: [pgadmin-support] Foreign key UI bug
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
>From 5b8bebb9bdd30ea48cb257c976198ce2b8b35851 Mon Sep 17 00:00:00 2001
From: Guillaume Lelarge
Date: Sat, 20 Nov 2010 18:43:19 +0100
Subject: [PATCH] Force typing the constraint name or the index name
Before this patch, the index name stays "fki_" without a constraint name. If
someone tries to create two foreign keys without typing a constraint name or
an index name, the second one will fail. So, we force typing either of them.
Fixes #289.
---
pgadmin/dlg/dlgForeignKey.cpp |3 +++
1 files changed, 3 insertions(+), 0 deletions(-)
diff --git a/pgadmin/dlg/dlgForeignKey.cpp b/pgadmin/dlg/dlgForeignKey.cpp
index ec7d970..8f77df5 100644
--- a/pgadmin/dlg/dlgForeignKey.cpp
+++ b/pgadmin/dlg/dlgForeignKey.cpp
@@ -82,6 +82,9 @@ dlgForeignKey::dlgForeignKey(pgaFactory *f, frmMain *frame, ctlListView *colList
wxString dlgForeignKey::DefaultIndexName(const wxString &name)
{
+if (name.IsEmpty())
+return wxEmptyString;
+
if (name.Left(3) == wxT("fk_"))
return wxT("fki_") + name.Mid(3);
else if (name.Left(3) == wxT("FK_"))
--
1.7.1
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Re: [pgadmin-support] Foreign key UI bug
> > 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
