Thread: Primary schema name prepended to database objects

Primary schema name prepended to database objects

From
Kovács Péter
Date:
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

Re: Primary schema name prepended to database objects

From
Kris Jurka
Date:

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

Re: Primary schema name prepended to database objects

From
Kovács Péter
Date:
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
>


Re: Primary schema name prepended to database objects

From
Kris Jurka
Date:

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

Re: Primary schema name prepended to database objects

From
Kovács Péter
Date:
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
>