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