Re: pgAgent Database Name Limitation

Hi

On Tue, Sep 4, 2012 at 11:21 AM, Martin French
<Martin.French@romaxtech.com> wrote:
>>
>> Hi all,
>>
>> I've come across an issue within pgAgent whereby it cannot/will not
>> connect to a database where there is a space in the name.
>> (I know that typically pgAgent should run within the postgres db to
>> allow pgAdmin job management, however, this is a second pgAgent
>> running with a specific task set)
>>
>> I have tried all the following variants:
>>
>> "dbname='DB With Spaces 1' user=user1"
>> 'dbname="DB With Spaces 1" user=user1'
>> "dbname=DB With Spaces 1 user=user1"
>> dbname=DB With Spaces 1 user=user1
>> "dbname=\'DB With Spaces 1\' user=user1"
>>
>> etc etc...
>>
>> All of which return:
>>         ERROR: Primary connection string is not valid!
>>
>> Anyone know if this a limitation within pgAgent or libpq? I had an
>> odd thought that this may be because of wxStrings...
>>
>> I've not yet investigated the source to see how this is being
>> interpreted, as I thought i'd shout up on here to see If this is a
>> libpq limitation.
>>
>> Cheers
>>
>> Martin
>
> Ok, so a little source investigation and a quick libpq program later (to
> verify where this issue lies):
>
> $ gcc testlibpq.c -I/usr/pg914/include -L/usr/pg914/lib -lpq -o test
>
> This works:
>
> $ ./test "dbname='DB With Spaces 1'"
>
>
> These do not:
>
> $ ./test dbname=DB With Spaces 1
> $ ./test dbname="DB With Spaces 1"
> $ ./test dbname='DB With Spaces 1'
>
> However, passing the same style parameter to pgAgent still fails. I suspect
> that this is where I'm seeing an issue:
>
> connInfo connInfo::getConnectionInfo(wxString connStr)
> {
>         connInfo cnInfo;
>
>         wxRegEx propertyExp;
>
>         // Remove the white-space(s) to match the following format
>         // i.e. prop=value
>         bool res = propertyExp.Compile(wxT("(([ ]*[\t]*)+)="));
>
>         propertyExp.ReplaceAll(&connStr, wxT("="));
>
>         res = propertyExp.Compile(wxT("=(([ ]*[\t]*)+)"));
>         propertyExp.ReplaceAll(&connStr, wxT("="));
>
> [...]
> }
>
> The regex strips out spaces causing "DB With Spaces 1" to become
> "DBWithSpaces1"

Urgh.

> If everyone is happy for me to, I'll work up a patch for this to allow
> spaces in the DB name for instances where the postgres DB doesn't
> exist/isn't used as the pgAgent driver.

The correct fix would be to modify the regexp so it doesn't strip
spaces inside of quoted sub-strings. Not sure my regexp-fu is that
strong though, so please feel free to work on it :-)

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

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