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




--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
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/pgadm...1/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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

hi!

http://archives.postgresql.org/pgadm...1/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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match