Patch to allow setting schema/search_path in the connectionURL

Many years ago, Andreas Joseph Krogh submitted a patch to allow the
setting of the default schema in a JDBC connection string:

http://archives.postgresql.org/pgsql-jdbc/2002-12/msg00187.php

It was rejected with some objections:
http://archives.postgresql.org/pgsql-jdbc/2002-12/msg00189.php

I've made a modern version of this patch against the current CVS head
that hopefully addresses the earlier objections on this list. In this
version, however, the parameter is called 'searchpath' instead of 'schema'

I find this functionality useful for a data reporting tool like BIRT:
http://www.eclipse.org/birt/phoenix/
that expects you to provide the JDBC connection parameters but where the
tool will build appropriate SQL queries for report generation - based
on your manipulation of the report designer interface. Our DBA likes to
use postgresql schemas for namespace management. Setting a searchpath
keyword in the JDBC connection parameters allows the BIRT reports to be
easily portable for use with differently-named schemas.

E.g.:

jdbc:postgresql://localhost:5432/mydatabase?searchpath=myschema

Incidentally, I noticed that the npgsql developers added similar
functionality to their Posgresql driver in November of last year:

http://archives.postgresql.org/pgsql-committers/2007-11/msg00506.php

Regards,

Scott Langley
--
Systems Analyst/Programmer
Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
Fred Hutchinson Cancer Research Center
Seattle, Washington
slangley(at)scharp(dot)org

Scott Langley wrote:
> Many years ago, Andreas Joseph Krogh submitted a patch to allow the
> setting of the default schema in a JDBC connection string:
>
> http://archives.postgresql.org/pgsql-jdbc/2002-12/msg00187.php
>
> It was rejected with some objections:
> http://archives.postgresql.org/pgsql-jdbc/2002-12/msg00189.php
>
> I've made a modern version of this patch against the current CVS head
> that hopefully addresses the earlier objections on this list. In this
> version, however, the parameter is called 'searchpath' instead of 'schema'

I think it would be more useful to provide a way to set any GUC variable
in the connection string, not just search_path.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Wed, 9 Jul 2008, Heikki Linnakangas wrote:

> I think it would be more useful to provide a way to set any GUC variable in
> the connection string, not just search_path.
>

In previous discussions:

http://archives.postgresql.org/pgsql-jdbc/2004-02/thrd2.php#00022

One significant roadblock was how to handle the Datasource implementation
which needs to know all the available GUC parameters at compile time:

http://archives.postgresql.org/pgsql-jdbc/2004-02/msg00037.php

I'm not particularly excited about enumerating them all and the suggested
alternative methods of passing these values in Properties, String[][]
don't seem terribly appealing

Kris Jurka

Hi,

Did this patch ever make it in? Is there a way to specify the default schema
before establishing a connection?

Thanks,
Gili

Kris Jurka wrote
> On Wed, 9 Jul 2008, Heikki Linnakangas wrote:
>
>> I think it would be more useful to provide a way to set any GUC variable
>> in
>> the connection string, not just search_path.
>>
>
> In previous discussions:
>
> http://archives.postgresql.org/pgsql-jdbc/2004-02/thrd2.php#00022
>
> One significant roadblock was how to handle the Datasource implementation
> which needs to know all the available GUC parameters at compile time:
>
> http://archives.postgresql.org/pgsql-jdbc/2004-02/msg00037.php
>
> I'm not particularly excited about enumerating them all and the suggested
> alternative methods of passing these values in Properties, String[][]
> don't seem terribly appealing
>
> Kris Jurka
>
> --
> Sent via pgsql-jdbc mailing list (

> pgsql-jdbc@

> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819366.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

I don't think it did, I'm not opposed to this. I think there may even be an
outstanding pull request for it.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 17 September 2014 14:04, cowwoc <cowwoc(at)bbs(dot)darktech(dot)org> wrote:

> Hi,
>
> Did this patch ever make it in? Is there a way to specify the default
> schema
> before establishing a connection?
>
> Thanks,
> Gili
>
>
> Kris Jurka wrote
> > On Wed, 9 Jul 2008, Heikki Linnakangas wrote:
> >
> >> I think it would be more useful to provide a way to set any GUC variable
> >> in
> >> the connection string, not just search_path.
> >>
> >
> > In previous discussions:
> >
> > http://archives.postgresql.org/pgsql-jdbc/2004-02/thrd2.php#00022
> >
> > One significant roadblock was how to handle the Datasource implementation
> > which needs to know all the available GUC parameters at compile time:
> >
> > http://archives.postgresql.org/pgsql-jdbc/2004-02/msg00037.php
> >
> > I'm not particularly excited about enumerating them all and the suggested
> > alternative methods of passing these values in Properties, String[][]
> > don't seem terribly appealing
> >
> > Kris Jurka
> >
> > --
> > Sent via pgsql-jdbc mailing list (
>
> > pgsql-jdbc@
>
> > )
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819366.html
> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>

I too have a similar requirement to specify the schema.

I see in various discussions that many people have tried to fix this
problem. It appears there is no resolution yet. It is sad that the JDBC
driver community does not seem to pay any attention to the end-user needs.
Hello.. PLEASE WAKE UP! We are in a different era!

Does anybody know of a solution on how to designate a schema while
establishing the connection? I would highly appreciate it!!!

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819543.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On 9/18/2014 12:47 PM, stagirus wrote:
> Does anybody know of a solution on how to designate a schema while
> establishing the connection? I would highly appreciate it!!!
>

we use the rolename as the default schema. works for us.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

I added a hook that runs immediately before acquiring a Connection. It
sets the schema using an SQL update command, and forwards the connection
on to the rest of the application. Not ideal but it works.

Gili

On 18/09/2014 3:47 PM, stagirus [via PostgreSQL] wrote:
> I too have a similar requirement to specify the schema.
>
> I see in various discussions that many people have tried to fix this
> problem. It appears there is no resolution yet. It is sad that the
> JDBC driver community does not seem to pay any attention to the
> end-user needs. Hello.. PLEASE WAKE UP! We are in a different era!
>
> Does anybody know of a solution on how to designate a schema while
> establishing the connection? I would highly appreciate it!!!

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819547.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

What era is this exactly ? 3 people are looking for this particular
feature. This is not part of the JDBC spec. Perhaps if it is that important
someone can provide some code to implement this ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 18 September 2014 15:47, stagirus <mamasa(at)stagirus(dot)com> wrote:

> I too have a similar requirement to specify the schema.
>
> I see in various discussions that many people have tried to fix this
> problem. It appears there is no resolution yet. It is sad that the JDBC
> driver community does not seem to pay any attention to the end-user needs.
> Hello.. PLEASE WAKE UP! We are in a different era!
>
> Does anybody know of a solution on how to designate a schema while
> establishing the connection? I would highly appreciate it!!!
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819543.html
> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>

Hi,

This feature is already available as it has been implemented and merged
earlier in the year using a property named "currentSchema" of the JDBC URL.

It's currenly only available in the master branch though.

btw, the related issue #155 can be closed now.

Alexis Meneses

2014-09-18 22:17 GMT+02:00 Dave Cramer <pg(at)fastcrypt(dot)com>:

> What era is this exactly ? 3 people are looking for this particular
> feature. This is not part of the JDBC spec. Perhaps if it is that important
> someone can provide some code to implement this ?
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
> On 18 September 2014 15:47, stagirus <mamasa(at)stagirus(dot)com> wrote:
>
>> I too have a similar requirement to specify the schema.
>>
>> I see in various discussions that many people have tried to fix this
>> problem. It appears there is no resolution yet. It is sad that the JDBC
>> driver community does not seem to pay any attention to the end-user needs.
>> Hello.. PLEASE WAKE UP! We are in a different era!
>>
>> Does anybody know of a solution on how to designate a schema while
>> establishing the connection? I would highly appreciate it!!!
>>
>>
>>
>>
>> --
>> View this message in context:
>> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819543.html
>> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>
>

Alexis,

Is there an issue keeping track of the fact the documentation needs to
be updated?

Thanks,
Gili

On 19/09/2014 11:10 AM, Alexis Meneses [via PostgreSQL] wrote:
> Hi,
>
> This feature is already available as it has been implemented and
> merged earlier in the year using a property named "currentSchema" of
> the JDBC URL.
>
> It's currenly only available in the master branch though.
>
> btw, the related issue #155 can be closed now.
>
> Alexis Meneses
>
> 2014-09-18 22:17 GMT+02:00 Dave Cramer <[hidden email]
> </user/SendEmail.jtp?type=node&node=5819674&i=0>>:
>
> What era is this exactly ? 3 people are looking for this
> particular feature. This is not part of the JDBC spec. Perhaps if
> it is that important someone can provide some code to implement this ?
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
> On 18 September 2014 15:47, stagirus <[hidden email]
> </user/SendEmail.jtp?type=node&node=5819674&i=1>> wrote:
>
> I too have a similar requirement to specify the schema.
>
> I see in various discussions that many people have tried to
> fix this
> problem. It appears there is no resolution yet. It is sad that
> the JDBC
> driver community does not seem to pay any attention to the
> end-user needs.
> Hello.. PLEASE WAKE UP! We are in a different era!
>
> Does anybody know of a solution on how to designate a schema while
> establishing the connection? I would highly appreciate it!!!
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819543.html
> Sent from the PostgreSQL - jdbc mailing list archive at
> Nabble.com.
>
>
> --
> Sent via pgsql-jdbc mailing list ([hidden email]
> </user/SendEmail.jtp?type=node&node=5819674&i=2>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
>
>
>
> ------------------------------------------------------------------------
> If you reply to this email, your message will be added to the
> discussion below:
> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819674.html
>
> To unsubscribe from Patch to allow setting schema/search_path in the
> connectionURL, click here
> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=2174512&code=Y293d29jQGJicy5kYXJrdGVjaC5vcmd8MjE3NDUxMnwxNTc0MzIxMjQ3>.
> NAML
> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819704.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

The docbook is up-to-date regarding this connection parameter. It's also
reflected by the internal getPropertyInfo. Do you think about something
else?

Alexis Meneses

2014-09-19 18:55 GMT+02:00 cowwoc <cowwoc(at)bbs(dot)darktech(dot)org>:

> Alexis,
>
> Is there an issue keeping track of the fact the documentation needs to be
> updated?
>
> Thanks,
> Gili
>
> On 19/09/2014 11:10 AM, Alexis Meneses [via PostgreSQL] wrote:
>
> Hi,
>
> This feature is already available as it has been implemented and merged
> earlier in the year using a property named "currentSchema" of the JDBC URL.
>
> It's currenly only available in the master branch though.
>
> btw, the related issue #155 can be closed now.
>
> Alexis Meneses
>
> 2014-09-18 22:17 GMT+02:00 Dave Cramer <[hidden email]
> <http://user/SendEmail.jtp?type=node&node=5819674&i=0>>:
>
>> What era is this exactly ? 3 people are looking for this particular
>> feature. This is not part of the JDBC spec. Perhaps if it is that important
>> someone can provide some code to implement this ?
>>
>> Dave Cramer
>>
>> dave.cramer(at)credativ(dot)ca
>> http://www.credativ.ca
>>
>> On 18 September 2014 15:47, stagirus <[hidden email]
>> <http://user/SendEmail.jtp?type=node&node=5819674&i=1>> wrote:
>>
>>> I too have a similar requirement to specify the schema.
>>>
>>> I see in various discussions that many people have tried to fix this
>>> problem. It appears there is no resolution yet. It is sad that the JDBC
>>> driver community does not seem to pay any attention to the end-user
>>> needs.
>>> Hello.. PLEASE WAKE UP! We are in a different era!
>>>
>>> Does anybody know of a solution on how to designate a schema while
>>> establishing the connection? I would highly appreciate it!!!
>>>
>>>
>>>
>>>
>>> --
>>> View this message in context:
>>> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819543.html
>>> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>>>
>>>
>>> --
>>> Sent via pgsql-jdbc mailing list ([hidden email]
>>> <http://user/SendEmail.jtp?type=node&node=5819674&i=2>)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>>
>>
>>
>
>
> ------------------------------
> If you reply to this email, your message will be added to the discussion
> below:
>
> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819674.html
> To unsubscribe from Patch to allow setting schema/search_path in the
> connectionURL, click here.
> NAML
> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>
>
>
> ------------------------------
> View this message in context: Re: Patch to allow setting
> schema/search_path in the connectionURL
> <http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819704.html>
> Sent from the PostgreSQL - jdbc mailing list archive
> <http://postgresql.1045698.n5.nabble.com/PostgreSQL-jdbc-f2168246.html>
> at Nabble.com.
>

I am expecting to see it here:
http://jdbc.postgresql.org/documentation/head/connect.html

Gili

On 20/09/2014 3:48 AM, Alexis Meneses wrote:
> The docbook is up-to-date regarding this connection parameter. It's
> also reflected by the internal getPropertyInfo. Do you think about
> something else?
>
> Alexis Meneses
>
> 2014-09-19 18:55 GMT+02:00 cowwoc <cowwoc(at)bbs(dot)darktech(dot)org
> <mailto:cowwoc(at)bbs(dot)darktech(dot)org>>:
>
> Alexis,
>
> Is there an issue keeping track of the fact the documentation
> needs to be updated?
>
> Thanks,
> Gili
>
> On 19/09/2014 11:10 AM, Alexis Meneses [via PostgreSQL] wrote:
>> Hi,
>>
>> This feature is already available as it has been implemented and
>> merged earlier in the year using a property named "currentSchema"
>> of the JDBC URL.
>>
>> It's currenly only available in the master branch though.
>>
>> btw, the related issue #155 can be closed now.
>>
>> Alexis Meneses
>>
>> 2014-09-18 22:17 GMT+02:00 Dave Cramer <[hidden email]
>> <http://user/SendEmail.jtp?type=node&node=5819674&i=0>>:
>>
>> What era is this exactly ? 3 people are looking for this
>> particular feature. This is not part of the JDBC spec.
>> Perhaps if it is that important someone can provide some code
>> to implement this ?
>>
>> Dave Cramer
>>
>> dave.cramer(at)credativ(dot)ca
>> http://www.credativ.ca
>>
>> On 18 September 2014 15:47, stagirus <[hidden email]
>> <http://user/SendEmail.jtp?type=node&node=5819674&i=1>> wrote:
>>
>> I too have a similar requirement to specify the schema.
>>
>> I see in various discussions that many people have tried
>> to fix this
>> problem. It appears there is no resolution yet. It is sad
>> that the JDBC
>> driver community does not seem to pay any attention to
>> the end-user needs.
>> Hello.. PLEASE WAKE UP! We are in a different era!
>>
>> Does anybody know of a solution on how to designate a
>> schema while
>> establishing the connection? I would highly appreciate it!!!
>>
>>
>>
>>
>> --
>> View this message in context:
>> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819543.html
>> Sent from the PostgreSQL - jdbc mailing list archive at
>> Nabble.com.
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list ([hidden email]
>> <http://user/SendEmail.jtp?type=node&node=5819674&i=2>)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>>
>>
>>
>>
>> ------------------------------------------------------------------------
>> If you reply to this email, your message will be added to the
>> discussion below:
>> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819674.html
>>
>> To unsubscribe from Patch to allow setting schema/search_path in
>> the connectionURL, click here.
>> NAML
>> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>>
>
>
> ------------------------------------------------------------------------
> View this message in context: Re: Patch to allow setting
> schema/search_path in the connectionURL
> <http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819704.html>
> Sent from the PostgreSQL - jdbc mailing list archive
> <http://postgresql.1045698.n5.nabble.com/PostgreSQL-jdbc-f2168246.html>
> at Nabble.com.
>
>

On Thu, Sep 18, 2014 at 4:59 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> On 9/18/2014 12:47 PM, stagirus wrote:
>
>> Does anybody know of a solution on how to designate a schema while
>> establishing the connection? I would highly appreciate it!!!
>>
>>
>
> we use the rolename as the default schema. works for us.

If someone has this requirement, it *probably* uses different users for
different schema configurations. In such case you don't even need to match
user name and schema name, you can simple configure search_path per user as:

ALTER ROLE username SET search_path TO schema1, schema2, ...;

For me, setting search_path from URL connection is a mistake, as it is not
one of configurations accept on connection creating at the protocol (or is
it?), like it is for client_encoding, application_name and others. If JDBC
devs are going to change settings on URL then you probably should start
thinking on more generic, like setting any GUC (like work_mem, etc.).

Disclaimer: I'm not a JDBC community active user, that is my "opinion", and
you can take or not... ;-)

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

I actually want the same user across all schemas (unit tests where each
test runs against a different schema in order to isolate the tests from
each other).

Can someone please confirm whether set search_path can be set on a per
connection basis?

Thanks,
Gili
On Sep 21, 2014 10:50 AM, "Matheus de Oliveira [via PostgreSQL]" <
ml-node+s1045698n5819845h63(at)n5(dot)nabble(dot)com> wrote:

>
> On Thu, Sep 18, 2014 at 4:59 PM, John R Pierce <[hidden email]
> <http://user/SendEmail.jtp?type=node&node=5819845&i=0>> wrote:
>
>> On 9/18/2014 12:47 PM, stagirus wrote:
>>
>>> Does anybody know of a solution on how to designate a schema while
>>> establishing the connection? I would highly appreciate it!!!
>>>
>>>
>>
>> we use the rolename as the default schema. works for us.
>
>
> If someone has this requirement, it *probably* uses different users for
> different schema configurations. In such case you don't even need to match
> user name and schema name, you can simple configure search_path per user as:
>
> ALTER ROLE username SET search_path TO schema1, schema2, ...;
>
> For me, setting search_path from URL connection is a mistake, as it is not
> one of configurations accept on connection creating at the protocol (or is
> it?), like it is for client_encoding, application_name and others. If JDBC
> devs are going to change settings on URL then you probably should start
> thinking on more generic, like setting any GUC (like work_mem, etc.).
>
> Disclaimer: I'm not a JDBC community active user, that is my "opinion",
> and you can take or not... ;-)
>
> Regards,
> --
> Matheus de Oliveira
> Analista de Banco de Dados
> Dextra Sistemas - MPS.Br nível F!
> www.dextra.com.br/postgres
>
>
>
> ------------------------------
> If you reply to this email, your message will be added to the discussion
> below:
>
> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819845.html
> To unsubscribe from Patch to allow setting schema/search_path in the
> connectionURL, click here
> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=2174512&code=Y293d29jQGJicy5kYXJrdGVjaC5vcmd8MjE3NDUxMnwxNTc0MzIxMjQ3>
> .
> NAML
> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819855.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Yes it will work
On Sep 21, 2014 1:39 PM, "cowwoc" <cowwoc(at)bbs(dot)darktech(dot)org> wrote:

> I actually want the same user across all schemas (unit tests where each
> test runs against a different schema in order to isolate the tests from
> each other).
>
> Can someone please confirm whether set search_path can be set on a per
> connection basis?
>
> Thanks,
> Gili
> On Sep 21, 2014 10:50 AM, "Matheus de Oliveira [via PostgreSQL]" <[hidden
> email] <http://user/SendEmail.jtp?type=node&node=5819855&i=0>> wrote:
>
>>
>> On Thu, Sep 18, 2014 at 4:59 PM, John R Pierce <[hidden email]
>> <http://user/SendEmail.jtp?type=node&node=5819845&i=0>> wrote:
>>
>>> On 9/18/2014 12:47 PM, stagirus wrote:
>>>
>>>> Does anybody know of a solution on how to designate a schema while
>>>> establishing the connection? I would highly appreciate it!!!
>>>>
>>>>
>>>
>>> we use the rolename as the default schema. works for us.
>>
>>
>> If someone has this requirement, it *probably* uses different users for
>> different schema configurations. In such case you don't even need to match
>> user name and schema name, you can simple configure search_path per user as:
>>
>> ALTER ROLE username SET search_path TO schema1, schema2, ...;
>>
>> For me, setting search_path from URL connection is a mistake, as it is
>> not one of configurations accept on connection creating at the protocol (or
>> is it?), like it is for client_encoding, application_name and others. If
>> JDBC devs are going to change settings on URL then you probably should
>> start thinking on more generic, like setting any GUC (like work_mem, etc.).
>>
>> Disclaimer: I'm not a JDBC community active user, that is my "opinion",
>> and you can take or not... ;-)
>>
>> Regards,
>> --
>> Matheus de Oliveira
>> Analista de Banco de Dados
>> Dextra Sistemas - MPS.Br nível F!
>> www.dextra.com.br/postgres
>>
>>
>>
>> ------------------------------
>> If you reply to this email, your message will be added to the
>> discussion below:
>>
>> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819845.html
>> To unsubscribe from Patch to allow setting schema/search_path in the
>> connectionURL, click here.
>> NAML
>> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>>
>
> ------------------------------
> View this message in context: Re: Patch to allow setting
> schema/search_path in the connectionURL
> <http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819855.html>
> Sent from the PostgreSQL - jdbc mailing list archive
> <http://postgresql.1045698.n5.nabble.com/PostgreSQL-jdbc-f2168246.html>
> at Nabble.com.
>

Gili,

It should appear in the page you're linking either when the 9.4 is released
or if the feature is backported in 9.3 branch.

Alexis Meneses

2014-09-20 22:27 GMT+02:00 Gili <cowwoc(at)bbs(dot)darktech(dot)org>:

> I am expecting to see it here:
> http://jdbc.postgresql.org/documentation/head/connect.html
>
> Gili
>
>
> On 20/09/2014 3:48 AM, Alexis Meneses wrote:
>
> The docbook is up-to-date regarding this connection parameter. It's also
> reflected by the internal getPropertyInfo. Do you think about something
> else?
>
> Alexis Meneses
>
> 2014-09-19 18:55 GMT+02:00 cowwoc <cowwoc(at)bbs(dot)darktech(dot)org>:
>
>> Alexis,
>>
>> Is there an issue keeping track of the fact the documentation needs to be
>> updated?
>>
>> Thanks,
>> Gili
>>
>> On 19/09/2014 11:10 AM, Alexis Meneses [via PostgreSQL] wrote:
>>
>> Hi,
>>
>> This feature is already available as it has been implemented and merged
>> earlier in the year using a property named "currentSchema" of the JDBC URL.
>>
>> It's currenly only available in the master branch though.
>>
>> btw, the related issue #155 can be closed now.
>>
>> Alexis Meneses
>>
>> 2014-09-18 22:17 GMT+02:00 Dave Cramer <[hidden email]
>> <http://user/SendEmail.jtp?type=node&node=5819674&i=0>>:
>>
>>> What era is this exactly ? 3 people are looking for this particular
>>> feature. This is not part of the JDBC spec. Perhaps if it is that important
>>> someone can provide some code to implement this ?
>>>
>>> Dave Cramer
>>>
>>> dave.cramer(at)credativ(dot)ca
>>> http://www.credativ.ca
>>>
>>> On 18 September 2014 15:47, stagirus <[hidden email]
>>> <http://user/SendEmail.jtp?type=node&node=5819674&i=1>> wrote:
>>>
>>>> I too have a similar requirement to specify the schema.
>>>>
>>>> I see in various discussions that many people have tried to fix this
>>>> problem. It appears there is no resolution yet. It is sad that the JDBC
>>>> driver community does not seem to pay any attention to the end-user
>>>> needs.
>>>> Hello.. PLEASE WAKE UP! We are in a different era!
>>>>
>>>> Does anybody know of a solution on how to designate a schema while
>>>> establishing the connection? I would highly appreciate it!!!
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> View this message in context:
>>>> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819543.html
>>>> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>>>>
>>>>
>>>> --
>>>> Sent via pgsql-jdbc mailing list ([hidden email]
>>>> <http://user/SendEmail.jtp?type=node&node=5819674&i=2>)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>>>
>>>
>>>
>>
>>
>> ------------------------------
>> If you reply to this email, your message will be added to the
>> discussion below:
>>
>> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819674.html
>> To unsubscribe from Patch to allow setting schema/search_path in the
>> connectionURL, click here.
>> NAML
>> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>>
>>
>>
>> ------------------------------
>> View this message in context: Re: Patch to allow setting
>> schema/search_path in the connectionURL
>> <http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819704.html>
>> Sent from the PostgreSQL - jdbc mailing list archive
>> <http://postgresql.1045698.n5.nabble.com/PostgreSQL-jdbc-f2168246.html>
>> at Nabble.com.
>>
>
>
>

I just tried as follows:
ALTER ROLE dev SET search_path TO bep;

Then ran a bunch of SQL commands after connecting to the user dev. But it
fails because it is looking for the "public" schema. I am not sure how this
"ALTER ROLE" works.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820021.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

I am highly interested in exploring currentSchema property in the JDBC URL
string.

Can someone point me how/where to obtain the jar file with that change? I
really appreciate it.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820023.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On 9/22/2014 9:53 PM, stagirus wrote:
> I just tried as follows:
> ALTER ROLE dev SET search_path TO bep;
>
> Then ran a bunch of SQL commands after connecting to the user dev. But it
> fails because it is looking for the "public" schema. I am not sure how this
> "ALTER ROLE" works.

ALTER ROLE dev SET search_path TO bep,public;

--
john r pierce 37N 122W
somewhere on the middle of the left coast

You are all being very helpful. Pleasantly surprised. Thank you very much.

I thought the following context might be helpful. We have been using Oracle.
It conveniently and transparently relates a user to a schema. It makes
perfect sense for some architectures/designs.

But to replicate similar functionality/behavior within PG seems more
challenging than some experienced folks would expect.

YES.. we are living in the iPhone-era.. where what you expect 'magically'
works, even for a 3-year old!!

Again thank you all for your generous support..

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820026.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Sorry John. I don't see much of a difference in those two commands. For all
practical purpose, in our design the "public" schema has no role. I delete
it.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820028.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On 9/22/2014 10:02 PM, stagirus wrote:
> I thought the following context might be helpful. We have been using Oracle.
> It conveniently and transparently relates a user to a schema.

postgres' default search_path is $USER,public... so if there's a schema
the same name as the user role, then it will look for objects there
before it looks in public... which as far as I can tell is exactly what
you're saying Oracle does.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

On 9/22/2014 10:07 PM, stagirus wrote:
> Sorry John. I don't see much of a difference in those two commands. For all
> practical purpose, in our design the "public" schema has no role. I delete
> it.

then what is the meaning of your statement...

> Then ran a bunch of SQL commands after connecting to the user dev. But it
> fails because it is looking for the "public" schema. I am not sure how this
> "ALTER ROLE" works.

? /what/ is looking for the public schema ? what is the exact
error you are getting ?

--
john r pierce 37N 122W
somewhere on the middle of the left coast

John R Pierce <pierce(at)hogranch(dot)com> writes:
> On 9/22/2014 10:02 PM, stagirus wrote:
>> I thought the following context might be helpful. We have been using Oracle.
>> It conveniently and transparently relates a user to a schema.

> postgres' default search_path is $USER,public... so if there's a schema
> the same name as the user role, then it will look for objects there
> before it looks in public... which as far as I can tell is exactly what
> you're saying Oracle does.

Quite. You do have to create a schema having the same name as the user
(and owned by that user), but if you do that then you should get similar
behavior with the default search path.

There is some documentation about this in the fine manual; see
http://www.postgresql.org/docs/9.3/static/ddl-schemas.html
particularly the last two sections on that page.

regards, tom lane

Your above response almost gave a hope. But not too long.
All DDLs are going to the public schema. I get the errors as follows:

Error executing SQL CREATE TABLE public.databas...
Caused by: org.postgresql.util.PSQLException: ERROR: schema "public" does
not exist

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820034.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

stagirus <mamasa(at)stagirus(dot)com> writes:
> Your above response almost gave a hope. But not too long.
> All DDLs are going to the public schema. I get the errors as follows:

> Error executing SQL CREATE TABLE public.databas...
> Caused by: org.postgresql.util.PSQLException: ERROR: schema "public" does
> not exist

[ scratches head ... ] You just said you deleted the public schema.
Why do you find the above result surprising?

regards, tom lane

Now I have a schema bepc and role bepc and also the ownership between them

Same problem continues... It is sounding like a "witch-hunt"???

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820037.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On 9/22/2014 10:17 PM, stagirus wrote:
> Your above response almost gave a hope. But not too long.
> All DDLs are going to the public schema. I get the errors as follows:
>
> Error executing SQL CREATE TABLE public.databas...
> Caused by: org.postgresql.util.PSQLException: ERROR: schema "public" does
> not exist

huh. works in SQL.

$ psql
psql (9.3.5)
Type "help" for help.

postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create schema myschema;
CREATE SCHEMA
test=# set search_path to myschema;
SET
test=# drop schema public;
DROP SCHEMA
test=# create table stuff (id serial primary key, val text);
CREATE TABLE
test=# \d
List of relations
Schema | Name | Type | Owner
----------+--------------+----------+----------
myschema | stuff | table | postgres
myschema | stuff_id_seq | sequence | postgres
(2 rows)

test=#

--
john r pierce 37N 122W
somewhere on the middle of the left coast

On 9/22/2014 10:22 PM, stagirus wrote:
> Now I have a schema bepc and role bepc and also the ownership between them
>
> Same problem continues... It is sounding like a "witch-hunt"???
>

log on as role bepc, and

SHOW SEARCH_PATH;

whats that returning ?

--
john r pierce 37N 122W
somewhere on the middle of the left coast

Helllloooo..
I said I have no business or need for "public" schema. I want to instruct
the DB just to use a designated schema, in this case bepc.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820042.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

I am getting "$user,public"
When ran, in PG Query Tool, SHOW SEARCH_PATH;

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820045.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On 9/22/2014 10:30 PM, stagirus wrote:
> I am getting "$user,public"
> When ran, in PG Query Tool, SHOW SEARCH_PATH;

That's the default, implying you haven't set the search_path in use at
the time you did your CREATE TABLE ... if there is no schema matching
$USER (the current role name), then it will try public, which apparently
you deleted.

were you logged in as the user for whom you'd done the ALTER ROLE name
SET SEARCH_PATH TO ...; ?

--
john r pierce 37N 122W
somewhere on the middle of the left coast

We all know "set search_path to myschema" works.

But in some cases, while using JDBC and third-party tools, we don't have the
luxury of running "set search_path."

In my humble opinion, any industrial grade tools especially RDBMS and JDBC
drivers have to work in ALL scenarios not just in our own shell. Then user
adoption will be just limited to the shell-guys!!

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820048.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

But I do have a schema and user/role called bepc. I have ownership also
established between them. But when I logon as bepc, it is not picking the
schema bepc, as Oracle does.

What am I missing please?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820051.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On 9/22/2014 10:39 PM, stagirus wrote:
> But in some cases, while using JDBC and third-party tools, we don't have the
> luxury of running "set search_path."

why not? its a simple SQL command.

personally, I find the default search_path to be perfect for our needs.
we make extensive use of schema's named the same as the application
roles, and we put common tables used by all roles in public.

anyways, you SHOULD be able to set the search_path per role, not sure
why that didn't work for you, but I'm too tired now to cook up an
example showing it works as designed.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

Great news..

I finally resolved this problem. I have done the following stuff to make it
work.
1. I have carefully created a role with proper privileges.
2. I have created a schema with the same name as the role and assigned
ownership at the time of creation.
3. The tool that I am using fortunately supports setting the default schema
to be used. More than anything else, this seems to be the main driver.

Wola.. it worked. I want to thank all of you who tried to solve my problem.
Very kind of you.

JDBC driver devleoper(s) - show some mercy. Be open and flexible. Many
people have offered solutions (code changes) to support the default/current
schema problem. Accept that feature and make it part of your JDBC driver.

Thank you all.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820189.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

I'm confused. I thought we already agreed that this is implemented:
http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5819674.html

Gili

On 23/09/2014 3:11 PM, stagirus [via PostgreSQL] wrote:
> Great news..
>
> I finally resolved this problem. I have done the following stuff to
> make it work.
> 1. I have carefully created a role with proper privileges.
> 2. I have created a schema with the same name as the role and assigned
> ownership at the time of creation.
> 3. The tool that I am using fortunately supports setting the default
> schema to be used. More than anything else, this seems to be the main
> driver.
>
> Wola.. it worked. I want to thank all of you who tried to solve my
> problem. Very kind of you.
>
> JDBC driver devleoper(s) - show some mercy. Be open and flexible. Many
> people have offered solutions (code changes) to support the
> default/current schema problem. Accept that feature and make it part
> of your JDBC driver.
>
> Thank you all.
>
> ------------------------------------------------------------------------
> If you reply to this email, your message will be added to the
> discussion below:
> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820189.html
>
> To unsubscribe from Patch to allow setting schema/search_path in the
> connectionURL, click here
> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=2174512&code=Y293d29jQGJicy5kYXJrdGVjaC5vcmd8MjE3NDUxMnwxNTc0MzIxMjQ3>.
> NAML
> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820190.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

I am using your JDBC driver "postgresql-9.3-1102.jdbc4.jar" It did not seem
to support the property currentSchema. Or, does it?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820191.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

As explained in the aforementioned post, this feature has only been
added in 9.4 which isn't released yet.

Gili

On 23/09/2014 3:21 PM, stagirus [via PostgreSQL] wrote:
> I am using your JDBC driver "postgresql-9.3-1102.jdbc4.jar" It did not
> seem to support the property currentSchema. Or, does it?
>
>
> ------------------------------------------------------------------------
> If you reply to this email, your message will be added to the
> discussion below:
> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820191.html
>
> To unsubscribe from Patch to allow setting schema/search_path in the
> connectionURL, click here
> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=2174512&code=Y293d29jQGJicy5kYXJrdGVjaC5vcmd8MjE3NDUxMnwxNTc0MzIxMjQ3>.
> NAML
> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820193.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Unfortunately, it seems there are no snapshots of the master branch pushed
on maven repositories so far.
I think you can open an issue for this concern.

In the meantime, you can build it on your own from the source tree.

Alexis Meneses

2014-09-23 6:55 GMT+02:00 stagirus <mamasa(at)stagirus(dot)com>:

> I am highly interested in exploring currentSchema property in the JDBC URL
> string.
>
> Can someone point me how/where to obtain the jar file with that change? I
> really appreciate it.
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Patch-to-allow-setting-schema-search-path-in-the-connectionURL-tp2174512p5820023.html
> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>