PATCH: pgagent connection string parsing
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=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=tk
Re: PATCH: pgagent connection string parsing
On Aug 20, 2017 22:48, "Thomas Krennwallner" 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 201
Re: PATCH: pgagent connection string parsing
On Mon Aug 21, 2017 06:50:03AM +0530, Ashesh Vashi wrote:
>On Aug 20, 2017 22:48, "Thomas Krennwallner" <[1]tk+pg...@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 (0x7ff531382000)
[...]
The system has libpq 9.6.4 installed:
% apt-cache show libpq5
Package: libpq5
Source: postgresql-9.6
Version: 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: 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
On Aug 21, 2017 10:52, "Thomas Krennwallner"
wrote:
On Mon Aug 21, 2017 06:50:03AM +0530, Ashesh Vashi wrote:
>On Aug 20, 2017 22:48, "Thomas Krennwallner" <[1]
tk+pg...@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
(0x7ff531382000)
[...]
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
