Thread: Client application name
A useful feature found in other DBMSs such as MS SQL Server that has been requested on these lists a few times, is the ability for a client application to report its name to the server. This information may then be presented in logs, activity reports and so on to aid debugging and help the sysadmin/DBA see easily what activity is coming from where on their server. The attached patch is a first quick cut of the basic functionality to do this. Currently, it makes the following changes: - Adds a new userset GUC called application_name. - Modifies psql to set application_name to 'psql' following connection to an 8.5 or above server. - Adds the application_name value to the CSV log output. - Adds a new parameter %a to the log line prefix which is replaced by the application_name value. - Reports the application_name with the other session stats in shared memory. - Adds application_name to the pg_stat_activity view and pg_stat_get_activity(int) function. Work to be done: - Docs - libpq modifications - Set the application_name in pg_dump and pals. My questions to the group are: - Is my approach reasonable? - What interface should I include in libpq? On the second question, obviously the user can call SET to set a value, as I've done for now in psql, however in other DBMSs, it may be set in the connection string. My feeling would be to do that, and possibly add PQsetApplicationName(PGconn *conn, char *name) and char *PQapplicationName(PGconn *conn);. What do others think? (Yes, I know I should technically discuss then code, but I was going to do this as a pet project to dabble in the server code which I don't do nearly often enough and figured I'd just send a WIP :-p ). -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Attachment
Dave Page wrote: > The attached patch is a first quick cut of the basic functionality to > do this. Currently, it makes the following changes: Couple of thoughts, - should we use argv[0] automatically in libpq unless overridden? - should we reject funny chars such as \n? (hopefully \0 won't be a problem) -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Dave Page wrote: > My questions to the group are: > > - Is my approach reasonable? > - What interface should I include in libpq? > > On the second question, obviously the user can call SET to set a > value, as I've done for now in psql, however in other DBMSs, it may be > set in the connection string. My feeling would be to do that, and > possibly add PQsetApplicationName(PGconn *conn, char *name) and char > *PQapplicationName(PGconn *conn);. What do others think? > > Doing it with a GUC will not be nearly so useful as having it in the wire protocol, IMNSHO. Just one example: it wouldn't be present in connection records, because it wouldn't be set yet. cheers andrew
On Tue, Oct 13, 2009 at 4:06 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Dave Page wrote: > >> The attached patch is a first quick cut of the basic functionality to >> do this. Currently, it makes the following changes: > > Couple of thoughts, > > - should we use argv[0] automatically in libpq unless overridden? How can I get to it from libpq? I could use getprogname() if present. > - should we reject funny chars such as \n? (hopefully \0 won't be a > problem) Is there any need? I can't see that it would do any harm other than maybe messing up some query output - and the solution would be 'don't do that then' :-) -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Tue, Oct 13, 2009 at 4:11 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > > Doing it with a GUC will not be nearly so useful as having it in the wire > protocol, IMNSHO. Just one example: it wouldn't be present in connection > records, because it wouldn't be set yet. I quite like the flexibility of being able to set/show a GUC at any time, but you raise a good point. I'll need to venture into previously unknown territory (for me at least :-p) to figure out how best to do that, and if possible keep the GUC... -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page wrote: > On Tue, Oct 13, 2009 at 4:06 PM, Alvaro Herrera > <alvherre@commandprompt.com> wrote: > > - should we reject funny chars such as \n? (hopefully \0 won't be a > > problem) > > Is there any need? I can't see that it would do any harm other than > maybe messing up some query output - and the solution would be 'don't > do that then' :-) I worry about log_line_prefix expansion with an unexpected newline. I'm not sure "don't do that" is a good enough answer because you might be dealing with uncooperative application writers :-( -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Dave Page wrote: > On Tue, Oct 13, 2009 at 4:11 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > >> Doing it with a GUC will not be nearly so useful as having it in the wire >> protocol, IMNSHO. Just one example: it wouldn't be present in connection >> records, because it wouldn't be set yet. >> > > I quite like the flexibility of being able to set/show a GUC at any > time, but you raise a good point. I'll need to venture into previously > unknown territory (for me at least :-p) to figure out how best to do > that, and if possible keep the GUC... > > From time to time people ask for "scalar variable" facility. ISTM what you're trying to do is just a special case of that. Maybe we could approach it by providing a builtin (and non-removable) custom_variable_classes entry ('pg_variables'?). Then you could have clients safely do: set pg_variables.client_name = 'blurfl'; And I'm sure other people would find interesting uses for such a gadget. cheers andrew
On Tue, Oct 13, 2009 at 4:34 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > > From time to time people ask for "scalar variable" facility. ISTM what > you're trying to do is just a special case of that. Maybe we could approach > it by providing a builtin (and non-removable) custom_variable_classes entry > ('pg_variables'?). Then you could have clients safely do: > > set pg_variables.client_name = 'blurfl'; > > And I'm sure other people would find interesting uses for such a gadget. I'm not sure that's really related to this - for example, we wouldn't want to push everything in the custom class through the logger or into per-backend shared memory, which would mean special-casing this particular variable for which doing those things is the primary use case. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page <dpage@pgadmin.org> writes: > - Is my approach reasonable? > - What interface should I include in libpq? I thought the plan was to have libpq look at an environment variable, compare PGCLIENTENCODING for example. I'm not convinced psql should be involved in the logic at all --- if it is, there definitely must be a way for scripts to override the "psql" value. In general the place that is most reasonable to set the value might be several software levels up from libpq, which is what makes the environment-variable approach attractive. regards, tom lane
Dave Page <dpage@pgadmin.org> writes: > On Tue, Oct 13, 2009 at 4:06 PM, Alvaro Herrera > <alvherre@commandprompt.com> wrote: >> - should we use argv[0] automatically in libpq unless overridden? > How can I get to it from libpq? You can't. regards, tom lane
Dave Page <dpage@pgadmin.org> writes: > On Tue, Oct 13, 2009 at 4:34 PM, Andrew Dunstan <andrew@dunslane.net> wrote: >> From time to time people ask for "scalar variable" facility. > I'm not sure that's really related to this It isn't; I think Andrew confused this thread with the one where someone wanted to know about trigger context. regards, tom lane
On Tue, Oct 13, 2009 at 5:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> - Is my approach reasonable? >> - What interface should I include in libpq? > > I thought the plan was to have libpq look at an environment variable, I wasn't aware we had a plan :-) > compare PGCLIENTENCODING for example. I'm not convinced psql should be > involved in the logic at all --- if it is, there definitely must be a > way for scripts to override the "psql" value. In general the place that > is most reasonable to set the value might be several software levels up > from libpq, which is what makes the environment-variable approach > attractive. The current implementation just has psql do SET application_name = 'psql'; immediately following connection to setup a sensible default. That can be overridden at any time with another SET. I can have libpq look at the environment as it does for PGCLIENTENCODING, but I'd certainly like to be able to use the connection string as well, as environment variables are not really the first choice of a Windows programmer for such things. I'm not sure psql should be looking directly at the environment though should it? Or would you envisage it only SETing application_name itself, if libpq didn't already have a value from elsewhere? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Tom Lane wrote: > Dave Page <dpage@pgadmin.org> writes: > >> On Tue, Oct 13, 2009 at 4:34 PM, Andrew Dunstan <andrew@dunslane.net> wrote: >> >>> From time to time people ask for "scalar variable" facility. >>> > > >> I'm not sure that's really related to this >> > > It isn't; I think Andrew confused this thread with the one where someone > wanted to know about trigger context. > > > I didn't but I'm multitasking and should stop. cheers andrew
On Tue, 13 Oct 2009, Dave Page wrote: > A useful feature found in other DBMSs such as MS SQL Server that has > been requested on these lists a few times, is the ability for a client > application to report its name to the server. This information may > then be presented in logs, activity reports and so on to aid debugging > and help the sysadmin/DBA see easily what activity is coming from > where on their server. As a point of reference the JDBC API specifies the following which allows multiple properties that are similar to the desired request which are useful in a multi-tier application. http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#setClientInfo(java.lang.String,%20java.lang.String) Kris Jurka
>I can have libpq look at the environment as it does for<br />>PGCLIENTENCODING, but I'd certainly like to be ableto use the<br />>connection string as well, as environment variables are not really the<br /><br />another challengewith the Environment variable: they are (at least on windows) usually set for one logged on user.<br /><br />Andususally I have pg_admin, python for development, psql and my application all connected to the same PostgreSQL server.<br/><br />I would love to see 4 different application names, and not the value of one environment-variable. thatargv[0] that was somewhere in this thread looked nice :)<br /><br />Harald<br /><br /><br clear="all" /><br />-- <br/>GHUM Harald Massa<br />persuadere et programmare<br />Harald Armin Massa<br />Spielberger Straße 49<br />70435 Stuttgart<br/>0173/9409607<br />no fx, no carrier pigeon <br />-<br />%s is too gigantic of an industry to bend to the whimsof reality<br />
On Tuesday 13 October 2009 18:30:54 Massa, Harald Armin wrote: > >I can have libpq look at the environment as it does for > >PGCLIENTENCODING, but I'd certainly like to be able to use the > >connection string as well, as environment variables are not really the > another challenge with the Environment variable: they are (at least on > windows) usually set for one logged on user. > And ususally I have pg_admin, python for development, psql and my > application all connected to the same PostgreSQL server. > I would love to see 4 different application names, and not the value of one > environment-variable. that argv[0] that was somewhere in this thread looked > nice :) Well, those applications could set it themselves... Andres
On Tue, Oct 13, 2009 at 12:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> - Is my approach reasonable? >> - What interface should I include in libpq? > > I thought the plan was to have libpq look at an environment variable, > compare PGCLIENTENCODING for example. I'm not convinced psql should be > involved in the logic at all --- if it is, there definitely must be a > way for scripts to override the "psql" value. In general the place that > is most reasonable to set the value might be several software levels up > from libpq, which is what makes the environment-variable approach > attractive. What happens if we want to change the application name after the fact?Consider the case where there is a connection poolerbetween the database and application, for example. ...Robert
Robert Haas <robertmhaas@gmail.com> writes: > What happens if we want to change the application name after the fact? As long as it's a GUC variable you can just do SET. I think the point of discussion is what is the best convention for getting it set initially. regards, tom lane
On Tue, Oct 13, 2009 at 11:55 AM, Robert Haas <robertmhaas@gmail.com> wrote: > > What happens if we want to change the application name after the fact? > Consider the case where there is a connection pooler between the > database and application, for example. > good point... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
Dave Page <dpage@pgadmin.org> writes: > On Tue, Oct 13, 2009 at 5:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Dave Page <dpage@pgadmin.org> writes: >>> - Is my approach reasonable? >> >> I thought the plan was to have libpq look at an environment variable, > I wasn't aware we had a plan :-) There was some previous discussion of this, which I am too lazy to look up but I thought we had sketched out an API. regards, tom lane
On Tue, Oct 13, 2009 at 6:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> On Tue, Oct 13, 2009 at 5:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Dave Page <dpage@pgadmin.org> writes: >>>> - Is my approach reasonable? >>> >>> I thought the plan was to have libpq look at an environment variable, > >> I wasn't aware we had a plan :-) > > There was some previous discussion of this, which I am too lazy to look > up but I thought we had sketched out an API. This seems to be the recent one, which is probably what was in the back of my mind when I decided to spend some spare time on this project: http://archives.postgresql.org/pgsql-hackers/2009-07/msg01008.php There's no specific API discussion in either of the referenced threads though - just some vague hand waving and opining about what it should do and how it should look (pretty much all of which is covered by my patch). Oh, and apologies to Jaime who I just noticed had volunteered to work on this :-( -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Tue, Oct 13, 2009 at 1:07 PM, Dave Page <dpage@pgadmin.org> wrote: > > Oh, and apologies to Jaime who I just noticed had volunteered to work > on this :-( > never mind... i get blocked for the ugliness of the libpq api connect functions... and my first attempt to solve that was seriously broken... besides, as Robert mention, because of pooler connections using a GUC is more appropiate... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
On Tue, Oct 13, 2009 at 9:13 PM, Jaime Casanova <jcasanov@systemguards.com.ec> wrote: > On Tue, Oct 13, 2009 at 1:07 PM, Dave Page <dpage@pgadmin.org> wrote: >> >> Oh, and apologies to Jaime who I just noticed had volunteered to work >> on this :-( >> > > never mind... i get blocked for the ugliness of the libpq api connect > functions... > and my first attempt to solve that was seriously broken... Funny - I reverted my first attempt at that bit too. > besides, as Robert mention, because of pooler connections using a GUC > is more appropiate... I'd like both options to be available to the programmer. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page <dpage@pgadmin.org> writes: > On Tue, Oct 13, 2009 at 9:13 PM, Jaime Casanova > <jcasanov@systemguards.com.ec> wrote: >> besides, as Robert mention, because of pooler connections using a GUC >> is more appropiate... > I'd like both options to be available to the programmer. We have several things already that can be fed either from an environment variable or an option in the connection string. Is there any compelling reason why those two mechanisms aren't adequate for this? regards, tom lane
On Tue, Oct 13, 2009 at 10:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> On Tue, Oct 13, 2009 at 9:13 PM, Jaime Casanova >> <jcasanov@systemguards.com.ec> wrote: >>> besides, as Robert mention, because of pooler connections using a GUC >>> is more appropiate... > >> I'd like both options to be available to the programmer. > > We have several things already that can be fed either from an > environment variable or an option in the connection string. > Is there any compelling reason why those two mechanisms aren't > adequate for this? Err, yes - see above. And didn't you also say it was essential to be able to change it after the initial connection (for which the GUC seems like the obvious solution)? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page <dpage@pgadmin.org> writes: > On Tue, Oct 13, 2009 at 10:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> We have several things already that can be fed either from an >> environment variable or an option in the connection string. >> Is there any compelling reason why those two mechanisms aren't >> adequate for this? > Err, yes - see above. And didn't you also say it was essential to be > able to change it after the initial connection (for which the GUC > seems like the obvious solution)? Sure. I'm envisioning that what the env variable or connection option actually does is cause libpq to include a SET command for a GUC variable in the initial connection request packet. Compare, say, PGCLIENTENCODING -> client_encoding. regards, tom lane
On Wed, Oct 14, 2009 at 3:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> On Tue, Oct 13, 2009 at 10:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> We have several things already that can be fed either from an >>> environment variable or an option in the connection string. >>> Is there any compelling reason why those two mechanisms aren't >>> adequate for this? > >> Err, yes - see above. And didn't you also say it was essential to be >> able to change it after the initial connection (for which the GUC >> seems like the obvious solution)? > > Sure. I'm envisioning that what the env variable or connection option > actually does is cause libpq to include a SET command for a GUC > variable in the initial connection request packet. Compare, say, > PGCLIENTENCODING -> client_encoding. Right - I think we're just misunderstanding each other whilst we're saying the same thing. I want the envvar/connection string option as ways to setup the value at connection time, and the use of SET available for later changes. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Oct 13, 2009, at 11:02 AM, Dave Page wrote: > A useful feature found in other DBMSs such as MS SQL Server that has > been requested on these lists a few times, is the ability for a client > application to report its name to the server. I've been following this thread closely and haven't seen mention of including the setting as part of the process name, so a 'ps' (on Unix) would display it. Thoughts? eric
On Wed, Oct 14, 2009 at 5:37 PM, Eric B. Ridge <ebr@tcdi.com> wrote: > On Oct 13, 2009, at 11:02 AM, Dave Page wrote: > >> A useful feature found in other DBMSs such as MS SQL Server that has >> been requested on these lists a few times, is the ability for a client >> application to report its name to the server. > > I've been following this thread closely and haven't seen mention of > including the setting as part of the process name, so a 'ps' (on Unix) would > display it. Thoughts? Isn't that cluttered enough already? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Wed, Oct 14, 2009 at 18:39, Dave Page <dpage@pgadmin.org> wrote: > On Wed, Oct 14, 2009 at 5:37 PM, Eric B. Ridge <ebr@tcdi.com> wrote: >> On Oct 13, 2009, at 11:02 AM, Dave Page wrote: >> >>> A useful feature found in other DBMSs such as MS SQL Server that has >>> been requested on these lists a few times, is the ability for a client >>> application to report its name to the server. >> >> I've been following this thread closely and haven't seen mention of >> including the setting as part of the process name, so a 'ps' (on Unix) would >> display it. Thoughts? > > Isn't that cluttered enough already? +1 for pg_stat_activity but not 'os' output. -- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/
Dave Page <dpage@pgadmin.org> writes: > On Wed, Oct 14, 2009 at 5:37 PM, Eric B. Ridge <ebr@tcdi.com> wrote: >> I've been following this thread closely and haven't seen mention of >> including the setting as part of the process name, so a 'ps' (on Unix) would >> display it. Thoughts? > Isn't that cluttered enough already? Given the security arguments going on in other threads, it occurs to me that putting app names into ps output could be considered positively undesirable --- you could not put anything into the name that you'd not want exposed to everyone on the server machine. Of course, since we're exposing user and database names there anyhow, I'm not sure that this argument carries much weight. But I think it's not totally clear what people might choose to use the appname for, so we ought to consider the possibility that there's some somewhat-sensitive info in it. regards, tom lane
On Oct 14, 2009, at 12:39 PM, Dave Page wrote: > Isn't that cluttered enough already? I find the ps output uninformative. Having it display something that gets generated from my application would start to make it useful. Maybe what I really want is a totally different feature: log_line_prefix, but for process name. eric
On Wed, Oct 14, 2009 at 3:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Sure. I'm envisioning that what the env variable or connection option > actually does is cause libpq to include a SET command for a GUC > variable in the initial connection request packet. Compare, say, > PGCLIENTENCODING -> client_encoding. So you can now do any of the following to set the application name: - Set $PGAPPLICATIONNAME on the client, prior to connection. - Include 'application_name=MyCoolApp' in the PQconnectdb connection string. - Use SET application_name Currently though, pg_dump and psql (and presumably their close friends) use PQsetdbLogin to establish their connection. Would it be preferred if I: a) Added PQsetdbLogin2() with an additional option for the application name (my guess is 'no'). b) Updated the apps to use PQconnectdb c) Something else? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page escreveu: > On Wed, Oct 14, 2009 at 3:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Sure. I'm envisioning that what the env variable or connection option >> actually does is cause libpq to include a SET command for a GUC >> variable in the initial connection request packet. Compare, say, >> PGCLIENTENCODING -> client_encoding. > > So you can now do any of the following to set the application name: > > - Set $PGAPPLICATIONNAME on the client, prior to connection. > - Include 'application_name=MyCoolApp' in the PQconnectdb connection string. > - Use SET application_name > Works for me. > Currently though, pg_dump and psql (and presumably their close > friends) use PQsetdbLogin to establish their connection. Would it be > preferred if I: > > a) Added PQsetdbLogin2() with an additional option for the application > name (my guess is 'no'). > b) Updated the apps to use PQconnectdb > c) Something else? > My prefered option is (b). But it should be a separate patch. -- Euler Taveira de Oliveira http://www.timbira.com/
Dave Page <dpage@pgadmin.org> writes: > a) Added PQsetdbLogin2() with an additional option for the application > name (my guess is 'no'). > b) Updated the apps to use PQconnectdb > c) Something else? a) is absolutely right out. b) is okay from an overall viewpoint but you would find yourself doing something very much like this: http://archives.postgresql.org/message-id/3073cc9b0909141123s7ec779a0h6524ec90a0a81c7@mail.gmail.com So I would suggest d) leave the issue unsolved until some descendant of that patch gets committed, and then use it. There is also e) do nothing, since the environment var and SET options are plenty. Also, I am wondering exactly what you think psql would *do* with the parameter if it had it. If the answer is "force the setting to be 'psql'", that's the wrong answer. IMO you'd really want the environment variable to take precedence over that, if set. But libpq considers the priority to go the other way. regards, tom lane
On Thu, Oct 15, 2009 at 3:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> a) Added PQsetdbLogin2() with an additional option for the application >> name (my guess is 'no'). >> b) Updated the apps to use PQconnectdb >> c) Something else? > > a) is absolutely right out. b) is okay from an overall viewpoint but > you would find yourself doing something very much like this: > http://archives.postgresql.org/message-id/3073cc9b0909141123s7ec779a0h6524ec90a0a81c7@mail.gmail.com > So I would suggest > d) leave the issue unsolved until some descendant of that patch gets > committed, and then use it. Ooh, I like that patch. I'll wait for that. > There is also > e) do nothing, since the environment var and SET options are plenty. > > Also, I am wondering exactly what you think psql would *do* with the > parameter if it had it. If the answer is "force the setting to be > 'psql'", that's the wrong answer. IMO you'd really want the environment > variable to take precedence over that, if set. But libpq considers the > priority to go the other way. Well in the psql case, it could flip that priority itself and only set the value if the environment variable wasn't set - which I agree would seem the right thing to do. On further thought, it would seem reasonable to do the same in the other apps as well, so you could have your backup script do something like "PGAPPLICATIONNAME="Nightly backup" /usr/bin/pg_dump ..." -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page <dpage@pgadmin.org> writes: > On Thu, Oct 15, 2009 at 3:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Also, I am wondering exactly what you think psql would *do* with the >> parameter if it had it. �If the answer is "force the setting to be >> 'psql'", that's the wrong answer. �IMO you'd really want the environment >> variable to take precedence over that, if set. �But libpq considers the >> priority to go the other way. > Well in the psql case, it could flip that priority itself and only set > the value if the environment variable wasn't set - which I agree would > seem the right thing to do. On further thought, it would seem > reasonable to do the same in the other apps as well, so you could have > your backup script do something like "PGAPPLICATIONNAME="Nightly > backup" /usr/bin/pg_dump ..." Hmm. Maybe this is a generic problem. Should libpq offer some sort of help? Maybe a "secondaryappname" parameter that doesn't override the env variable. regards, tom lane
On Thu, Oct 15, 2009 at 3:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> On Thu, Oct 15, 2009 at 3:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Also, I am wondering exactly what you think psql would *do* with the >>> parameter if it had it. If the answer is "force the setting to be >>> 'psql'", that's the wrong answer. IMO you'd really want the environment >>> variable to take precedence over that, if set. But libpq considers the >>> priority to go the other way. > >> Well in the psql case, it could flip that priority itself and only set >> the value if the environment variable wasn't set - which I agree would >> seem the right thing to do. On further thought, it would seem >> reasonable to do the same in the other apps as well, so you could have >> your backup script do something like "PGAPPLICATIONNAME="Nightly >> backup" /usr/bin/pg_dump ..." > > Hmm. Maybe this is a generic problem. Should libpq offer some sort > of help? Maybe a "secondaryappname" parameter that doesn't override > the env variable. is it worth uglifying libpq? All we're talking about is something like: if (!getenv("PGAPPLICATIONNAME")) strncat(connstr, " application_name=psql", sizeof(connstr) - strlen(connstr) - 1); -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page <dpage@pgadmin.org> writes: > On Thu, Oct 15, 2009 at 3:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Hmm. �Maybe this is a generic problem. �Should libpq offer some sort >> of help? �Maybe a "secondaryappname" parameter that doesn't override >> the env variable. > is it worth uglifying libpq? All we're talking about is something like: > if (!getenv("PGAPPLICATIONNAME")) > strncat(connstr, " application_name=psql", sizeof(connstr) - > strlen(connstr) - 1); Well, per earlier discussion we're not really interested in doing this with connection strings, it's going to be some sort of parameter array deal. This approach might require a read-write instead of constant parameter array (depending on how the other patch eventually works out). But the main thing that's bugging me about it is the explicit knowledge on the application's part that there is an environment variable that interacts with this setting. Seems ugly and badly-factored. Another possibility that should be mentioned for the record is that we could special-case the appname parameter inside libpq, so that the environment variable takes precedence over the conn setting instead of the other way round. That seems pretty ugly too, but maybe it's the least bad answer, if we decide that most apps need that precedence. regards, tom lane
On Thu, Oct 15, 2009 at 4:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Another possibility that should be mentioned for the record is that > we could special-case the appname parameter inside libpq, so that the > environment variable takes precedence over the conn setting instead > of the other way round. That seems pretty ugly too, but maybe it's > the least bad answer, if we decide that most apps need that precedence. Looking further, I think this might be quite clean: - Add a precedence flag to PQconninfoOption - In conninfo_parse, in the section that grabs the envvars for empty params, modify the logic to override any existing values if a value is set in the environment and the precedence flag is set for that option. That may be useful in the future for other options, and will certainly be less ugly than special casing one setting. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page <dpage@pgadmin.org> writes: > Looking further, I think this might be quite clean: > - Add a precedence flag to PQconninfoOption > - In conninfo_parse, in the section that grabs the envvars for empty > params, modify the logic to override any existing values if a value is > set in the environment and the precedence flag is set for that option. > That may be useful in the future for other options, and will certainly > be less ugly than special casing one setting. Changing PQconninfoOption would be an ABI break necessitating a soname bump and recompiling all libpq-using applications. If there were a significant probability that we'd have additional options in future that should act this way, it might be worth it. But I don't think there is. The approach with two different conninfo options is really probably the logically cleanest answer short of an ABI break. regards, tom lane
On Tue, Oct 13, 2009 at 4:11 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > >> On the second question, obviously the user can call SET to set a >> value, as I've done for now in psql, however in other DBMSs, it may be >> set in the connection string. My feeling would be to do that, and >> possibly add PQsetApplicationName(PGconn *conn, char *name) and char >> *PQapplicationName(PGconn *conn);. What do others think? >> >> > > > Doing it with a GUC will not be nearly so useful as having it in the wire > protocol, IMNSHO. Just one example: it wouldn't be present in connection > records, because it wouldn't be set yet. I just realised there's a nasty problem with this. In my client application, I can use PQconninfoParse to determine if application_name (or fallback_application_name) are valid connection string options for the version of libpq that I have. However, there is no way that I can see of doing a comparable test in libpq itself, to ensure that the server understands the parameter, so if I connect to an 8.5 server, everything works fine, but if connect to an older server with my new libpq, the connection fails because of the unknown parameter in the startup packet. Am I missing a way around this? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start
Dave Page <dpage@pgadmin.org> writes: > I just realised there's a nasty problem with this. In my client > application, I can use PQconninfoParse to determine if > application_name (or fallback_application_name) are valid connection > string options for the version of libpq that I have. > However, there is no way that I can see of doing a comparable test in > libpq itself, to ensure that the server understands the parameter, so > if I connect to an 8.5 server, everything works fine, but if connect > to an older server with my new libpq, the connection fails because of > the unknown parameter in the startup packet. Hmm, yeah, that's a good point. It seems like you will have to send the appname SET command as a separate packet, after you have gotten the initial response and know what version the server is. Kind of annoying from a performance standpoint, but I believe it won't be too hard to shoehorn into libpq --- it already has a code path for that for older servers, IIRC. regards, tom lane
On Tue, Oct 20, 2009 at 8:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> I just realised there's a nasty problem with this. In my client >> application, I can use PQconninfoParse to determine if >> application_name (or fallback_application_name) are valid connection >> string options for the version of libpq that I have. > >> However, there is no way that I can see of doing a comparable test in >> libpq itself, to ensure that the server understands the parameter, so >> if I connect to an 8.5 server, everything works fine, but if connect >> to an older server with my new libpq, the connection fails because of >> the unknown parameter in the startup packet. > > Hmm, yeah, that's a good point. It seems like you will have to send the > appname SET command as a separate packet, after you have gotten the > initial response and know what version the server is. Kind of annoying > from a performance standpoint, but I believe it won't be too hard to > shoehorn into libpq --- it already has a code path for that for older > servers, IIRC. Yeah - unfortunately that means the connection log messages won't be able to include the appname (I had to tweak ProcessStatupPacket() to deal with it earlier as it was). Should we perhaps change the behaviour of the backend to give a warning only for unknown settings in the startup packet? It doesn't seem beyond the realms of possibility that we might want to add something else in the future, and this will at least mean that in a few releases time it might be reasonably safe to do so. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start
On Wed, 2009-10-14 at 18:44 +0200, Magnus Hagander wrote: > On Wed, Oct 14, 2009 at 18:39, Dave Page <dpage@pgadmin.org> wrote: > > On Wed, Oct 14, 2009 at 5:37 PM, Eric B. Ridge <ebr@tcdi.com> wrote: > >> On Oct 13, 2009, at 11:02 AM, Dave Page wrote: > >> > >>> A useful feature found in other DBMSs such as MS SQL Server that has > >>> been requested on these lists a few times, is the ability for a client > >>> application to report its name to the server. > >> > >> I've been following this thread closely and haven't seen mention of > >> including the setting as part of the process name, so a 'ps' (on Unix) would > >> display it. Thoughts? > > > > Isn't that cluttered enough already? > > +1 for pg_stat_activity but not 'os' output. +1 to output application name in pg_stat_activity and also accessible to pg_stat_statements. We want to be able to set the application name *after* connection, so that session pool users can be more easily identified. Note that this *must* be set *after* connection, not during connection. So for me, the ability to set the application name at connection time is a related but separate feature - and a much less valuable one at that. ISTM much of the complexity discussed relates to this second feature. Let's just concentrate on getting the connection-pool-identification aspect of this done right and then maybe add second part later. -- Simon Riggs www.2ndQuadrant.com
Dave Page wrote: > On Tue, Oct 20, 2009 at 8:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Dave Page <dpage@pgadmin.org> writes: >>> I just realised there's a nasty problem with this. In my client >>> application, I can use PQconninfoParse to determine if >>> application_name (or fallback_application_name) are valid connection >>> string options for the version of libpq that I have. >>> However, there is no way that I can see of doing a comparable test in >>> libpq itself, to ensure that the server understands the parameter, so >>> if I connect to an 8.5 server, everything works fine, but if connect >>> to an older server with my new libpq, the connection fails because of >>> the unknown parameter in the startup packet. >> Hmm, yeah, that's a good point. It seems like you will have to send the >> appname SET command as a separate packet, after you have gotten the >> initial response and know what version the server is. Kind of annoying >> from a performance standpoint, but I believe it won't be too hard to >> shoehorn into libpq --- it already has a code path for that for older >> servers, IIRC. > > Yeah - unfortunately that means the connection log messages won't be > able to include the appname (I had to tweak ProcessStatupPacket() to > deal with it earlier as it was). Looking at the way we process the startup packet in ProcessStartupPacket, there's one dirty hack you could do. As the code stands, if you specify "options" key/value pair more than once, the latter value overrides the first one. If we change that in PG 8.5 so that the values are concatenated instead, you can put app name into the first "options" line, and other options (or an empty string) into the second. Pre-8.5 servers will silently ignore the first line. Another idea is to do something similar to the 'prefer' SSL mode, or if the server doesn't support protocol version 3: Try with the GUC in startup packet first, and if that fails, retry without it. I'm not sure if I like either of those better than the extra SET command, but I thought I'd mention it. > Should we perhaps change the behaviour of the backend to give a > warning only for unknown settings in the startup packet? It doesn't > seem beyond the realms of possibility that we might want to add > something else in the future, and this will at least mean that in a > few releases time it might be reasonably safe to do so. Yeah, sounds like a good idea. But what if there's options that we would *want* to throw an error on if the server doesn't understand them? It would be better if the client could explicitly mark optional arguments somehow. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Wed, Oct 21, 2009 at 10:14 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > ISTM much of the complexity discussed relates to this second feature. > Let's just concentrate on getting the connection-pool-identification > aspect of this done right and then maybe add second part later. That side of the patch works fine already (it's only a GUC after all), as does the during-connection option. The current problem is that old servers will barf if it's set in the startup packet. And yes, the stats part also works :-). -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start
On Wed, 2009-10-21 at 10:19 +0100, Dave Page wrote: > On Wed, Oct 21, 2009 at 10:14 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > > > ISTM much of the complexity discussed relates to this second feature. > > Let's just concentrate on getting the connection-pool-identification > > aspect of this done right and then maybe add second part later. > > That side of the patch works fine already (it's only a GUC after all), > as does the during-connection option. The current problem is that old > servers will barf if it's set in the startup packet. > > And yes, the stats part also works :-). Then I say this feature is good to go. It's much needed. Well done. Strip out the during-connection option for now. Let the startup packet stuff come later, if ever. -- Simon Riggs www.2ndQuadrant.com
On Wed, Oct 21, 2009 at 10:40 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > On Wed, 2009-10-21 at 10:19 +0100, Dave Page wrote: >> On Wed, Oct 21, 2009 at 10:14 AM, Simon Riggs <simon@2ndquadrant.com> wrote: >> >> > ISTM much of the complexity discussed relates to this second feature. >> > Let's just concentrate on getting the connection-pool-identification >> > aspect of this done right and then maybe add second part later. >> >> That side of the patch works fine already (it's only a GUC after all), >> as does the during-connection option. The current problem is that old >> servers will barf if it's set in the startup packet. >> >> And yes, the stats part also works :-). > > Then I say this feature is good to go. It's much needed. Well done. Thanks. > Strip out the during-connection option for now. Let the startup packet > stuff come later, if ever. Either way there's a little work to do - if we forget about the startup packet (which I'd rather not do - I can see the value in having the app name in the connection log messages), I'll still need to tweak things such that libpq can set the GUC post-connection, either using the code Tom mentioned yesterday, or just issuing an explicit SET. I don't see that being a problem though. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start
On Wed, Oct 21, 2009 at 10:14 AM, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote: > Looking at the way we process the startup packet in > ProcessStartupPacket, there's one dirty hack you could do. As the code > stands, if you specify "options" key/value pair more than once, the > latter value overrides the first one. If we change that in PG 8.5 so > that the values are concatenated instead, you can put app name into the > first "options" line, and other options (or an empty string) into the > second. Pre-8.5 servers will silently ignore the first line. Not sure I follow - are you suggesting I set the appname via the backend command line options? Currently I just send the "application_name" as an explicit key/value pair. > Another idea is to do something similar to the 'prefer' SSL mode, or if > the server doesn't support protocol version 3: Try with the GUC in > startup packet first, and if that fails, retry without it. > > I'm not sure if I like either of those better than the extra SET > command, but I thought I'd mention it. The command line sure seems ugly if that's what you meant. Retrying doesn't seem so bad, though it'll still litter server logs with connection errors. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start
On Wed, 2009-10-21 at 10:45 +0100, Dave Page wrote: > On Wed, Oct 21, 2009 at 10:40 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > > On Wed, 2009-10-21 at 10:19 +0100, Dave Page wrote: > >> On Wed, Oct 21, 2009 at 10:14 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > >> > >> > ISTM much of the complexity discussed relates to this second feature. > >> > Let's just concentrate on getting the connection-pool-identification > >> > aspect of this done right and then maybe add second part later. > >> > >> That side of the patch works fine already (it's only a GUC after all), > >> as does the during-connection option. The current problem is that old > >> servers will barf if it's set in the startup packet. > >> > >> And yes, the stats part also works :-). > > > > Then I say this feature is good to go. It's much needed. Well done. > > Thanks. > > > Strip out the during-connection option for now. Let the startup packet > > stuff come later, if ever. > > Either way there's a little work to do - if we forget about the > startup packet (which I'd rather not do - I can see the value in > having the app name in the connection log messages), I'll still need > to tweak things such that libpq can set the GUC post-connection, > either using the code Tom mentioned yesterday, or just issuing an > explicit SET. The SET seems sufficient for me. All interfaces currently support it. The reason we want this is because the app name is not available at connection time. It will only ever be NULL in the connection log messages in the cases where this is most needed. In other cases, the app name is not uniquely associated with a session and can change over time. What value is there in knowing the app name at connection? Could we not just log the app name iff log_connections is enabled? Also, adding something to libpq means we have to alter all the clients and that means they become incompatible with earlier versions. What advantage comes from doing all of that work? Nothing even close to large enough to warrant the pain and delay, AFAICS. -- Simon Riggs www.2ndQuadrant.com
On Wed, Oct 21, 2009 at 11:06 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > The SET seems sufficient for me. All interfaces currently support it. SET alone will not allow what I see as one of the most useful uses of this - consider: PGAPPLICATIONNAME="Nightly backup" pg_dump mydb PGAPPLICATIONNAME="Sensor data import" psql < data.log > The reason we want this is because the app name is not available at > connection time. It will only ever be NULL in the connection log > messages in the cases where this is most needed. In other cases, the app > name is not uniquely associated with a session and can change over time. > What value is there in knowing the app name at connection? Could we not > just log the app name iff log_connections is enabled? It allows you to identify what application or script a connection came from. Sure, you can probably figure that out regardless (from PID and later log messages for example), but it's convenient and avoids the need to correlate multiple log entries to one another. > Also, adding something to libpq means we have to alter all the clients > and that means they become incompatible with earlier versions. What > advantage comes from doing all of that work? Nothing even close to large > enough to warrant the pain and delay, AFAICS. I must be missing something - why do we have to alter the clients? As it stands, they can use SET with whatever libpq they currently have, however if they wish to use the environment or connection string they'll need to update to the new libpq version. Those apps that don't care won't be affected because the libpq API hasn't changed in any way that isn't fully backwards compatible. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start
Dave Page wrote: > On Wed, Oct 21, 2009 at 10:14 AM, Heikki Linnakangas > <heikki.linnakangas@enterprisedb.com> wrote: > >> Looking at the way we process the startup packet in >> ProcessStartupPacket, there's one dirty hack you could do. As the code >> stands, if you specify "options" key/value pair more than once, the >> latter value overrides the first one. If we change that in PG 8.5 so >> that the values are concatenated instead, you can put app name into the >> first "options" line, and other options (or an empty string) into the >> second. Pre-8.5 servers will silently ignore the first line. > > Not sure I follow - are you suggesting I set the appname via the > backend command line options? Yep! -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Wed, 2009-10-21 at 11:20 +0100, Dave Page wrote: > On Wed, Oct 21, 2009 at 11:06 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > > > The SET seems sufficient for me. All interfaces currently support it. > > SET alone will not allow what I see as one of the most useful uses of > this - consider: > > PGAPPLICATIONNAME="Nightly backup" pg_dump mydb > PGAPPLICATIONNAME="Sensor data import" psql < data.log This highlights a different issue. If you wish to pass arbitrary SET parameter(s) to a client then it is difficult to do so. We would be better off solving that generic problem than solving your specific one. Consider PGDEADLOCKTIMEOUT=1 pg_dump mydb PGWORKMEM=32657 psql < data.log Same requirement as setting the appname. Specific code for each parameter is the wrong way to do that. > Also, adding something to libpq means we have to alter all the clients > > and that means they become incompatible with earlier versions. What > > advantage comes from doing all of that work? Nothing even close to large > > enough to warrant the pain and delay, AFAICS. > > I must be missing something - why do we have to alter the clients? As > it stands, they can use SET with whatever libpq they currently have, > however if they wish to use the environment or connection string > they'll need to update to the new libpq version. Those apps that don't > care won't be affected because the libpq API hasn't changed in any way > that isn't fully backwards compatible. If they can use SET, why are we changing libpq? If we are changing libpq, why would we ignore those changes in the clients? (We don't need to change clients, but most clients expose some language specific feature to set things rather than just ignore them and let them be set via libpq). -- Simon Riggs www.2ndQuadrant.com
On Wed, Oct 21, 2009 at 11:49, Dave Page <dpage@pgadmin.org> wrote: >> Another idea is to do something similar to the 'prefer' SSL mode, or if >> the server doesn't support protocol version 3: Try with the GUC in >> startup packet first, and if that fails, retry without it. >> >> I'm not sure if I like either of those better than the extra SET >> command, but I thought I'd mention it. > > The command line sure seems ugly if that's what you meant. Retrying > doesn't seem so bad, though it'll still litter server logs with > connection errors. It'd definitely be good if we can avoid littering the server logs with it. It's bad enough in some SSL situations already :( -- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/
On Wed, Oct 21, 2009 at 12:45, Simon Riggs <simon@2ndquadrant.com> wrote: > On Wed, 2009-10-21 at 11:20 +0100, Dave Page wrote: >> On Wed, Oct 21, 2009 at 11:06 AM, Simon Riggs <simon@2ndquadrant.com> wrote: >> >> > The SET seems sufficient for me. All interfaces currently support it. >> >> SET alone will not allow what I see as one of the most useful uses of >> this - consider: >> >> PGAPPLICATIONNAME="Nightly backup" pg_dump mydb >> PGAPPLICATIONNAME="Sensor data import" psql < data.log > > This highlights a different issue. If you wish to pass arbitrary SET > parameter(s) to a client then it is difficult to do so. We would be > better off solving that generic problem than solving your specific one. > > Consider > > PGDEADLOCKTIMEOUT=1 pg_dump mydb > PGWORKMEM=32657 psql < data.log > > Same requirement as setting the appname. Specific code for each > parameter is the wrong way to do that. PGOPTIONS is the way to do that, no? It can be a bit tricky when you have to deal with quoting, but it is there and it works... >> Also, adding something to libpq means we have to alter all the clients >> > and that means they become incompatible with earlier versions. What >> > advantage comes from doing all of that work? Nothing even close to large >> > enough to warrant the pain and delay, AFAICS. >> >> I must be missing something - why do we have to alter the clients? As >> it stands, they can use SET with whatever libpq they currently have, >> however if they wish to use the environment or connection string >> they'll need to update to the new libpq version. Those apps that don't >> care won't be affected because the libpq API hasn't changed in any way >> that isn't fully backwards compatible. > > If they can use SET, why are we changing libpq? If we are changing > libpq, why would we ignore those changes in the clients? (We don't need > to change clients, but most clients expose some language specific > feature to set things rather than just ignore them and let them be set > via libpq). The idea is to provide a better default than an empty string, I think. -- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/
On Wed, 2009-10-21 at 12:49 +0200, Magnus Hagander wrote: > PGOPTIONS is the way to do that, no? It can be a bit tricky when you > have to deal with quoting, but it is there and it works... Which will work for application name also. -- Simon Riggs www.2ndQuadrant.com
On Wed, Oct 21, 2009 at 12:01 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > On Wed, 2009-10-21 at 12:49 +0200, Magnus Hagander wrote: > >> PGOPTIONS is the way to do that, no? It can be a bit tricky when you >> have to deal with quoting, but it is there and it works... > > Which will work for application name also. In earlier discussion on this, we spoke about having some way of overriding hard-coded values in the client, such that psql could report it's application name, but that could be overridden by a value in the environment as in the examples I gave earlier. This is the exact opposite behaviour of the existing mechanism. The solution implemented was to add 2 connection string options, one of which libpq would only use if the other wasn't set, thus allowing you to hardcode 'fallback_application_name=psql', which could be overriden with PGAPPLICATIONNAME. Overloading this feature on PGOPTIONS not only makes that desired behaviour impossible (at least without propagating the notion of fallback_application_name to the backend), but also potentially makes configuration/scripting more tricky for the user when that variable now need to be reset as otherwise unrelated options are changed. Besides, I really don't see the problem anyway. The patch is already written - I'd rather work out the kink (for which we already have three possible solutions) than rip out 50% of the functionality. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start
Dave Page <dpage@pgadmin.org> writes: > Should we perhaps change the behaviour of the backend to give a > warning only for unknown settings in the startup packet? It's not going to help, unless you first invent a time machine so we can retroactively cause all PG servers that are already in the field to behave that way. > It doesn't > seem beyond the realms of possibility that we might want to add > something else in the future, and this will at least mean that in a > few releases time it might be reasonably safe to do so. This might be a good argument for changing that going forward, but it will be *years* before we can rely on it for anything. regards, tom lane
On Wed, Oct 21, 2009 at 3:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> Should we perhaps change the behaviour of the backend to give a >> warning only for unknown settings in the startup packet? > > It's not going to help, unless you first invent a time machine so > we can retroactively cause all PG servers that are already in the field > to behave that way. > >> It doesn't >> seem beyond the realms of possibility that we might want to add >> something else in the future, and this will at least mean that in a >> few releases time it might be reasonably safe to do so. > > This might be a good argument for changing that going forward, but > it will be *years* before we can rely on it for anything. That's what I meant by 'a few releases' (major, not minor). -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start
On Wed, Oct 21, 2009 at 4:39 PM, Dave Page <dpage@pgadmin.org> wrote: > On Wed, Oct 21, 2009 at 3:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Dave Page <dpage@pgadmin.org> writes: >> This might be a good argument for changing that going forward, but >> it will be *years* before we can rely on it for anything. > > That's what I meant by 'a few releases' (major, not minor). BTW, any thoughts on Heikki's suggestions of hacking about the 'options' value or retrying the connection vs. just doing a SET post-connection in libpq? It's pretty certain that whatever I choose you probably won't like :-p -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start
Dave Page <dpage@pgadmin.org> writes: > BTW, any thoughts on Heikki's suggestions of hacking about the > 'options' value or retrying the connection vs. just doing a SET > post-connection in libpq? It's pretty certain that whatever I choose > you probably won't like :-p The post-connect SET still seems like the best choice to me. It's mildly annoying that that won't help for log_connections messages, but in the big scheme of things that's really not a killer problem. The retry approach is not too bad from a user perspective: it would only actually happen during a server version mismatch, which isn't *that* common. My recollection though is that there's no graceful way to implement a retry in libpq; you'd need a significant amount of new, ugly, special-purpose code, with the complexity rising very fast if there's more than one reason to retry. If you can figure out a clean implementation this one would be okay with me, but I'm dubious that it's worth the work. That options hack was just an ugly hack, I don't like it at all --- mainly because I don't believe that approach scales to solve more than one case either. regards, tom lane
Tom Lane wrote: > That options hack was just an ugly hack, I don't like it at all --- > mainly because I don't believe that approach scales to solve more > than one case either. It does if you hack it even more: don't pass the (first) options directly as command line arguments, but parse it in ProcessStartupPacket into a list of GUC settings. Add the ones that the server version understands into the usual list of GUCs to set, and ignore others. Yeah, ugly as hell, but does scale if used wisely. One possible issue is that there might be 3rd party applications out there that speak the fe/be protocol, like proxies. They might not like an extra options line. I don't think it's a show-stopper, but would need to check at least the popular ones out there. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Wed, Oct 21, 2009 at 12:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> BTW, any thoughts on Heikki's suggestions of hacking about the >> 'options' value or retrying the connection vs. just doing a SET >> post-connection in libpq? It's pretty certain that whatever I choose >> you probably won't like :-p > > The post-connect SET still seems like the best choice to me. > It's mildly annoying that that won't help for log_connections > messages, but in the big scheme of things that's really not a > killer problem. Are we really thinking about interposing an additional server round-trip on every connection for such a marginal feature (to paraphrase yourself)? That doesn't seem like a good trade-off. > The retry approach is not too bad from a user perspective: it would > only actually happen during a server version mismatch, which isn't > *that* common. My recollection though is that there's no graceful way > to implement a retry in libpq; you'd need a significant amount of new, > ugly, special-purpose code, with the complexity rising very fast if > there's more than one reason to retry. If you can figure out a clean > implementation this one would be okay with me, but I'm dubious that > it's worth the work. > > That options hack was just an ugly hack, I don't like it at all --- > mainly because I don't believe that approach scales to solve more > than one case either. Either way, seems like you can try it with all the options and the back up one major release at a time until you find compatibility. It's only O(features), not O(2^features). ...Robert
Robert Haas <robertmhaas@gmail.com> writes: > On Wed, Oct 21, 2009 at 12:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> The post-connect SET still seems like the best choice to me. > Are we really thinking about interposing an additional server > round-trip on every connection for such a marginal feature (to > paraphrase yourself)? That doesn't seem like a good trade-off. Only connections that are actually using the feature. It doesn't bother me that much --- before 7.4 we had *multiple* round trips involved in a connection start, and anyway backend startup is a pretty dang heavyweight operation. If you are concerned about that you should certainly not be advocating multiple connection tries instead. That's a lot of round trips too, plus you are paying repeated fork and backend-startup overhead. regards, tom lane
On Wed, Oct 21, 2009 at 2:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Wed, Oct 21, 2009 at 12:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> The post-connect SET still seems like the best choice to me. > >> Are we really thinking about interposing an additional server >> round-trip on every connection for such a marginal feature (to >> paraphrase yourself)? That doesn't seem like a good trade-off. > > Only connections that are actually using the feature. It doesn't > bother me that much --- before 7.4 we had *multiple* round trips > involved in a connection start, OK, but surely we're not saying that was good? I presume we fixed that for a reason and want it to stay fixed. > and anyway backend startup is a > pretty dang heavyweight operation. > > If you are concerned about that you should certainly not be advocating > multiple connection tries instead. That's a lot of round trips too, > plus you are paying repeated fork and backend-startup overhead. Yeah, I don't like that either, but at least it only happens when you have a server-version mismatch, and even then (if we accept Dave's other suggestion) only for releases prior to 8.5. I'd rather it be dog-slow in a rare situation that I can avoid through proper configuration than moderately slow in a common situation that I can't escape short of not using the feature. Of course, the difficulty of implementation is another issue... ...Robert
Robert Haas <robertmhaas@gmail.com> writes: > On Wed, Oct 21, 2009 at 2:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Only connections that are actually using the feature. �It doesn't >> bother me that much --- before 7.4 we had *multiple* round trips >> involved in a connection start, > OK, but surely we're not saying that was good? I presume we fixed > that for a reason and want it to stay fixed. Well, that was one of multiple things we fixed in the 7.4 protocol version bump. The *right* way to fix this would probably be another protocol bump, but I don't see us doing one now ... there are not enough things broken to justify it, yet. I think that leaving this as a separate SET until such time as that happens is the most reasonable thing to do from a project management standpoint. This feature is a nice-to-have, it's not any kind of "must"; so we should not be boxing ourselves in with weird kluges we'll have to stay compatible with till the end of time in order to make it work slightly better. If you want to avoid an extra round trip, that could probably be managed with some effort within libpq by not waiting for the response to come back after the SET. It'd just need to retain some state to remind itself to discard the first CommandComplete message from the server. I'm not convinced it's worth the trouble though. regards, tom lane