Re: Foreign Key selection / no public schema

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. ;-)

Thanks,
Philipp