Re: Foreign Key selection / no public schema

Philipp Specht wrote:
> Dave Page wrote:
>> Philipp Specht wrote:
>>> Hi!
>>>
>>> I hope you can tell me how to avoid the following problem:
>>>
>>> I'm using pgadmin 1.6.2 on MacOSX (same problem with 1.6.1) connecting
>>> to a 8.2.0 database.
>>>
>>> I've renamed my public schema and created a new schema. There's no
>>> 'public' schema any more.
>>>
>>> Now pgadmin can't find the columns of a referenced table to populate the
>>> drop down selector.
>>>
>>> After activating debug logging I found that pgadmin tries to find the
>>> columns in the "public" schema.
>>>
>>> ###
>>> 2007-01-20 13:03:35 QUERY : Set query (xxx:5432): SELECT attname
>>> FROM pg_attribute att, pg_class cl, pg_namespace nsp
>>> WHERE attrelid=cl.oid AND relnamespace=nsp.oid
>>> AND nspname='public'
>>> AND relname='xxx'
>>> AND attnum > 0
>>>
>>> ORDER BY attnum
>>>
>>> ###
>> Hmm, I cannot reproduce this. After renaming the public schema, I find
>> existing constraints still reference the correct table (in the renamed
>> schema), and new constraints can see the table in the renamed schema
>> without an problems.
>>
>> Can you details the steps to reproduce the problem?
>
> Yeah, of course.
>
> I tried to trace the problem and find the smallest possible example to
> post to the list. Here is a bit for you to experiment...
>
> ###
> create database m14;
> \c m14
> create schema s;
> drop schema public;
> create table s.t1 (id serial not null);
> create table s.t2 (id integer);
> ###
>
> While trying to find a small example I found out more about the problem.
>
> After creating db "m14" you can do everything with pgadmin and the
> problem does not surface.
>
> Try right clicking on the table t2 -> properties -> constraints ->
> foreign key -> add. You can see the tables in the references drop down
> box are named "s.t1" and "s.t2", which is correct.
>
> Now, change your current user, by adding "search_path=s" to the
> variables section.
>
> Repeat above steps and you can see the problem:
> The tables are named "t1" and "t2" and when selected they do not
> populate the "referencing" drop down box in the columns tab any more.
>
> I don't really need to set the search_path while using pgadmin, I just
> did it to make my life easier while using the command line interface.
> At the moment I can work with two different accounts (now that I know
> what the problem is), so the problem is not really urgent. ;-)

Ah, got it - thanks. Fixed in SVN for 1.6.3.

Regards, Dave.