Обсуждение: Foreign Key selection / no public schema
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 nspWHEREattrelid=cl.oid AND relnamespace=nsp.oid AND nspname='public' AND relname='xxx' AND attnum > 0 ORDER BY attnum ### 'search_path' for the logged in user is set to the names of my new schemas. That's a bug? A missing feature? Or did I miss a configuration parameter? Thanks for your help, Philipp
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? Thanks, Dave.
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
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.