Re: Search path in connection string

Hello Julien,

As normally you would always use a connection pool (like BoneCP or
c3p0), you can easily configure an InitSQL property to initialize your
connection as needed.

Note that JDBC driver for now does not support search_path at all, as the
OID cache lookup is not taking it into an account. So prepare for some
crazy problems when for example returning a type that exists in several
schemas with the same name.

With best regards,

-- Valentine

On Tue, Jul 31, 2012 at 2:41 AM, David Johnston <polobo(at)yahoo(dot)com> wrote:

> *From:* pgsql-jdbc-owner(at)postgresql(dot)org [mailto:
> pgsql-jdbc-owner(at)postgresql(dot)org] *On Behalf Of *Julien Demoor
> *Sent:* Monday, July 30, 2012 8:56 AM
> *To:* pgsql-jdbc(at)postgresql(dot)org
> *Subject:* [JDBC] Search path in connection string****
>
> ** **
>
> There have been a number of messages on this list suggesting that the
> Postgres JDBC driver should support setting the search path as part of the
> connection string.****
>
> ** **
>
> Can this be considered?****
>
> ** **
>
> I have a use case where there doesn't appear to be any good alternatives:
> using BIRT, with multiple clients sharing a Postgres database with one
> schema each, one cannot just execute a SET SCHEMA statement (UPDATE
> pg_settings ... is not possible either) and qualifying object names is also
> not always possible (say, if there are stored procedures involved that use
> unqualified object names).****
>
> ** **
>
> Besides being useful in this case and surely others, this feature makes
> sense: schemas can be used to offer separation of database objects very
> similar to that offered by having multiple databases, and the database
> itself is part of the connection string.****
>
> ** **
>
> The patch proposed in
> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-td2174512.htmlis still applicable.
> ****
>
> ** **
>
> Regards,****
>
> Julien****
>
> ** **
>
> ** **
>
> You can customize the connection string to accomplish this. You vary the
> “user” that is connecting and configure a search_path specific to that user.
> ****
>
> ** **
>
> ALTER ROLE custom_role SET search_path = …****
>
> ** **
>
> David J.****
>
> ** **
>