Thread: PATCH: pgagent connection string parsing

PATCH: pgagent connection string parsing

From

Thomas Krennwallner

Date:

20 August 2017, 23:17:52

Hi!

I tried to pass the libpq parameter connect_timeout on the pgagent
command-line without success:

% ./pgagent -f -t60 -l2  dbname=xxx user=tkren connect_timeout=5
Sun Aug 20 18:24:26 2017 : DEBUG: Creating primary connection
Sun Aug 20 18:24:26 2017 : ERROR: Primary connection string is not valid!


connInfo::getConnectionString() misspelled this parameter as
connection_timeout, which subsequently breaks the PQconnectdb() call
in DBconn::Connect():

% ./pgagent -f -t60 -l2  dbname=xxx user=tkren connection_timeout=5
Sun Aug 20 18:24:34 2017 : DEBUG: Creating primary connection
Sun Aug 20 18:24:34 2017 : DEBUG: Connection Information:
Sun Aug 20 18:24:34 2017 : DEBUG:      user         : tkren
Sun Aug 20 18:24:34 2017 : DEBUG:      port         : 0
Sun Aug 20 18:24:34 2017 : DEBUG:      host         :
Sun Aug 20 18:24:34 2017 : DEBUG:      dbname       : xxx
Sun Aug 20 18:24:34 2017 : DEBUG:      password     :
Sun Aug 20 18:24:34 2017 : DEBUG:      conn timeout : 5
Sun Aug 20 18:24:34 2017 : DEBUG: Connection Information:
Sun Aug 20 18:24:34 2017 : DEBUG:      user         : tkren
Sun Aug 20 18:24:34 2017 : DEBUG:      port         : 0
Sun Aug 20 18:24:34 2017 : DEBUG:      host         :
Sun Aug 20 18:24:34 2017 : DEBUG:      dbname       : xxx
Sun Aug 20 18:24:34 2017 : DEBUG:      password     :
Sun Aug 20 18:24:34 2017 : DEBUG:      conn timeout : 5
Sun Aug 20 18:24:34 2017 : DEBUG: Creating DB connection: user=tkren connection_timeout=5 dbname=xxx
Sun Aug 20 18:24:34 2017 : WARNING: Couldn't create the primary connection (attempt 1): invalid connection option
"connection_timeout"
Sun Aug 20 18:24:34 2017 : DEBUG: Clearing all connections
Sun Aug 20 18:24:34 2017 : DEBUG: Connection stats: total - 1, free - 0, deleted - 1


During my code inspection, I realized that pgagent does not support
application_name and other parameters from libpq:

% ./pgagent -f -t60 -l2  dbname=xxx user=tkren application_name=abc
Sun Aug 20 18:31:19 2017 : DEBUG: Creating primary connection
Sun Aug 20 18:31:19 2017 : ERROR: Primary connection string is not valid!

See also this thread on pgadmin-support:
https://www.postgresql.org/message-id/559AC825.6010708%40agliodbs.com

Using PGAPPNAME would be an option, but we use pgagent on Windows as
well, and this makes it unnecessarily hard to set environment
variables for services.  It would be also be nice to have support for
application_name on the jobstep level, but PGAPPNAME can only be set
globally.

To fix this issue, I've refactored connInfo and DBconn and use of
PQconninfoParse() for connection string parsing.  With the attached
patch applied, pgagent supports both vanilla keyword/value connection
strings and postgresql:// URIs as command-line options as well as
stored in the jstconnstr column of pgagent.pga_jobstep.  We now
support all parameter keywords from libpq, and fix the aforementioned
misspelled connect_timeout parameter.


In a patched pgagent, we can now do the following.  With the jobsteps
shown below that are part of a job running every minute,

xxx=# select * from pgagent.pga_jobstep;
 jstid | jstjobid |         jstname         | jstdesc | jstenabled | jstkind |       jstcode       |
jstconnstr                  | jstdbname | jstonerror | jscnextrun
 

-------+----------+-------------------------+---------+------------+---------+---------------------+------------------------------------------------+-----------+------------+------------
     2 |        1 | pg_sleep(10) local      |         | t          | s       | select pg_sleep(10) |
                           | xxx       | f          |
 
     3 |        1 | pg_sleep(10) remote uri |         | t          | s       | select pg_sleep(10) |
postgresql://tkren@localhost:5432/xxx         |           | f          |
 
     1 |        1 | pg_sleep(10) remote     |         | t          | s       | select pg_sleep(10) | user=tkren
host=localhostport=5432 dbname=xxx |           | f          |
 
(3 rows)

we can run pgagent and get the following trace.  Note that connection
pooling works as intended with postgresql:// URIs and keyword/value
connection strings.

% ./pgagent -f -t60 -l2  dbname=xxx user=tkren application_name=xyz
Sun Aug 20 19:03:01 2017 : DEBUG: Creating primary connection
Sun Aug 20 19:03:01 2017 : DEBUG: Parsing connection information: dbname=xxx user=tkren application_name=xyz
Sun Aug 20 19:03:01 2017 : DEBUG:       Database-User: tkren
Sun Aug 20 19:03:01 2017 : DEBUG:       Database-Name: xxx
Sun Aug 20 19:03:01 2017 : DEBUG:       Application-Name: xyz
Sun Aug 20 19:03:01 2017 : DEBUG: Creating DB connection: 'user=tkren dbname=xxx application_name=xyz'
Sun Aug 20 19:03:01 2017 : DEBUG: Database sanity check
Sun Aug 20 19:03:01 2017 : DEBUG: Clearing zombies
Sun Aug 20 19:03:01 2017 : DEBUG: Checking for jobs to run
Sun Aug 20 19:03:01 2017 : DEBUG: Creating job thread for job 1
Sun Aug 20 19:03:01 2017 : DEBUG: Creating DB connection: 'user=tkren dbname=xxx application_name=xyz'
Sun Aug 20 19:03:01 2017 : DEBUG: Allocating new connection 'user=tkren dbname=xxx application_name=xyz' to database
xxx
Sun Aug 20 19:03:01 2017 : DEBUG: Starting job: 1
Sun Aug 20 19:03:01 2017 : DEBUG: Sleeping...
Sun Aug 20 19:03:01 2017 : DEBUG: Parsing connection information: user=tkren dbname=xxx application_name=xyz
dbname=xxx
Sun Aug 20 19:03:01 2017 : DEBUG:       Database-User: tkren
Sun Aug 20 19:03:01 2017 : DEBUG:       Database-Name: xxx
Sun Aug 20 19:03:01 2017 : DEBUG:       Application-Name: xyz
Sun Aug 20 19:03:01 2017 : DEBUG: Creating DB connection: 'user=tkren dbname=xxx application_name=xyz'
Sun Aug 20 19:03:01 2017 : DEBUG: Allocating new connection 'user=tkren dbname=xxx application_name=xyz' to database
xxx
Sun Aug 20 19:03:01 2017 : DEBUG: Executing SQL step 2 (part of job 1)
Sun Aug 20 19:03:11 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx application_name=xyz' to database xxx
Sun Aug 20 19:03:11 2017 : DEBUG: Parsing connection information: user=tkren host=localhost port=5432 dbname=xxx
Sun Aug 20 19:03:11 2017 : DEBUG:       Database-User: tkren
Sun Aug 20 19:03:11 2017 : DEBUG:       Database-Name: xxx
Sun Aug 20 19:03:11 2017 : DEBUG:       Database-Host: localhost
Sun Aug 20 19:03:11 2017 : DEBUG:       Database-Port: 5432
Sun Aug 20 19:03:11 2017 : DEBUG: Creating DB connection: 'user=tkren dbname=xxx host=localhost port=5432'
Sun Aug 20 19:03:11 2017 : DEBUG: Allocating new connection 'user=tkren dbname=xxx host=localhost port=5432' to
databasexxx
 
Sun Aug 20 19:03:11 2017 : DEBUG: Executing SQL step 1 (part of job 1)
Sun Aug 20 19:03:21 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx host=localhost port=5432' to database
xxx
Sun Aug 20 19:03:21 2017 : DEBUG: Parsing connection information: postgresql://tkren@localhost:5432/xxx
Sun Aug 20 19:03:21 2017 : DEBUG:       Database-User: tkren
Sun Aug 20 19:03:21 2017 : DEBUG:       Database-Name: xxx
Sun Aug 20 19:03:21 2017 : DEBUG:       Database-Host: localhost
Sun Aug 20 19:03:21 2017 : DEBUG:       Database-Port: 5432
Sun Aug 20 19:03:21 2017 : DEBUG: Allocating existing connection 'user=tkren dbname=xxx host=localhost port=5432' to
databasexxx
 
Sun Aug 20 19:03:21 2017 : DEBUG: Executing SQL step 3 (part of job 1)
Sun Aug 20 19:03:31 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx host=localhost port=5432' to database
xxx
Sun Aug 20 19:03:32 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx application_name=xyz' to database xxx
Sun Aug 20 19:03:32 2017 : DEBUG: Completed job: 1
Sun Aug 20 19:03:32 2017 : DEBUG: Destroying job thread for job 1
Sun Aug 20 19:04:01 2017 : DEBUG: Checking for jobs to run
Sun Aug 20 19:04:01 2017 : DEBUG: Creating job thread for job 1
Sun Aug 20 19:04:01 2017 : DEBUG: Allocating existing connection 'user=tkren dbname=xxx application_name=xyz' to
databasexxx
 
Sun Aug 20 19:04:01 2017 : DEBUG: Starting job: 1
Sun Aug 20 19:04:01 2017 : DEBUG: Sleeping...
Sun Aug 20 19:04:01 2017 : DEBUG: Allocating existing connection 'user=tkren dbname=xxx application_name=xyz' to
databasexxx
 
Sun Aug 20 19:04:01 2017 : DEBUG: Executing SQL step 2 (part of job 1)
Sun Aug 20 19:04:11 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx application_name=xyz' to database xxx
Sun Aug 20 19:04:11 2017 : DEBUG: Parsing connection information: user=tkren host=localhost port=5432 dbname=xxx
Sun Aug 20 19:04:11 2017 : DEBUG:       Database-User: tkren
Sun Aug 20 19:04:11 2017 : DEBUG:       Database-Name: xxx
Sun Aug 20 19:04:11 2017 : DEBUG:       Database-Host: localhost
Sun Aug 20 19:04:11 2017 : DEBUG:       Database-Port: 5432
Sun Aug 20 19:04:11 2017 : DEBUG: Allocating existing connection 'user=tkren dbname=xxx host=localhost port=5432' to
databasexxx
 
Sun Aug 20 19:04:11 2017 : DEBUG: Executing SQL step 1 (part of job 1)
Sun Aug 20 19:04:21 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx host=localhost port=5432' to database
xxx
Sun Aug 20 19:04:21 2017 : DEBUG: Parsing connection information: postgresql://tkren@localhost:5432/xxx
Sun Aug 20 19:04:21 2017 : DEBUG:       Database-User: tkren
Sun Aug 20 19:04:21 2017 : DEBUG:       Database-Name: xxx
Sun Aug 20 19:04:21 2017 : DEBUG:       Database-Host: localhost
Sun Aug 20 19:04:21 2017 : DEBUG:       Database-Port: 5432
Sun Aug 20 19:04:21 2017 : DEBUG: Allocating existing connection 'user=tkren dbname=xxx host=localhost port=5432' to
databasexxx
 
Sun Aug 20 19:04:21 2017 : DEBUG: Executing SQL step 3 (part of job 1)
Sun Aug 20 19:04:31 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx host=localhost port=5432' to database
xxx
Sun Aug 20 19:04:32 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx application_name=xyz' to database xxx
Sun Aug 20 19:04:32 2017 : DEBUG: Completed job: 1
Sun Aug 20 19:04:32 2017 : DEBUG: Destroying job thread for job 1
[...]


TK

Attachment

0001-connection-info-refactoring.patch

Re: PATCH: pgagent connection string parsing

From

Ashesh Vashi

Date:

21 August 2017, 07:20:03

On Aug 20, 2017 22:48, "Thomas Krennwallner" <tk+pgsql@postsubmeta.net> wrote:

Hi!

I tried to pass the libpq parameter connect_timeout on the pgagent
command-line without success:

% ./pgagent -f -t60 -l2 dbname=xxx user=tkren connect_timeout=5
Sun Aug 20 18:24:26 2017 : DEBUG: Creating primary connection
Sun Aug 20 18:24:26 2017 : ERROR: Primary connection string is not valid!

connInfo::getConnectionString() misspelled this parameter as
connection_timeout, which subsequently breaks the PQconnectdb() call
in DBconn::Connect():

% ./pgagent -f -t60 -l2 dbname=xxx user=tkren connection_timeout=5
Sun Aug 20 18:24:34 2017 : DEBUG: Creating primary connection
Sun Aug 20 18:24:34 2017 : DEBUG: Connection Information:
Sun Aug 20 18:24:34 2017 : DEBUG: user : tkren
Sun Aug 20 18:24:34 2017 : DEBUG: port : 0
Sun Aug 20 18:24:34 2017 : DEBUG: host :
Sun Aug 20 18:24:34 2017 : DEBUG: dbname : xxx
Sun Aug 20 18:24:34 2017 : DEBUG: password :
Sun Aug 20 18:24:34 2017 : DEBUG: conn timeout : 5
Sun Aug 20 18:24:34 2017 : DEBUG: Connection Information:
Sun Aug 20 18:24:34 2017 : DEBUG: user : tkren
Sun Aug 20 18:24:34 2017 : DEBUG: port : 0
Sun Aug 20 18:24:34 2017 : DEBUG: host :
Sun Aug 20 18:24:34 2017 : DEBUG: dbname : xxx
Sun Aug 20 18:24:34 2017 : DEBUG: password :
Sun Aug 20 18:24:34 2017 : DEBUG: conn timeout : 5
Sun Aug 20 18:24:34 2017 : DEBUG: Creating DB connection: user=tkren connection_timeout=5 dbname=xxx
Sun Aug 20 18:24:34 2017 : WARNING: Couldn't create the primary connection (attempt 1): invalid connection option "connection_timeout"

This suggests pgAgent is not using the latest version of libpq.

Please use LD_LIBRARY_PATH environment to inform pgAgent to use the libpq, it is compiled with.

-- Thanks,

Ashesh Vashi

-- Thanks, Ashesh

Sun Aug 20 18:24:34 2017 : DEBUG: Clearing all connections
Sun Aug 20 18:24:34 2017 : DEBUG: Connection stats: total - 1, free - 0, deleted - 1

During my code inspection, I realized that pgagent does not support
application_name and other parameters from libpq:

% ./pgagent -f -t60 -l2 dbname=xxx user=tkren application_name=abc
Sun Aug 20 18:31:19 2017 : DEBUG: Creating primary connection
Sun Aug 20 18:31:19 2017 : ERROR: Primary connection string is not valid!

See also this thread on pgadmin-support:
https://www.postgresql.org/message-id/559AC825.6010708%40agliodbs.com

Using PGAPPNAME would be an option, but we use pgagent on Windows as
well, and this makes it unnecessarily hard to set environment
variables for services. It would be also be nice to have support for
application_name on the jobstep level, but PGAPPNAME can only be set
globally.

To fix this issue, I've refactored connInfo and DBconn and use of
PQconninfoParse() for connection string parsing. With the attached
patch applied, pgagent supports both vanilla keyword/value connection
strings and postgresql:// URIs as command-line options as well as
stored in the jstconnstr column of pgagent.pga_jobstep. We now
support all parameter keywords from libpq, and fix the aforementioned
misspelled connect_timeout parameter.

In a patched pgagent, we can now do the following. With the jobsteps
shown below that are part of a job running every minute,

xxx=# select * from pgagent.pga_jobstep;
jstid | jstjobid | jstname | jstdesc | jstenabled | jstkind | jstcode | jstconnstr | jstdbname | jstonerror | jscnextrun
-------+----------+-------------------------+---------+------------+---------+---------------------+------------------------------------------------+-----------+------------+------------
2 | 1 | pg_sleep(10) local | | t | s | select pg_sleep(10) | | xxx | f |
3 | 1 | pg_sleep(10) remote uri | | t | s | select pg_sleep(10) | postgresql://tkren@localhost:5432/xxx | | f |
1 | 1 | pg_sleep(10) remote | | t | s | select pg_sleep(10) | user=tkren host=localhost port=5432 dbname=xxx | | f |
(3 rows)

we can run pgagent and get the following trace. Note that connection
pooling works as intended with postgresql:// URIs and keyword/value
connection strings.

% ./pgagent -f -t60 -l2 dbname=xxx user=tkren application_name=xyz
Sun Aug 20 19:03:01 2017 : DEBUG: Creating primary connection
Sun Aug 20 19:03:01 2017 : DEBUG: Parsing connection information: dbname=xxx user=tkren application_name=xyz
Sun Aug 20 19:03:01 2017 : DEBUG: Database-User: tkren
Sun Aug 20 19:03:01 2017 : DEBUG: Database-Name: xxx
Sun Aug 20 19:03:01 2017 : DEBUG: Application-Name: xyz
Sun Aug 20 19:03:01 2017 : DEBUG: Creating DB connection: 'user=tkren dbname=xxx application_name=xyz'
Sun Aug 20 19:03:01 2017 : DEBUG: Database sanity check
Sun Aug 20 19:03:01 2017 : DEBUG: Clearing zombies
Sun Aug 20 19:03:01 2017 : DEBUG: Checking for jobs to run
Sun Aug 20 19:03:01 2017 : DEBUG: Creating job thread for job 1
Sun Aug 20 19:03:01 2017 : DEBUG: Creating DB connection: 'user=tkren dbname=xxx application_name=xyz'
Sun Aug 20 19:03:01 2017 : DEBUG: Allocating new connection 'user=tkren dbname=xxx application_name=xyz' to database xxx
Sun Aug 20 19:03:01 2017 : DEBUG: Starting job: 1
Sun Aug 20 19:03:01 2017 : DEBUG: Sleeping...
Sun Aug 20 19:03:01 2017 : DEBUG: Parsing connection information: user=tkren dbname=xxx application_name=xyz dbname=xxx
Sun Aug 20 19:03:01 2017 : DEBUG: Database-User: tkren
Sun Aug 20 19:03:01 2017 : DEBUG: Database-Name: xxx
Sun Aug 20 19:03:01 2017 : DEBUG: Application-Name: xyz
Sun Aug 20 19:03:01 2017 : DEBUG: Creating DB connection: 'user=tkren dbname=xxx application_name=xyz'
Sun Aug 20 19:03:01 2017 : DEBUG: Allocating new connection 'user=tkren dbname=xxx application_name=xyz' to database xxx
Sun Aug 20 19:03:01 2017 : DEBUG: Executing SQL step 2 (part of job 1)
Sun Aug 20 19:03:11 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx application_name=xyz' to database xxx
Sun Aug 20 19:03:11 2017 : DEBUG: Parsing connection information: user=tkren host=localhost port=5432 dbname=xxx
Sun Aug 20 19:03:11 2017 : DEBUG: Database-User: tkren
Sun Aug 20 19:03:11 2017 : DEBUG: Database-Name: xxx
Sun Aug 20 19:03:11 2017 : DEBUG: Database-Host: localhost
Sun Aug 20 19:03:11 2017 : DEBUG: Database-Port: 5432
Sun Aug 20 19:03:11 2017 : DEBUG: Creating DB connection: 'user=tkren dbname=xxx host=localhost port=5432'
Sun Aug 20 19:03:11 2017 : DEBUG: Allocating new connection 'user=tkren dbname=xxx host=localhost port=5432' to database xxx
Sun Aug 20 19:03:11 2017 : DEBUG: Executing SQL step 1 (part of job 1)
Sun Aug 20 19:03:21 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx host=localhost port=5432' to database xxx
Sun Aug 20 19:03:21 2017 : DEBUG: Parsing connection information: postgresql://tkren@localhost:5432/xxx
Sun Aug 20 19:03:21 2017 : DEBUG: Database-User: tkren
Sun Aug 20 19:03:21 2017 : DEBUG: Database-Name: xxx
Sun Aug 20 19:03:21 2017 : DEBUG: Database-Host: localhost
Sun Aug 20 19:03:21 2017 : DEBUG: Database-Port: 5432
Sun Aug 20 19:03:21 2017 : DEBUG: Allocating existing connection 'user=tkren dbname=xxx host=localhost port=5432' to database xxx
Sun Aug 20 19:03:21 2017 : DEBUG: Executing SQL step 3 (part of job 1)
Sun Aug 20 19:03:31 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx host=localhost port=5432' to database xxx
Sun Aug 20 19:03:32 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx application_name=xyz' to database xxx
Sun Aug 20 19:03:32 2017 : DEBUG: Completed job: 1
Sun Aug 20 19:03:32 2017 : DEBUG: Destroying job thread for job 1
Sun Aug 20 19:04:01 2017 : DEBUG: Checking for jobs to run
Sun Aug 20 19:04:01 2017 : DEBUG: Creating job thread for job 1
Sun Aug 20 19:04:01 2017 : DEBUG: Allocating existing connection 'user=tkren dbname=xxx application_name=xyz' to database xxx
Sun Aug 20 19:04:01 2017 : DEBUG: Starting job: 1
Sun Aug 20 19:04:01 2017 : DEBUG: Sleeping...
Sun Aug 20 19:04:01 2017 : DEBUG: Allocating existing connection 'user=tkren dbname=xxx application_name=xyz' to database xxx
Sun Aug 20 19:04:01 2017 : DEBUG: Executing SQL step 2 (part of job 1)
Sun Aug 20 19:04:11 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx application_name=xyz' to database xxx
Sun Aug 20 19:04:11 2017 : DEBUG: Parsing connection information: user=tkren host=localhost port=5432 dbname=xxx
Sun Aug 20 19:04:11 2017 : DEBUG: Database-User: tkren
Sun Aug 20 19:04:11 2017 : DEBUG: Database-Name: xxx
Sun Aug 20 19:04:11 2017 : DEBUG: Database-Host: localhost
Sun Aug 20 19:04:11 2017 : DEBUG: Database-Port: 5432
Sun Aug 20 19:04:11 2017 : DEBUG: Allocating existing connection 'user=tkren dbname=xxx host=localhost port=5432' to database xxx
Sun Aug 20 19:04:11 2017 : DEBUG: Executing SQL step 1 (part of job 1)
Sun Aug 20 19:04:21 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx host=localhost port=5432' to database xxx
Sun Aug 20 19:04:21 2017 : DEBUG: Parsing connection information: postgresql://tkren@localhost:5432/xxx
Sun Aug 20 19:04:21 2017 : DEBUG: Database-User: tkren
Sun Aug 20 19:04:21 2017 : DEBUG: Database-Name: xxx
Sun Aug 20 19:04:21 2017 : DEBUG: Database-Host: localhost
Sun Aug 20 19:04:21 2017 : DEBUG: Database-Port: 5432
Sun Aug 20 19:04:21 2017 : DEBUG: Allocating existing connection 'user=tkren dbname=xxx host=localhost port=5432' to database xxx
Sun Aug 20 19:04:21 2017 : DEBUG: Executing SQL step 3 (part of job 1)
Sun Aug 20 19:04:31 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx host=localhost port=5432' to database xxx
Sun Aug 20 19:04:32 2017 : DEBUG: Returning connection 'user=tkren dbname=xxx application_name=xyz' to database xxx
Sun Aug 20 19:04:32 2017 : DEBUG: Completed job: 1
Sun Aug 20 19:04:32 2017 : DEBUG: Destroying job thread for job 1
[...]

TK

Re: PATCH: pgagent connection string parsing

From

Thomas Krennwallner

Date:

21 August 2017, 11:22:03

On Mon Aug 21, 2017 06:50:03AM +0530, Ashesh Vashi wrote:
>    On Aug 20, 2017 22:48, "Thomas Krennwallner" <[1]tk+pgsql@postsubmeta.net>
>    wrote:
> 
>      % ./pgagent -f -t60 -l2  dbname=xxx user=tkren connection_timeout=5
[...]
>      Sun Aug 20 18:24:34 2017 : DEBUG: Creating DB connection: user=tkren connection_timeout=5 dbname=xxx
>      Sun Aug 20 18:24:34 2017 : WARNING: Couldn't create the primary connection (attempt 1): invalid connection
option"connection_timeout"
 
> 
>    This suggests pgAgent is not using the latest version of libpq.
>    Please use LD_LIBRARY_PATH environment to inform pgAgent to use the libpq,
>    it is compiled with.
>    -- Thanks,
>    Ashesh Vashi
>    -- Thanks, Ashesh

Unfortunately, this does not work. On a current Debian sid system,
I've compiled pgagent from source and get
% ldd ./pgagent[...]    libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5 (0x00007ff531382000)[...]


The system has libpq 9.6.4 installed:
% apt-cache show libpq5Package: libpq5Source: postgresql-9.6Version: 9.6.4-1[...]


If I run pgagent from the Debian package (same ldd linkage), I get the
same error:
% /usr/bin/pgagent -f -t60 -l2  dbname=xxx user=tkren connection_timeout=5[...]Mon Aug 21 06:51:04 2017 : DEBUG:
CreatingDB connection: user=tkren connection_timeout=5 dbname=xxxMon Aug 21 06:51:04 2017 : WARNING: Couldn't create
theprimary connection (attempt 1): invalid connection option "connection_timeout"[...]
 


But I don't get why connection_timeout should work at all, the
libpq documentation 
https://www.postgresql.org/docs/9.6/static/libpq-connect.html#LIBPQ-CONNECT-CONNECT-TIMEOUT
states that the keyword is
connect_timeout
    Maximum wait for connection, in seconds (write as a decimal    integer string). Zero or not specified means wait
indefinitely.It    is not recommended to use a timeout of less than 2 seconds.
 


libpq.so seems to only mention connect_timeout:
% strings /usr/lib/x86_64-linux-gnu/libpq.so.5  | egrep 'connect(ion)?_timeout'connect_timeout


The libpq source implements it in src/interfaces/libpq/fe-connect.c,
but after a quick check I didn't see a special treatment for
connection_timeout:
    {"connect_timeout", "PGCONNECT_TIMEOUT", NULL, NULL,        "Connect-timeout", "", 10,    /* strlen(INT32_MAX) ==
10*/    offsetof(struct pg_conn, connect_timeout)},
 


And psql dislikes connection_timeout as well:
% psql 'dbname=xxx user=tkren connection_timeout=5'psql: invalid connection option "connection_timeout"
% psql 'dbname=xxx user=tkren connect_timeout=5'psql (9.6.4)Type "help" for help.
xxx=#


TK

Re: PATCH: pgagent connection string parsing

From

Ashesh Vashi

Date:

21 August 2017, 11:24:45

On Aug 21, 2017 10:52, "Thomas Krennwallner" <tk+pgsql@postsubmeta.net> wrote:

On Mon Aug 21, 2017 06:50:03AM +0530, Ashesh Vashi wrote:
> On Aug 20, 2017 22:48, "Thomas Krennwallner" <[1]tk+pgsql@postsubmeta.net>
> wrote:
>
> % ./pgagent -f -t60 -l2 dbname=xxx user=tkren connection_timeout=5
[...]
> Sun Aug 20 18:24:34 2017 : DEBUG: Creating DB connection: user=tkren connection_timeout=5 dbname=xxx
> Sun Aug 20 18:24:34 2017 : WARNING: Couldn't create the primary connection (attempt 1): invalid connection option "connection_timeout"
>
> This suggests pgAgent is not using the latest version of libpq.
> Please use LD_LIBRARY_PATH environment to inform pgAgent to use the libpq,
> it is compiled with.
> -- Thanks,
> Ashesh Vashi
> -- Thanks, Ashesh

Unfortunately, this does not work. On a current Debian sid system,
I've compiled pgagent from source and get

% ldd ./pgagent
[...]
libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5 (0x00007ff531382000)
[...]

The system has libpq 9.6.4 installed:

% apt-cache show libpq5
Package: libpq5
Source: postgresql-9.6
Version: 9.6.4-1
[...]

You may want set the rpath manually using chrpath utility for testing.

-- Thanks,

Ashesh Vashi

If I run pgagent from the Debian package (same ldd linkage), I get the
same error:

% /usr/bin/pgagent -f -t60 -l2 dbname=xxx user=tkren connection_timeout=5
[...]
Mon Aug 21 06:51:04 2017 : DEBUG: Creating DB connection: user=tkren connection_timeout=5 dbname=xxx
Mon Aug 21 06:51:04 2017 : WARNING: Couldn't create the primary connection (attempt 1): invalid connection option "connection_timeout"
[...]

But I don't get why connection_timeout should work at all, the
libpq documentation
https://www.postgresql.org/docs/9.6/static/libpq-connect.html#LIBPQ-CONNECT-CONNECT-TIMEOUT
states that the keyword is

connect_timeout

Maximum wait for connection, in seconds (write as a decimal
integer string). Zero or not specified means wait indefinitely. It
is not recommended to use a timeout of less than 2 seconds.

libpq.so seems to only mention connect_timeout:

% strings /usr/lib/x86_64-linux-gnu/libpq.so.5 | egrep 'connect(ion)?_timeout'
connect_timeout

The libpq source implements it in src/interfaces/libpq/fe-connect.c,
but after a quick check I didn't see a special treatment for
connection_timeout:

{"connect_timeout", "PGCONNECT_TIMEOUT", NULL, NULL,
"Connect-timeout", "", 10, /* strlen(INT32_MAX) == 10 */
offsetof(struct pg_conn, connect_timeout)},

And psql dislikes connection_timeout as well:

% psql 'dbname=xxx user=tkren connection_timeout=5'
psql: invalid connection option "connection_timeout"

% psql 'dbname=xxx user=tkren connect_timeout=5'
psql (9.6.4)
Type "help" for help.

xxx=#

TK

Re: PATCH: pgagent connection string parsing

From

Dave Page

Date:

21 August 2017, 14:11:02

On Mon, Aug 21, 2017 at 6:24 AM, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:

On Aug 21, 2017 10:52, "Thomas Krennwallner" <tk+pgsql@postsubmeta.net> wrote:
On Mon Aug 21, 2017 06:50:03AM +0530, Ashesh Vashi wrote:
> On Aug 20, 2017 22:48, "Thomas Krennwallner" <[1]tk+pgsql@postsubmeta.net>
> wrote:
>
> % ./pgagent -f -t60 -l2 dbname=xxx user=tkren connection_timeout=5
[...]
> Sun Aug 20 18:24:34 2017 : DEBUG: Creating DB connection: user=tkren connection_timeout=5 dbname=xxx
> Sun Aug 20 18:24:34 2017 : WARNING: Couldn't create the primary connection (attempt 1): invalid connection option "connection_timeout"
>
> This suggests pgAgent is not using the latest version of libpq.
> Please use LD_LIBRARY_PATH environment to inform pgAgent to use the libpq,
> it is compiled with.
> -- Thanks,
> Ashesh Vashi
> -- Thanks, Ashesh

Unfortunately, this does not work. On a current Debian sid system,
I've compiled pgagent from source and get

% ldd ./pgagent
[...]
libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5 (0x00007ff531382000)
[...]

The system has libpq 9.6.4 installed:

% apt-cache show libpq5
Package: libpq5
Source: postgresql-9.6
Version: 9.6.4-1
[...]
You may want set the rpath manually using chrpath utility for testing.

Regardless of that, connection.cpp does need to be taught the correct name for connect_timeout, as well as the additional appname related parameters right?

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: PATCH: pgagent connection string parsing

From

Ashesh Vashi

Date:

21 August 2017, 14:46:35

On Mon, Aug 21, 2017 at 1:41 PM, Dave Page <dpage@pgadmin.org> wrote:

On Mon, Aug 21, 2017 at 6:24 AM, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:

On Aug 21, 2017 10:52, "Thomas Krennwallner" <tk+pgsql@postsubmeta.net> wrote:
On Mon Aug 21, 2017 06:50:03AM +0530, Ashesh Vashi wrote:
> On Aug 20, 2017 22:48, "Thomas Krennwallner" <[1]tk+pgsql@postsubmeta.net>
> wrote:
>
> % ./pgagent -f -t60 -l2 dbname=xxx user=tkren connection_timeout=5
[...]
> Sun Aug 20 18:24:34 2017 : DEBUG: Creating DB connection: user=tkren connection_timeout=5 dbname=xxx
> Sun Aug 20 18:24:34 2017 : WARNING: Couldn't create the primary connection (attempt 1): invalid connection option "connection_timeout"
>
> This suggests pgAgent is not using the latest version of libpq.
> Please use LD_LIBRARY_PATH environment to inform pgAgent to use the libpq,
> it is compiled with.
> -- Thanks,
> Ashesh Vashi
> -- Thanks, Ashesh

Unfortunately, this does not work. On a current Debian sid system,
I've compiled pgagent from source and get

% ldd ./pgagent
[...]
libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5 (0x00007ff531382000)
[...]

The system has libpq 9.6.4 installed:

% apt-cache show libpq5
Package: libpq5
Source: postgresql-9.6
Version: 9.6.4-1
[...]
You may want set the rpath manually using chrpath utility for testing.

Regardless of that, connection.cpp does need to be taught the correct name for connect_timeout, as well as the additional appname related parameters right?

True.

Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Company

http://www.linkedin.com/in/asheshvashi

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: PATCH: pgagent connection string parsing

From

Dave Page

Date:

21 August 2017, 14:47:47

On Mon, Aug 21, 2017 at 9:46 AM, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:

On Mon, Aug 21, 2017 at 1:41 PM, Dave Page <dpage@pgadmin.org> wrote:

On Mon, Aug 21, 2017 at 6:24 AM, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:

On Aug 21, 2017 10:52, "Thomas Krennwallner" <tk+pgsql@postsubmeta.net> wrote:
On Mon Aug 21, 2017 06:50:03AM +0530, Ashesh Vashi wrote:
> On Aug 20, 2017 22:48, "Thomas Krennwallner" <[1]tk+pgsql@postsubmeta.net>
> wrote:
>
> % ./pgagent -f -t60 -l2 dbname=xxx user=tkren connection_timeout=5
[...]
> Sun Aug 20 18:24:34 2017 : DEBUG: Creating DB connection: user=tkren connection_timeout=5 dbname=xxx
> Sun Aug 20 18:24:34 2017 : WARNING: Couldn't create the primary connection (attempt 1): invalid connection option "connection_timeout"
>
> This suggests pgAgent is not using the latest version of libpq.
> Please use LD_LIBRARY_PATH environment to inform pgAgent to use the libpq,
> it is compiled with.
> -- Thanks,
> Ashesh Vashi
> -- Thanks, Ashesh

Unfortunately, this does not work. On a current Debian sid system,
I've compiled pgagent from source and get

% ldd ./pgagent
[...]
libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5 (0x00007ff531382000)
[...]

The system has libpq 9.6.4 installed:

% apt-cache show libpq5
Package: libpq5
Source: postgresql-9.6
Version: 9.6.4-1
[...]
You may want set the rpath manually using chrpath utility for testing.

Regardless of that, connection.cpp does need to be taught the correct name for connect_timeout, as well as the additional appname related parameters right?
True.

Thanks for volunteering :-)

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: PATCH: pgagent connection string parsing

From

Thomas Krennwallner

Date:

21 August 2017, 15:31:14

On Mon Aug 21, 2017 09:47:47AM +0100, Dave Page wrote:
> On Mon, Aug 21, 2017 at 9:46 AM, Ashesh Vashi <ashesh.vashi@enterprisedb.com wrote:
> > On Mon, Aug 21, 2017 at 1:41 PM, Dave Page <dpage@pgadmin.org> wrote:
> >> Regardless of that, connection.cpp does need to be taught the correct
> >> name for connect_timeout, as well as the additional appname related
> >> parameters right?
> >>
> > True.
> >
> 
> Thanks for volunteering :-)

The patch attached in my first email fixes both problems.

TK

Re: PATCH: pgagent connection string parsing

From

Ashesh Vashi

Date:

16 May 2018, 20:01:03

[Adding pgadmin-hackers list...]

Hi Team,

Using the mutex around the logging code too to avoid overlapping log message.

Please review it, and let me know your concern.

Implementation details:

- Moved 'MutexLogger' class, which was used by the connection management code only, in the include/misc.h

- Use a static mutex for the non-windows system, and used 'MutexLogger' instance for the better lock/unlock mechanism in the LogMessage(...) function.

-- Thanks, Ashesh

On Wed, May 16, 2018 at 6:59 PM, Dave Page <dpage@pgadmin.org> wrote:

On Wed, May 16, 2018 at 2:27 PM, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:
On Tue, May 8, 2018 at 3:01 PM, Thomas Krennwallner <tk+pgsql@postsubmeta.net> wrote:
Hi,

I've just found this suspicious log messages (I've added line numbers in
the attachment pgagent.log):

8 Tue May 8 11:11:23 2018 DEBUG: Creating DB connection: service=xiserver56_tisdbadm cTue May o8nne ct_timeout11:=151: 23 201 8applica DEBtUion_nameG: S=pglaeegpeinngt.@.d.e
9 v_tisdevel dbname=dev_tisdevel

Note how line 8 and 9 are actually two log messages, which should
have been printed in two lines:

Tue May 8 11:11:23 2018 DEBUG: Creating DB connection: service=xiserver56_tisdbadm connect_timeout=5 application_name=pgagent@dev_tisdevel dbname=dev_tisdevel
Tue May 8 11:11:23 2018 DEBUG: Sleeping...

It appears that two threads are writing to stdout at the same time,
which calls for an exclusive lock in function
void LogMessage(const std::wstring &msg, const int &level)
Dave,

Shall we use a mutex here too?

I would think so, yes.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Attachment

pgagent_logger_mutex.patch

Re: PATCH: pgagent connection string parsing

From

Ashesh Vashi

Date:

05 July 2018, 10:48:51

On Wed, May 16, 2018 at 7:31 PM, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:

[Adding pgadmin-hackers list...]

Hi Team,

Using the mutex around the logging code too to avoid overlapping log message.
Please review it, and let me know your concern.

Implementation details:
- Moved 'MutexLogger' class, which was used by the connection management code only, in the include/misc.h
- Use a static mutex for the non-windows system, and used 'MutexLogger' instance for the better lock/unlock mechanism in the LogMessage(...) function.

Hi Team,

Please review this patch.

-- Thanks, Ashesh

-- Thanks, Ashesh

On Wed, May 16, 2018 at 6:59 PM, Dave Page <dpage@pgadmin.org> wrote:

On Wed, May 16, 2018 at 2:27 PM, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:
On Tue, May 8, 2018 at 3:01 PM, Thomas Krennwallner <tk+pgsql@postsubmeta.net> wrote:
Hi,

I've just found this suspicious log messages (I've added line numbers in
the attachment pgagent.log):

8 Tue May 8 11:11:23 2018 DEBUG: Creating DB connection: service=xiserver56_tisdbadm cTue May o8nne ct_timeout11:=151: 23 201 8applica DEBtUion_nameG: S=pglaeegpeinngt.@.d.e
9 v_tisdevel dbname=dev_tisdevel

Note how line 8 and 9 are actually two log messages, which should
have been printed in two lines:

Tue May 8 11:11:23 2018 DEBUG: Creating DB connection: service=xiserver56_tisdbadm connect_timeout=5 application_name=pgagent@dev_tisdevel dbname=dev_tisdevel
Tue May 8 11:11:23 2018 DEBUG: Sleeping...

It appears that two threads are writing to stdout at the same time,
which calls for an exclusive lock in function
void LogMessage(const std::wstring &msg, const int &level)
Dave,

Shall we use a mutex here too?

I would think so, yes.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company