Thread: bug with fkey-dialog in tables belonging to schemes

bug with fkey-dialog in tables belonging to schemes

From
Harald Armin Massa
Date:
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  

Re: bug with fkey-dialog in tables belonging to

From
Miha Radej
Date:
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


Re: bug with fkey-dialog in tables belonging to

From
Miha Radej
Date:
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


Re: bug with fkey-dialog in tables belonging to

From
Andreas Pflug
Date:
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