Обсуждение: pgAgent Database Name Limitation
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
=============================================
Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham,
NG7 2PZ
England
Telephone numbers:
+44 (0)115 951 88 00 (main)
For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info@romaxtech.com
Website: www.romaxtech.com
=================================
================
Confidentiality Statement
This transmission is for the addressee only and contains information that is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation.
=================================================
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
=============================================
Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham,
NG7 2PZ
England
Telephone numbers:
+44 (0)115 951 88 00 (main)
For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info@romaxtech.com
Website: www.romaxtech.com
=================================
================
Confidentiality Statement
This transmission is for the addressee only and contains information that is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation.
=================================================
>
> 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"
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.
cheers
Martin
=============================================
Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham,
NG7 2PZ
England
Telephone numbers:
+44 (0)115 951 88 00 (main)
For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info@romaxtech.com
Website: www.romaxtech.com
=================================
================
Confidentiality Statement
This transmission is for the addressee only and contains information that is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation.
=================================================
> 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"
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.
cheers
Martin
=============================================
Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham,
NG7 2PZ
England
Telephone numbers:
+44 (0)115 951 88 00 (main)
For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info@romaxtech.com
Website: www.romaxtech.com
=================================
================
Confidentiality Statement
This transmission is for the addressee only and contains information that is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation.
=================================================
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
> > 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"
>
> 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.
>
Missed a bit of the code!!!
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 last line is the culprit.
cheers
=============================================
Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham,
NG7 2PZ
England
Telephone numbers:
+44 (0)115 951 88 00 (main)
For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info@romaxtech.com
Website: www.romaxtech.com
=================================
================
Confidentiality Statement
This transmission is for the addressee only and contains information that is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation.
=================================================
> > 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...
Cheers
Martin
=============================================
Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham,
NG7 2PZ
England
Telephone numbers:
+44 (0)115 951 88 00 (main)
For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info@romaxtech.com
Website: www.romaxtech.com
=================================
================
Confidentiality Statement
This transmission is for the addressee only and contains information that is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation.
=================================================
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