Comments on adding more connection URL parameters.

I am aware of at least three feature proposals that have adding a
parameter to the connection URL as a requirement. I would like to solicit
comments on a policy for adding new URL parameters. Is there are reason
to try and restrict the number of supported parameters? Proposals right
now include a login timeout, a server side prepared statement threshold
where server statements are used after a certain number of uses, and a
schema search path setting. These three proposals accurately reflect the
range of possible reasons for neeeding a parameter:

http://archives.postgresql.org/pgsql-jdbc/2004-01/msg00106.php
login timeout: This is the only possible way to support this feature.
This information must be available before the connection is created, so
the URL is the only reasonable place to put it.

http://archives.postgresql.org/pgsql-jdbc/2003-12/msg00019.php
server prepare threshold: This makes using server prepared statements
possible without using pg specific code. It also allows server side
prepares to automatically turn themselves on for reused statements which
is the exact situation that this is desireable. It is possible to
implement this feature entirely in client code, but it would be a real
mess.

http://gborg.postgresql.org/project/pgjdbc/bugs/bugupdate.php?668
schema search path: This allows setting a GUC parameter "search_path" on
a per connection basis. This is only useful in the situation where it
cannot be handled by the per user or per database defaults. This is
something which can be handled entirely in client code by issuing an
appropriate SET command, but would arguably be cleaner in the URL,
especially in a connection pooling situation. The problem is that once
you add any GUC variable you don't have a strong basis for not adding them
all. I could see using guc_ as a prefix and any parameter starting that
way we tried to issue a SET on.

So I'd like your thoughts on adding new parameters. Only things not
possible without them? Only significant improvements that would be real
difficult without them? Only certain GUC variables? All GUC variables?

Kris Jurka

Kris,

I have no problem with having a larger number of parameters, although we
should look carefully at how many we add. So for me the issue isn't how
many or what types, but how are they set.

I think the process of setting them via the jdbc url is only suitable
for a limited number of parameters, since urls are often typed in by
hand into many applications you can't have 50 parameters also needed on
the url.

So I beleive that there needs to be some sort of hierarchy of locations
where parameter values can be set. I would suggest something like the
following:

1) jvm parameters (i.e. -Dxxx=yyy)
2) URL
3) property file specified by a url parameter
4) property file bundled in the jar (i.e.
org/postgresql/conf.properties) - this allows application builders who
bundle the jdbc driver with their application to set the parameters
their application requires
5) property file in a default location (like user.home)

Once the number of parameters becomes larger, I would expect most people
will end up using property files for their parameters and then change
individual ones on a case by case basis via the url or jvm for exception
cases.

thanks,
--Barry

Kris Jurka wrote:

> I am aware of at least three feature proposals that have adding a
> parameter to the connection URL as a requirement. I would like to solicit
> comments on a policy for adding new URL parameters. Is there are reason
> to try and restrict the number of supported parameters? Proposals right
> now include a login timeout, a server side prepared statement threshold
> where server statements are used after a certain number of uses, and a
> schema search path setting. These three proposals accurately reflect the
> range of possible reasons for neeeding a parameter:
>
>
> http://archives.postgresql.org/pgsql-jdbc/2004-01/msg00106.php
> login timeout: This is the only possible way to support this feature.
> This information must be available before the connection is created, so
> the URL is the only reasonable place to put it.
>
> http://archives.postgresql.org/pgsql-jdbc/2003-12/msg00019.php
> server prepare threshold: This makes using server prepared statements
> possible without using pg specific code. It also allows server side
> prepares to automatically turn themselves on for reused statements which
> is the exact situation that this is desireable. It is possible to
> implement this feature entirely in client code, but it would be a real
> mess.
>
> http://gborg.postgresql.org/project/pgjdbc/bugs/bugupdate.php?668
> schema search path: This allows setting a GUC parameter "search_path" on
> a per connection basis. This is only useful in the situation where it
> cannot be handled by the per user or per database defaults. This is
> something which can be handled entirely in client code by issuing an
> appropriate SET command, but would arguably be cleaner in the URL,
> especially in a connection pooling situation. The problem is that once
> you add any GUC variable you don't have a strong basis for not adding them
> all. I could see using guc_ as a prefix and any parameter starting that
> way we tried to issue a SET on.
>
> So I'd like your thoughts on adding new parameters. Only things not
> possible without them? Only significant improvements that would be real
> difficult without them? Only certain GUC variables? All GUC variables?
>
>
> Kris Jurka
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match

Kris,

I also have a few more,

one to change the behaviour for handling booleans, from inserting 't',
'f' to inserting '1', and '0'

I think one way to deal with this on a non-connection basis is to use
System properties, this won't work for the schema search path, but would
work for most others.

How do the other drivers handle this?

Dave
On Tue, 2004-02-03 at 03:57, Kris Jurka wrote:
> I am aware of at least three feature proposals that have adding a
> parameter to the connection URL as a requirement. I would like to solicit
> comments on a policy for adding new URL parameters. Is there are reason
> to try and restrict the number of supported parameters? Proposals right
> now include a login timeout, a server side prepared statement threshold
> where server statements are used after a certain number of uses, and a
> schema search path setting. These three proposals accurately reflect the
> range of possible reasons for neeeding a parameter:
>
>
> http://archives.postgresql.org/pgsql-jdbc/2004-01/msg00106.php
> login timeout: This is the only possible way to support this feature.
> This information must be available before the connection is created, so
> the URL is the only reasonable place to put it.
>
> http://archives.postgresql.org/pgsql-jdbc/2003-12/msg00019.php
> server prepare threshold: This makes using server prepared statements
> possible without using pg specific code. It also allows server side
> prepares to automatically turn themselves on for reused statements which
> is the exact situation that this is desireable. It is possible to
> implement this feature entirely in client code, but it would be a real
> mess.
>
> http://gborg.postgresql.org/project/pgjdbc/bugs/bugupdate.php?668
> schema search path: This allows setting a GUC parameter "search_path" on
> a per connection basis. This is only useful in the situation where it
> cannot be handled by the per user or per database defaults. This is
> something which can be handled entirely in client code by issuing an
> appropriate SET command, but would arguably be cleaner in the URL,
> especially in a connection pooling situation. The problem is that once
> you add any GUC variable you don't have a strong basis for not adding them
> all. I could see using guc_ as a prefix and any parameter starting that
> way we tried to issue a SET on.
>
> So I'd like your thoughts on adding new parameters. Only things not
> possible without them? Only significant improvements that would be real
> difficult without them? Only certain GUC variables? All GUC variables?
>
>
> Kris Jurka
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
--
Dave Cramer
519 939 0336
ICQ # 14675561

Dave Cramer wrote:

> I think one way to deal with this on a non-connection basis is to use
> System properties, this won't work for the schema search path, but would
> work for most others.

Please, not system properties; they're global which is a really bad
thing for anyone who wants to instantiate multiple Datasources with
different options.

-O

Oh, good point, ok, so that leaves us with a whole bunch of url options.
What about a naming scheme, any suggestions?

Dave
On Tue, 2004-02-03 at 21:16, Oliver Jowett wrote:
> Dave Cramer wrote:
>
> > I think one way to deal with this on a non-connection basis is to use
> > System properties, this won't work for the schema search path, but would
> > work for most others.
>
> Please, not system properties; they're global which is a really bad
> thing for anyone who wants to instantiate multiple Datasources with
> different options.
>
> -O
>
--
Dave Cramer
519 939 0336
ICQ # 14675561

Barry Lind wrote:

> So I beleive that there needs to be some sort of hierarchy of locations
> where parameter values can be set. I would suggest something like the
> following:
>
> 1) jvm parameters (i.e. -Dxxx=yyy)
> 2) URL
> 3) property file specified by a url parameter
> 4) property file bundled in the jar (i.e.
> org/postgresql/conf.properties) - this allows application builders who
> bundle the jdbc driver with their application to set the parameters
> their application requires
> 5) property file in a default location (like user.home)

It seems that 1, 4, and 5 address the same problem (providing
system-wide defaults). Pick one?

DriverManager.getConnection() has a variant that takes a Properties
object, so I'd expect apps to support reading properties from a
configuration file to pass to the driver. Maybe it'd be preferable to
encourage apps to take this route rather than having a huge URL string.
I'm not sure we need to support passing a properties file location in
the URL (3) if we do this.

Another piece of this puzzle is the DataSource implementation. Currently
you can't get access to some of the parameters available via a URL if
using a DataSource: the DataSource implementation is responsible for
constructing the URL used from the current object state, but it only
includes server, port, and database (and passes username/password
directly). I'd like to see all properties accessible via DriverManager
also accessible via our DataSource implementation.

One issue with the DataSource mapping is that it gets harder to support
a whole family of parameters (e.g. the guc_ prefix suggested) in one go,
as we need to know the exact set of parameters we support at compile
time so we can declare the necessary accessor methods. Maybe we could
provide a catchall accessor (setExtraParameters()?) that takes a
Properties object or similar.

-O

On Tue, 3 Feb 2004, Barry Lind wrote:

> Kris,
>
> I have no problem with having a larger number of parameters, although we
> should look carefully at how many we add. So for me the issue isn't how
> many or what types, but how are they set.
>
> I think the process of setting them via the jdbc url is only suitable
> for a limited number of parameters, since urls are often typed in by
> hand into many applications you can't have 50 parameters also needed on
> the url.

No parameters are needed, they are all optional. So I don't really by
this argument.

> So I beleive that there needs to be some sort of hierarchy of locations
> where parameter values can be set. I would suggest something like the
> following:
>
> 1) jvm parameters (i.e. -Dxxx=yyy)
> 2) URL
> 3) property file specified by a url parameter
> 4) property file bundled in the jar (i.e.
> org/postgresql/conf.properties) - this allows application builders who
> bundle the jdbc driver with their application to set the parameters
> their application requires
> 5) property file in a default location (like user.home)
>
> Once the number of parameters becomes larger, I would expect most people
> will end up using property files for their parameters and then change
> individual ones on a case by case basis via the url or jvm for exception
> cases.

I suspect people with large numbers of properties will use a properties
file, but we don't want to deal with files. As Oliver mentioned there is
a DriverManager.getConnection method that takes a properties object so it
should be the user's responsibility to construct this from whatever
sources they desire.

The only idea I found interesting was "4) property file bundled in the
jar" This could potentially make deploying a number of applications that
behave in the same way simpler, but would also I imagine generate a number
of questions like "Why is the driver doing ..." not knowing that the jar
file had these configuration parameters. All of these ideas are something
I've never heard people ask for, so I'd like to know we are solving a
general problem before introducing this complexity and potential for
errors.

Kris Jurka

On Wed, 4 Feb 2004, Oliver Jowett wrote:

> One issue with the DataSource mapping is that it gets harder to support
> a whole family of parameters (e.g. the guc_ prefix suggested) in one go,
> as we need to know the exact set of parameters we support at compile
> time so we can declare the necessary accessor methods. Maybe we could
> provide a catchall accessor (setExtraParameters()?) that takes a
> Properties object or similar.

Yes, supporting all GUC options is something we certainly wouldn't want to
do by providing a method for each one. Especially since different server
versions have different options and syntax. I imagine setGUC(String name,
String value) would be sufficient. I suppose also we would have to be
aware of GUC options that the driver sets itself (like datestyle and
client_encoding) to ensure that these are not overwritten.

Kris Jurka

Kris Jurka <books(at)ejurka(dot)com> writes:
> Yes, supporting all GUC options is something we certainly wouldn't want to
> do by providing a method for each one. Especially since different server
> versions have different options and syntax. I imagine setGUC(String name,
> String value) would be sufficient. I suppose also we would have to be
> aware of GUC options that the driver sets itself (like datestyle and
> client_encoding) to ensure that these are not overwritten.

One minor gripe here: GUC is a server-implementation acronym that really
shouldn't be exposed as part of a client API, because it means nothing
to the average user. Can we do something that refers to "server
parameters" instead?

regards, tom lane

On 04/02/2004 06:04 Oliver Jowett wrote:
> [snip]
> DriverManager.getConnection() has a variant that takes a Properties
> object, so I'd expect apps to support reading properties from a
> configuration file to pass to the driver. Maybe it'd be preferable to
> encourage apps to take this route rather than having a huge URL string.
> I'm not sure we need to support passing a properties file location in
> the URL (3) if we do this.

For web application developers who use connection pooling (e.g., me)
passing parameters in the URL would be the preferred option. ATM, I can't
see how putting the name of a properties file in the URL would really help
as deciding on a valid physical location for the file can be problematic
and is highly container-specific. For my purposes, JNDI would be
preferable to a properties file if it is decided to add facilities beyond
URL paramters.
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+

On Wed, 4 Feb 2004, Paul Thomas wrote:

>
> On 04/02/2004 06:04 Oliver Jowett wrote:
> > [snip]
> > DriverManager.getConnection() has a variant that takes a Properties
> > object, so I'd expect apps to support reading properties from a
> > configuration file to pass to the driver. Maybe it'd be preferable to
> > encourage apps to take this route rather than having a huge URL string.
> > I'm not sure we need to support passing a properties file location in
> > the URL (3) if we do this.
>
> For web application developers who use connection pooling (e.g., me)
> passing parameters in the URL would be the preferred option. ATM, I can't
> see how putting the name of a properties file in the URL would really help
> as deciding on a valid physical location for the file can be problematic
> and is highly container-specific. For my purposes, JNDI would be
> preferable to a properties file if it is decided to add facilities beyond
> URL paramters.

As java.sql.Driver's only connection method is connect(String url,
Properties info) we are always eventually dealing with both a url and a
properties object. Internally pg takes the URL and parses it into various
fields and sticks that all in the given properties object and then uses
the properties object exclusively. This means that any parameter can be
passed in either the properties object or the URL. So the debate between
the url and the properties object is not an important one. I know you are
specifically addresses Barry's proposed additional files, but I just
wanted to make clear that url parameters and the properties data is the
same thing.

Kris Jurka

On 3 Feb 2004, Dave Cramer wrote:

> Kris,
>
> I also have a few more,
>
> one to change the behaviour for handling booleans, from inserting 't',
> 'f' to inserting '1', and '0'
>
> I think one way to deal with this on a non-connection basis is to use
> System properties, this won't work for the schema search path, but would
> work for most others.
>
> How do the other drivers handle this?

Why not store TRUE and FALSE with no ticks. Like DEFAULT and NULL they're
keywords that mean the exact thing, not an internal representation that
might change over time.

insert into table1 (tf) values (TRUE);

Kris Jurka wrote:
>
> On Tue, 3 Feb 2004, Barry Lind wrote:
>
>
>>Kris,
>>
>>I have no problem with having a larger number of parameters, although we
>>should look carefully at how many we add. So for me the issue isn't how
>>many or what types, but how are they set.
>>
>>I think the process of setting them via the jdbc url is only suitable
>>for a limited number of parameters, since urls are often typed in by
>>hand into many applications you can't have 50 parameters also needed on
>>the url.
>
>
> No parameters are needed, they are all optional. So I don't really by
> this argument.
>

While this is technically true, it is only true to the extent that you
like the default values of all the parameters. If you need other than
the default values. As we have more parameters there will be more
parameters that the default value will not be ideal and I would like to
change it, thus the url will get longer and longer.

Take for example the postgresql.conf file. There are a lot of
parameters in it, and in general there aren't many that I end up
changing on my development machine. However on my production box where
performance is much more important there are a lot of parameters that I
don't feel the default values are the best. Likewise I see that once
the driver has a lot of parameters that can tweak a bunch of things in
little ways, you will want to use many of them to get optimal
performance/behavior in your production deployments.

>
>>So I beleive that there needs to be some sort of hierarchy of locations
>>where parameter values can be set. I would suggest something like the
>>following:
>>
>>1) jvm parameters (i.e. -Dxxx=yyy)
>>2) URL
>>3) property file specified by a url parameter
>>4) property file bundled in the jar (i.e.
>>org/postgresql/conf.properties) - this allows application builders who
>>bundle the jdbc driver with their application to set the parameters
>>their application requires
>>5) property file in a default location (like user.home)
>>
>>Once the number of parameters becomes larger, I would expect most people
>>will end up using property files for their parameters and then change
>>individual ones on a case by case basis via the url or jvm for exception
>>cases.
>
>
> I suspect people with large numbers of properties will use a properties
> file, but we don't want to deal with files. As Oliver mentioned there is
> a DriverManager.getConnection method that takes a properties object so it
> should be the user's responsibility to construct this from whatever
> sources they desire.
>

But in a lot of cases I am dealing with an application for which I don't
have the source code (or dealing with the source code is a pain), and
the application only exposes the ability to give it a jdbc url. So you
don't have the option to use the underlying ability of the jdbc api to
use a properties object since the application doesn't expose that
capability. In fact I don't know of any application the exposes
anything other than the url as being settable by the user.

> The only idea I found interesting was "4) property file bundled in the
> jar" This could potentially make deploying a number of applications that
> behave in the same way simpler, but would also I imagine generate a number
> of questions like "Why is the driver doing ..." not knowing that the jar
> file had these configuration parameters. All of these ideas are something
> I've never heard people ask for, so I'd like to know we are solving a
> general problem before introducing this complexity and potential for
> errors.

I have experienced a need for most of these at one point or another.
And I forsee the need will only get more pronounced as more options are
exposed as being settable. For example I would like to see the
logging/debugging capabilities be much more robust (i.e. turn on
statement logging, bind variable logging, and a host of other
possibilities that are usefull when developing or supporting a customer
who has deployed an application), but I have been reluctant to even
think about adding all these different properties until some mechanism
exists to deal with them all other than the url.

I was only suggesting these five ways because a) they are all the
possible ways I could think of, and b) I could see some usefullness for
each.

1) jvm parameters (i.e. -Dxxx=yyy)
- The use case here is purly hypothetical and probably not at all
realistic, however maybe someone else has some other reason so I
included it in my list. My use case was: you have an application that
you have deployed that bundles the database with it, so you don't even
have access to setting the url since it is all a black box. However you
want to turn on logging or something else to diagnose a problem.

2) URL
- Doesn't need any explaination.

3) property file specified by a url parameter
- Too many properties to specify on the URL and the URL is the only
mechanism exposed by the application to the end user. Also allows some
reuse of property settings. I also like the suggestion someone had of
using JNDI for this as well.

4) property file bundled in the jar (i.e.
org/postgresql/conf.properties)
- this allows application builders who bundle the jdbc driver with
their application to set the parameters their application requires

5) property file in a default location (like user.home)
- useful for development and testing, since you can have a central
place to turn on debugging for example that will get used by all
tests/environments so you don't need to enable it in multiple places.

I am not saying that all of these are good or even that useful, but just
want to throw out some ideas to spark conversation. None of this has to
be done, but I think it would be useful in some form. I have started
implementing this more than once, but never got it completed, partly
because of not having a clear idea of what (if anything) others would
want/need from such a feature.

Since you brought up the topic of adding additional parameters, this
seemed like a related topic that would also be useful to get peoples
ideas on.

thanks,
--Barry

Scott,

This is a backend thing, 'f' 't' are boolean values for the backend, we
don't attempt to parse and change things.

Dave
On Wed, 2004-02-04 at 11:36, scott.marlowe wrote:
> On 3 Feb 2004, Dave Cramer wrote:
>
> > Kris,
> >
> > I also have a few more,
> >
> > one to change the behaviour for handling booleans, from inserting 't',
> > 'f' to inserting '1', and '0'
> >
> > I think one way to deal with this on a non-connection basis is to use
> > System properties, this won't work for the schema search path, but would
> > work for most others.
> >
> > How do the other drivers handle this?
>
> Why not store TRUE and FALSE with no ticks. Like DEFAULT and NULL they're
> keywords that mean the exact thing, not an internal representation that
> might change over time.
>
> insert into table1 (tf) values (TRUE);
>
--
Dave Cramer
519 939 0336
ICQ # 14675561

Sorry, since this is the jdbc list I kinda assumed you were talking about
how jdbc was storing true and false...

On 4 Feb 2004, Dave Cramer wrote:

> Scott,
>
> This is a backend thing, 'f' 't' are boolean values for the backend, we
> don't attempt to parse and change things.
>
> Dave
> On Wed, 2004-02-04 at 11:36, scott.marlowe wrote:
> > On 3 Feb 2004, Dave Cramer wrote:
> >
> > > Kris,
> > >
> > > I also have a few more,
> > >
> > > one to change the behaviour for handling booleans, from inserting 't',
> > > 'f' to inserting '1', and '0'
> > >
> > > I think one way to deal with this on a non-connection basis is to use
> > > System properties, this won't work for the schema search path, but would
> > > work for most others.
> > >
> > > How do the other drivers handle this?
> >
> > Why not store TRUE and FALSE with no ticks. Like DEFAULT and NULL they're
> > keywords that mean the exact thing, not an internal representation that
> > might change over time.
> >
> > insert into table1 (tf) values (TRUE);
> >
>

I am, but we can't just go making up our own version of true and false,
what this is referring to is storing a true/false into an integer and
interpreting it as such from the getBoolean()/setBoolean() methods

DAve
On Wed, 2004-02-04 at 12:20, scott.marlowe wrote:
> Sorry, since this is the jdbc list I kinda assumed you were talking about
> how jdbc was storing true and false...
>
> On 4 Feb 2004, Dave Cramer wrote:
>
> > Scott,
> >
> > This is a backend thing, 'f' 't' are boolean values for the backend, we
> > don't attempt to parse and change things.
> >
> > Dave
> > On Wed, 2004-02-04 at 11:36, scott.marlowe wrote:
> > > On 3 Feb 2004, Dave Cramer wrote:
> > >
> > > > Kris,
> > > >
> > > > I also have a few more,
> > > >
> > > > one to change the behaviour for handling booleans, from inserting 't',
> > > > 'f' to inserting '1', and '0'
> > > >
> > > > I think one way to deal with this on a non-connection basis is to use
> > > > System properties, this won't work for the schema search path, but would
> > > > work for most others.
> > > >
> > > > How do the other drivers handle this?
> > >
> > > Why not store TRUE and FALSE with no ticks. Like DEFAULT and NULL they're
> > > keywords that mean the exact thing, not an internal representation that
> > > might change over time.
> > >
> > > insert into table1 (tf) values (TRUE);
> > >
> >
>
--
Dave Cramer
519 939 0336
ICQ # 14675561

On 04/02/2004 09:37 Kris Jurka wrote:
> [snip]
> As java.sql.Driver's only connection method is connect(String url,
> Properties info) we are always eventually dealing with both a url and a
> properties object. Internally pg takes the URL and parses it into
> various
> fields and sticks that all in the given properties object and then uses
> the properties object exclusively. This means that any parameter can be
> passed in either the properties object or the URL. So the debate between
> the url and the properties object is not an important one. I know you
> are
> specifically addresses Barry's proposed additional files, but I just
> wanted to make clear that url parameters and the properties data is the
> same thing.

True.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+

Kris Jurka wrote:
>
> On Wed, 4 Feb 2004, Oliver Jowett wrote:
>
>
>>One issue with the DataSource mapping is that it gets harder to support
>>a whole family of parameters (e.g. the guc_ prefix suggested) in one go,
>>as we need to know the exact set of parameters we support at compile
>>time so we can declare the necessary accessor methods. Maybe we could
>>provide a catchall accessor (setExtraParameters()?) that takes a
>>Properties object or similar.
>
>
> Yes, supporting all GUC options is something we certainly wouldn't want to
> do by providing a method for each one. Especially since different server
> versions have different options and syntax. I imagine setGUC(String name,
> String value) would be sufficient. I suppose also we would have to be
> aware of GUC options that the driver sets itself (like datestyle and
> client_encoding) to ensure that these are not overwritten.

DataSource accessors are meant to follow the JavaBeans property model. I
don't think that lets you do two-parameter accessors (the only exception
is "indexed accessors" that take (int, othertype)), which is why I
suggested passing a Properties object originally. We could pass a single
String[][] argument but that seems a bit nasty and less likely to be
supported by a generic app using introspection.. even using Properties
is a bit dodgy here.

-O