Обсуждение: Primary schema name prepended to database objects
Hi there, I am not sure if this a general or jdbc question. (I am connecting to pgsql via jdbc.) I have postmaster (PostgreSQL) 7.4.5. It looks like the name of primary schema of the user (actually their username) is prepended to database objects so the "search_path" is not considered any more. (I got my stuff in the "public" schema, so I do not find them as user 'x'.) How can I tell the jdbc driver not to use schema-qualified object names? Thanks a lot Peter
On Tue, 1 Feb 2005, [ISO-8859-1] Kovács Péter wrote: > Hi there, > > I am not sure if this a general or jdbc question. (I am connecting to > pgsql via jdbc.) I have postmaster (PostgreSQL) 7.4.5. It looks like the > name of primary schema of the user (actually their username) is > prepended to database objects so the "search_path" is not considered any > more. By default the search_path has the schema of your username included in it with the special $user variable: jurka=# show search_path; search_path -------------- $user,public (1 row) > (I got my stuff in the "public" schema, so I do not find them as > user 'x'.) How can I tell the jdbc driver not to use schema-qualified > object names? > This should still search the public schema if an identically named object is not found in your user schema. It is unclear from your report exactly what your search_path setting is, what tables are where, and what query is not finding them correctly. If your search_path is not set correctly you may set this globally (postgresql.conf) or per user/database or per connection to match your needs. Kris Jurka
Kris, Thank you for your reply! The problem is that where I write in my code, say: "SELECT * FROM mytable" the backend log contains: "SELECT * FROM myusername.mytable" Someone along the way (I suspect the JDBC driver, but I am not sure) "qualifies" my database objects. Probably that is why the "public" schema is not searched for "mytable". Are there cases/configurations where the JDBC driver prepends the username to the object name? Is it possible to prevent this from happening? Thank you! Peter Kris Jurka wrote: > > On Tue, 1 Feb 2005, [ISO-8859-1] Kovács Péter wrote: > > >>Hi there, >> >>I am not sure if this a general or jdbc question. (I am connecting to >>pgsql via jdbc.) I have postmaster (PostgreSQL) 7.4.5. It looks like the >>name of primary schema of the user (actually their username) is >>prepended to database objects so the "search_path" is not considered any >>more. > > > By default the search_path has the schema of your username included in it > with the special $user variable: > > jurka=# show search_path; > search_path > -------------- > $user,public > (1 row) > > > >>(I got my stuff in the "public" schema, so I do not find them as >>user 'x'.) How can I tell the jdbc driver not to use schema-qualified >>object names? >> > > > This should still search the public schema if an identically named object > is not found in your user schema. It is unclear from your report exactly > what your search_path setting is, what tables are where, and what query is > not finding them correctly. If your search_path is not set correctly you > may set this globally (postgresql.conf) or per user/database or per > connection to match your needs. > > Kris Jurka >
On Wed, 2 Feb 2005, [ISO-8859-1] Kov�cs P�ter wrote: > The problem is that where I write in my code, say: > > "SELECT * FROM mytable" > > the backend log contains: > > "SELECT * FROM myusername.mytable" > > Someone along the way (I suspect the JDBC driver, but I am not sure) > "qualifies" my database objects. Probably that is why the "public" > schema is not searched for "mytable". > This is definitely not the JDBC driver doig this. The qualification by search_path I mentioned is done on the server and is done behind the scenes, meaning the qualified name would not show up in the backend log because it does not rewrite your original query. The fact that the backend log says mysername.mytable is a strong indication that it is in fact your software that is doing this because neither the driver, nor the server would do such a thing. Some kind of reproducible test case would convince me otherwise, but I don't expect to be receiving one. Kris Jurka
You are right. Silly me. The qualification was in _my_ code. I definitely need to slow down a bit :-) . Peter Kris Jurka wrote: > > On Wed, 2 Feb 2005, [ISO-8859-1] Kov�cs P�ter wrote: > > >>The problem is that where I write in my code, say: >> >>"SELECT * FROM mytable" >> >>the backend log contains: >> >>"SELECT * FROM myusername.mytable" >> >>Someone along the way (I suspect the JDBC driver, but I am not sure) >>"qualifies" my database objects. Probably that is why the "public" >>schema is not searched for "mytable". >> > > > This is definitely not the JDBC driver doig this. The qualification by > search_path I mentioned is done on the server and is done behind the > scenes, meaning the qualified name would not show up in the backend log > because it does not rewrite your original query. The fact that the > backend log says mysername.mytable is a strong indication that it is in > fact your software that is doing this because neither the driver, nor the > server would do such a thing. Some kind of reproducible test case would > convince me otherwise, but I don't expect to be receiving one. > > Kris Jurka >