Обсуждение: bug with fkey-dialog in tables belonging to schemes
I discovered a subtle bug within the dialog for foreign key creation:<br /><br />Be there a database "testdb" structuredlike:<br clear="all" /><br />CREATE SCHEMA nb1;<br /><br />CREATE TABLE nb1.hauptbaer (<br /> id_baer serialNOT NULL, <br /> fischfeld text<br />);<br /><br />ALTER TABLE nb1.hauptbaer<br /> ADD CONSTRAINT hauptbaer_pkeyPRIMARY KEY(id_baer);<br /><br />CREATE TABLE nb1.nebenbaer (<br /> id_nb serial NOT NULL,<br /> redetext,<br /> id_baer integer <br />);<br /><br />ALTER TABLE ONLY nb1.nebenbaer<br /> ADD CONSTRAINT nebenbaer_pkeyPRIMARY KEY (id_nb);<br /><br />that is, within schema "nb1" there are two tables "nb1.hauptbaer" and "nb1nebenbaer". <br /><br />The database, the schema, everthing is owned by a user named nb1.<br /><br />Now create a connectionto the database testdb as user nb1.<br /><br />When going to "nebenbaer" and trying to add a foreign key constraintto id_baer of hauptbaer... <br /><br />you can select hauptbaer from the list of tables, but there is no schemeprefix. On the dialog-tab where you need to select the columns for the fkey relationship, no column from hauptbaeris shown.<br /><br />My guess it's that the nb1-scheme is dropped out because it's same as username. <br /><br />IfI connect as postgres or any other user, creating that restriction works fine. I can select columns and all.<br /><br/>I hope I explained understandable; if I can do anything more to make it reproducable, please let me know. <br /><br/>Harald<br /><br /><br /><br /><br />-- <br />GHUM Harald Massa<br />persuadere et programmare<br />Harald Armin Massa<br/>Reinsburgstraße 202b<br />70197 Stuttgart<br />0173/9409607
hi! i seem to be having problems with sending email today so if anyone receives this email twice... i apologize. ****************** http://archives.postgresql.org/pgadmin-support/2005-11/msg00102.php i've also posted what seems to be the same bug about a month ago. i can confirm that, like harald said, it occured when working on a schema with the same name as the name of the user connecting to the database. and in a follow-up to the above post i've posted the query pgadmin makes and what it should be (imo) to work properly. regards, M Harald Armin Massa wrote: > I discovered a subtle bug within the dialog for foreign key creation: > > Be there a database "testdb" structured like: > > CREATE SCHEMA nb1; > > CREATE TABLE nb1.hauptbaer ( > id_baer serial NOT NULL, > fischfeld text > ); > > ALTER TABLE nb1.hauptbaer > ADD CONSTRAINT hauptbaer_pkey PRIMARY KEY(id_baer); > > CREATE TABLE nb1.nebenbaer ( > id_nb serial NOT NULL, > rede text, > id_baer integer > ); > > ALTER TABLE ONLY nb1.nebenbaer > ADD CONSTRAINT nebenbaer_pkey PRIMARY KEY (id_nb); > > that is, within schema "nb1" there are two tables "nb1.hauptbaer" and > "nb1 nebenbaer". > > The database, the schema, everthing is owned by a user named nb1. > > Now create a connection to the database testdb as user nb1. > > When going to "nebenbaer" and trying to add a foreign key constraint to > id_baer of hauptbaer... > > you can select hauptbaer from the list of tables, but there is no scheme > prefix. On the dialog-tab where you need to select the columns for the > fkey relationship, no column from hauptbaer is shown. > > My guess it's that the nb1-scheme is dropped out because it's same as > username. > > If I connect as postgres or any other user, creating that restriction > works fine. I can select columns and all. > > I hope I explained understandable; if I can do anything more to make it > reproducable, please let me know. > > Harald
hi! http://archives.postgresql.org/pgadmin-support/2005-11/msg00102.php i've reported what seems to be the same bug about a month ago. i can confirm that, like harald said, it occured when working on a schema with the same name as the user connecting to the database. and in a follow-up to the above post i've posted the query pgadmin makes and what it should be (imo) to work properly. regards, M Harald Armin Massa wrote: > I discovered a subtle bug within the dialog for foreign key creation: > > Be there a database "testdb" structured like: > > CREATE SCHEMA nb1; > > CREATE TABLE nb1.hauptbaer ( > id_baer serial NOT NULL, > fischfeld text > ); > > ALTER TABLE nb1.hauptbaer > ADD CONSTRAINT hauptbaer_pkey PRIMARY KEY(id_baer); > > CREATE TABLE nb1.nebenbaer ( > id_nb serial NOT NULL, > rede text, > id_baer integer > ); > > ALTER TABLE ONLY nb1.nebenbaer > ADD CONSTRAINT nebenbaer_pkey PRIMARY KEY (id_nb); > > that is, within schema "nb1" there are two tables "nb1.hauptbaer" and > "nb1 nebenbaer". > > The database, the schema, everthing is owned by a user named nb1. > > Now create a connection to the database testdb as user nb1. > > When going to "nebenbaer" and trying to add a foreign key constraint to > id_baer of hauptbaer... > > you can select hauptbaer from the list of tables, but there is no scheme > prefix. On the dialog-tab where you need to select the columns for the > fkey relationship, no column from hauptbaer is shown. > > My guess it's that the nb1-scheme is dropped out because it's same as > username. > > If I connect as postgres or any other user, creating that restriction > works fine. I can select columns and all. > > I hope I explained understandable; if I can do anything more to make it > reproducable, please let me know. > > Harald
Harald Armin Massa wrote: > I discovered a subtle bug within the dialog for foreign key creation: > > Be there a database "testdb" structured like: > > CREATE SCHEMA nb1; > > CREATE TABLE nb1.hauptbaer ( > id_baer serial NOT NULL, > fischfeld text > ); > > ALTER TABLE nb1.hauptbaer > ADD CONSTRAINT hauptbaer_pkey PRIMARY KEY(id_baer); > > CREATE TABLE nb1.nebenbaer ( > id_nb serial NOT NULL, > rede text, > id_baer integer > ); > > ALTER TABLE ONLY nb1.nebenbaer > ADD CONSTRAINT nebenbaer_pkey PRIMARY KEY (id_nb); > > that is, within schema "nb1" there are two tables "nb1.hauptbaer" and > "nb1 nebenbaer". > > The database, the schema, everthing is owned by a user named nb1. > > Now create a connection to the database testdb as user nb1. > > When going to "nebenbaer" and trying to add a foreign key constraint to > id_baer of hauptbaer... > > you can select hauptbaer from the list of tables, but there is no scheme > prefix. On the dialog-tab where you need to select the columns for the > fkey relationship, no column from hauptbaer is shown. I did exactly this, replacing nb1 by user/db/schema postgres, and everything worked as expected. My search_path is the default $user,public. Actually, suppressing the schema name here wouldn't do any harm, because the search_path will automatically add the username here. Regards, Andreas