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

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(at)commandprompt(dot)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(at)dunslane(dot)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(at)commandprompt(dot)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(at)dunslane(dot)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(at)dunslane(dot)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(at)pgadmin(dot)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(at)pgadmin(dot)org> writes:
> On Tue, Oct 13, 2009 at 4:06 PM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)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(at)pgadmin(dot)org> writes:
> On Tue, Oct 13, 2009 at 4:34 PM, Andrew Dunstan <andrew(at)dunslane(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)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(at)pgadmin(dot)org> writes:
>
>> On Tue, Oct 13, 2009 at 4:34 PM, Andrew Dunstan <andrew(at)dunslane(dot)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
>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 :)

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
%s is too gigantic of an industry to bend to the whims of reality

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)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 pooler between the
database and application, for example.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)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(at)gmail(dot)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(at)pgadmin(dot)org> writes:
> On Tue, Oct 13, 2009 at 5:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Dave Page <dpage(at)pgadmin(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)org> writes:
>> On Tue, Oct 13, 2009 at 5:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Dave Page <dpage(at)pgadmin(dot)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(at)pgadmin(dot)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(at)systemguards(dot)com(dot)ec> wrote:
> On Tue, Oct 13, 2009 at 1:07 PM, Dave Page <dpage(at)pgadmin(dot)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(at)pgadmin(dot)org> writes:
> On Tue, Oct 13, 2009 at 9:13 PM, Jaime Casanova
> <jcasanov(at)systemguards(dot)com(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)org> writes:
>> On Tue, Oct 13, 2009 at 9:13 PM, Jaime Casanova
>> <jcasanov(at)systemguards(dot)com(dot)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(at)pgadmin(dot)org> writes:
> On Tue, Oct 13, 2009 at 10:25 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)org> writes:
>> On Tue, Oct 13, 2009 at 10:25 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)tcdi(dot)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(at)pgadmin(dot)org> wrote:
> On Wed, Oct 14, 2009 at 5:37 PM, Eric B. Ridge <ebr(at)tcdi(dot)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 Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Dave Page <dpage(at)pgadmin(dot)org> writes:
> On Wed, Oct 14, 2009 at 5:37 PM, Eric B. Ridge <ebr(at)tcdi(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)pgadmin(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)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(at)pgadmin(dot)org> writes:
> On Thu, Oct 15, 2009 at 3:28 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)org> writes:
>> On Thu, Oct 15, 2009 at 3:28 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)pgadmin(dot)org> writes:
> On Thu, Oct 15, 2009 at 3:50 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)pgadmin(dot)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(at)dunslane(dot)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(at)pgadmin(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)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(at)pgadmin(dot)org> wrote:
> > On Wed, Oct 14, 2009 at 5:37 PM, Eric B. Ridge <ebr(at)tcdi(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Dave Page <dpage(at)pgadmin(dot)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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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(dot)linnakangas(at)enterprisedb(dot)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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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(dot)linnakangas(at)enterprisedb(dot)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(at)2ndquadrant(dot)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(at)pgadmin(dot)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 Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Wed, Oct 21, 2009 at 12:45, Simon Riggs <simon(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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 Hagander
Me: 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(at)2ndquadrant(dot)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(at)pgadmin(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)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(at)pgadmin(dot)org> wrote:
> On Wed, Oct 21, 2009 at 3:49 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Dave Page <dpage(at)pgadmin(dot)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(at)pgadmin(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)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(at)gmail(dot)com> writes:
> On Wed, Oct 21, 2009 at 12:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Wed, Oct 21, 2009 at 12:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)gmail(dot)com> writes:
> On Wed, Oct 21, 2009 at 2:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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