Thread: 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.html is still applicable.
Regards,
Julien
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Julien Demoor
Sent: Monday, July 30, 2012 8:56 AM
To: pgsql-jdbc@postgresql.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.html is 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.
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.
With best regards,
-- Valentine
On Tue, Jul 31, 2012 at 2:41 AM, David Johnston <polobo@yahoo.com> wrote:
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Julien Demoor
Sent: Monday, July 30, 2012 8:56 AM
To: pgsql-jdbc@postgresql.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.html is 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.
On Tue, Jul 31, 2012 at 8:05 PM, Julien Demoor <jdemoor@gmail.com> wrote:
2012/7/31 Valentine Gogichashvili <valgog@gmail.com>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,
-- ValentineHello Valentine,I'll see if connection pools are available with BIRT (I'm using the integrated web viewer so I can't go around its limitations).Thanks for the tip regarding the support for the search_path. Should I expect issues if the types that share a name across schemas have the same definition?Regards,Julien
Hello Julien,
You can always write a simple wrapper, that will override getConnection() and preinizialize there as needed.
The problem is now, that OID cache for types does not take into an account search_path at all. So if you have 2 types, that have the same name in 2 different schemas, one of them will be taken practically randomly, and if you do not have luck, it will take the wrong one and postgres will throw a crazy exception when the driver will try to use the wrong OID when passing this type as a parameter for example.
But it is very easy to patch the driver in case you will get such a problem. I suggested one quick-n-dirty patch once, and filed a bug report without a patch... (http://archives.postgresql.org/pgsql-jdbc/2011-03/msg00007.php, http://archives.postgresql.org/pgsql-jdbc/2011-12/msg00083.php) but both messages had been ignored unfortunately.
You can have a look in wich case I am getting a problem there here: http://tech.valgog.com/2012/01/schema-based-versioning-and-deployment.html
Regards,
-- Valentin
Valentine, Any chance you could recreate that patch against current sources ? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Tue, Jul 31, 2012 at 3:40 PM, Valentine Gogichashvili <valgog@gmail.com> wrote: > On Tue, Jul 31, 2012 at 8:05 PM, Julien Demoor <jdemoor@gmail.com> wrote: >> >> 2012/7/31 Valentine Gogichashvili <valgog@gmail.com> >>> >>> 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 >> >> >> Hello Valentine, >> >> I'll see if connection pools are available with BIRT (I'm using the >> integrated web viewer so I can't go around its limitations). >> >> Thanks for the tip regarding the support for the search_path. Should I >> expect issues if the types that share a name across schemas have the same >> definition? >> >> Regards, >> Julien > > > Hello Julien, > > You can always write a simple wrapper, that will override getConnection() > and preinizialize there as needed. > > The problem is now, that OID cache for types does not take into an account > search_path at all. So if you have 2 types, that have the same name in 2 > different schemas, one of them will be taken practically randomly, and if > you do not have luck, it will take the wrong one and postgres will throw a > crazy exception when the driver will try to use the wrong OID when passing > this type as a parameter for example. > > But it is very easy to patch the driver in case you will get such a problem. > I suggested one quick-n-dirty patch once, and filed a bug report without a > patch... (http://archives.postgresql.org/pgsql-jdbc/2011-03/msg00007.php, > http://archives.postgresql.org/pgsql-jdbc/2011-12/msg00083.php) but both > messages had been ignored unfortunately. > > You can have a look in wich case I am getting a problem there here: > http://tech.valgog.com/2012/01/schema-based-versioning-and-deployment.html > > Regards, > > -- Valentin > > >
Dne 31.7.2012 16:03, Valentine Gogichashvili napsal(a): > 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. This most likely explains while hibernate is going wild if table exists in multiple schemas, from my observation it seems that schema is picked at random.
Hello Dave,
-- Valentine Gogichashvili
sorry, did not see your reply. I will try to write a patch.
There is one issue, with the patch implementation:
1. try to stay "backwards compatible", i.e. try to search in all existing schemas but prefer the one from the search_path order (actually I do not know if it really makes sense)
2. simply search according to search_path parameter, i.e. fail in case the schema is not seen by the search_path logic
Regards,
-- Valentine Gogichashvili
On Tue, Jul 31, 2012 at 10:18 PM, Dave Cramer <pg@fastcrypt.com> wrote:
Valentine,
Any chance you could recreate that patch against current sources ?
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On Tue, Jul 31, 2012 at 3:40 PM, Valentine Gogichashvili
<valgog@gmail.com> wrote:
> On Tue, Jul 31, 2012 at 8:05 PM, Julien Demoor <jdemoor@gmail.com> wrote:
>>
>> 2012/7/31 Valentine Gogichashvili <valgog@gmail.com>
>>>
>>> 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
>>
>>
>> Hello Valentine,
>>
>> I'll see if connection pools are available with BIRT (I'm using the
>> integrated web viewer so I can't go around its limitations).
>>
>> Thanks for the tip regarding the support for the search_path. Should I
>> expect issues if the types that share a name across schemas have the same
>> definition?
>>
>> Regards,
>> Julien
>
>
> Hello Julien,
>
> You can always write a simple wrapper, that will override getConnection()
> and preinizialize there as needed.
>
> The problem is now, that OID cache for types does not take into an account
> search_path at all. So if you have 2 types, that have the same name in 2
> different schemas, one of them will be taken practically randomly, and if
> you do not have luck, it will take the wrong one and postgres will throw a
> crazy exception when the driver will try to use the wrong OID when passing
> this type as a parameter for example.
>
> But it is very easy to patch the driver in case you will get such a problem.
> I suggested one quick-n-dirty patch once, and filed a bug report without a
> patch... (http://archives.postgresql.org/pgsql-jdbc/2011-03/msg00007.php,
> http://archives.postgresql.org/pgsql-jdbc/2011-12/msg00083.php) but both
> messages had been ignored unfortunately.
>
> You can have a look in wich case I am getting a problem there here:
> http://tech.valgog.com/2012/01/schema-based-versioning-and-deployment.html
>
> Regards,
>
> -- Valentin
>
>
>
I would recommend to do the strict implementation (only honor search_path), and define a new connection parameter (search_all_schemas=true) which would be default for now, to maintain the old behavior. If one wants the strict behavior just add search_all_schemas=false to the connection url, and in a later driver revision, change the default to search_all_schemas=false. You could also search only by search_path and, if that fails, search all the rest, and output a warning about it. In any case, I believe that it should be possible to have strict behavior within the driver. Luis Flores On Thu, Aug 16, 2012 at 4:29 PM, Valentine Gogichashvili <valgog@gmail.com> wrote: > Hello Dave, > > sorry, did not see your reply. I will try to write a patch. > > There is one issue, with the patch implementation: > > 1. try to stay "backwards compatible", i.e. try to search in all existing > schemas but prefer the one from the search_path order (actually I do not > know if it really makes sense) > 2. simply search according to search_path parameter, i.e. fail in case the > schema is not seen by the search_path logic > > Regards, > > -- Valentine Gogichashvili > > > > On Tue, Jul 31, 2012 at 10:18 PM, Dave Cramer <pg@fastcrypt.com> wrote: >> >> Valentine, >> >> Any chance you could recreate that patch against current sources ? >> >> Dave Cramer >> >> dave.cramer(at)credativ(dot)ca >> http://www.credativ.ca >> >> >> On Tue, Jul 31, 2012 at 3:40 PM, Valentine Gogichashvili >> <valgog@gmail.com> wrote: >> > On Tue, Jul 31, 2012 at 8:05 PM, Julien Demoor <jdemoor@gmail.com> >> > wrote: >> >> >> >> 2012/7/31 Valentine Gogichashvili <valgog@gmail.com> >> >>> >> >>> 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 >> >> >> >> >> >> Hello Valentine, >> >> >> >> I'll see if connection pools are available with BIRT (I'm using the >> >> integrated web viewer so I can't go around its limitations). >> >> >> >> Thanks for the tip regarding the support for the search_path. Should I >> >> expect issues if the types that share a name across schemas have the >> >> same >> >> definition? >> >> >> >> Regards, >> >> Julien >> > >> > >> > Hello Julien, >> > >> > You can always write a simple wrapper, that will override >> > getConnection() >> > and preinizialize there as needed. >> > >> > The problem is now, that OID cache for types does not take into an >> > account >> > search_path at all. So if you have 2 types, that have the same name in 2 >> > different schemas, one of them will be taken practically randomly, and >> > if >> > you do not have luck, it will take the wrong one and postgres will throw >> > a >> > crazy exception when the driver will try to use the wrong OID when >> > passing >> > this type as a parameter for example. >> > >> > But it is very easy to patch the driver in case you will get such a >> > problem. >> > I suggested one quick-n-dirty patch once, and filed a bug report without >> > a >> > patch... >> > (http://archives.postgresql.org/pgsql-jdbc/2011-03/msg00007.php, >> > http://archives.postgresql.org/pgsql-jdbc/2011-12/msg00083.php) but both >> > messages had been ignored unfortunately. >> > >> > You can have a look in wich case I am getting a problem there here: >> > >> > http://tech.valgog.com/2012/01/schema-based-versioning-and-deployment.html >> > >> > Regards, >> > >> > -- Valentin >> > >> > >> > > >