Обсуждение: 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@scharp.org

--- pgjdbc/doc/pgjdbc.xml    Sun Apr 13 09:03:49 2008
+++ pgjdbc_searchpath/doc/pgjdbc.xml    Tue Jul  8 11:13:02 2008
@@ -525,6 +525,18 @@
       </varlistentry>

       <varlistentry>
+       <term><varname>searchpath</varname> = <type>String</type></term>
+       <listitem>
+        <para>
+        Set a custom search path for this connection.  The first schema in the path will be 
+        used for any tables or other named objects that are created when no target schema
+        is specified in a command. This is only useful in the situation where it cannot be 
+        handled by the per user or per database defaults.         
+        </para>
+       </listitem>
+      </varlistentry>
+      
+      <varlistentry>
        <term><varname>allowEncodingChanges</varname> = <type>boolean</type></term>
        <listitem>
         <para>
--- pgjdbc/org/postgresql/Driver.java.in    Sun Apr 13 09:03:49 2008
+++ pgjdbc_searchpath/org/postgresql/Driver.java.in    Tue Jul  8 10:00:56 2008
@@ -180,6 +180,8 @@
      *  server then the character set of the database is used as the default,
      *  otherwise the jvm character encoding is used as the default.
      *   This value is only used when connecting to a 7.2 or older server.
+     * searchpath - (optional) Set a custom search path. Useful if you want to 
+     *  query a non-default schema but don't wish to modify your queries. 
      * loglevel - (optional) Enable logging of messages from the driver.
      *  The value is an integer from 1 to 2 where:
      *    INFO = 1, DEBUG = 2
@@ -447,6 +449,8 @@
                 { "stringtype", Boolean.FALSE,
                   "The type to bind String parameters as (usually 'varchar'; 'unspecified' allows implicit casting to
othertypes)", 
                   new String[] { "varchar", "unspecified" } },
+                { "searchpath", Boolean.FALSE,
+                  "Set a custom search path." },
             };

     /**
--- pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java    Mon Apr 14 21:23:57 2008
+++ pgjdbc_searchpath/org/postgresql/jdbc2/AbstractJdbc2Connection.java    Tue Jul  8 13:13:40 2008
@@ -160,6 +160,13 @@
         _typeCache = createTypeInfo(this);
         initObjectTypes(info);

+        // Set Custom Search Path
+        String searchpath = info.getProperty("searchpath");
+        if((searchpath != null) && (!(searchpath.trim().isEmpty()))){
+            String set_cmd = "SET search_path TO " + searchpath + "; show search_path";
+            execSQLQuery(set_cmd);
+        }
+        
         if (Boolean.valueOf(info.getProperty("logUnclosedConnections")).booleanValue()) {
             openStackTrace = new Throwable("Connection was created at this point:");
             enableDriverManagerLogging();

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 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

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]>:

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]> 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])
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

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@bbs.darktech.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]>:

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]> 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])
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

---

View this message in context: Re: Patch to allow setting schema/search_path in the connectionURL

Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

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

Yes it will work

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@hogranch.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@stagirus.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.

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.
NAML

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.

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.
NAML