Обсуждение: 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

On Tue, Sep 4, 2012 at 12:00 PM, Martin French
<Martin.French@romaxtech.com> wrote:
>
>> > 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("="));
>> >
>> >                // Seperate all the prop=value patterns
>
>> >                wxArrayString tokens = wxStringTokenize(connStr, wxT("\t
>> > \n\r"));
>
>
>> > [...]
>
>
>>
>> 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 :-)
>>
>
>
>
> There's a couple of things going on here Dave that I can't seem to get
> around at the moment. The wxStringTokenize actually destroys the string, so
> I'm going to do a little work here to see if i can fix and allow libpq style
> string quoting! (my regexp-fu is appalling to say the least! ;-) )
>
> Wish me luck...

Good luck :-). Feel free to post here if you need advice; we'll help
out if we can.

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

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