Thread: 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 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
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
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@ejurka.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
storing true/false, was: Comments on adding more connection URL parameters.
From
"scott.marlowe"
Date:
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