[rt-users] RTx::S3Invoker problems with Postgres 8.1.11 and RT 3.8.4

[Ian Smith]

Hi folks,

 

I'm setting up a new instance of RT 3.8.4 with postgres 8.1.11 on CentOS
5.3, and had some problems trying to implement the Rtx::S3Invoker plugin
to enable simple search to execute saved searches.   I fixed the
problem, but have a couple of questions.  Some background:

 

After installing, when I tried to do a "do:unowned" at the simplesearch
screen I got an error like this in rt.log:

 

[Thu Aug 27 16:44:29 2009] [warning]: DBD::Pg::st execute failed: ERROR:
column "attributes.description" must appear in the GROUP BY clause or be
used in an aggregate function
(/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:509)

 

[Thu Aug 27 16:44:29 2009] [warning]: RT::Handle=HASH(0x2ac0dc7a8be0)
couldn't execute the query 'SELECT COUNT(*) FROM Attributes WHERE
Description LIKE '%unowned%' AND ( (Name='SavedSearch' AND (
(ObjectType='RT::User' AND ObjectId=12)  ) ) OR (
ObjectType='RT::System' AND (Name LIKE 'Search - %' OR
Name='SavedSearch') ) ) ORDER BY Description;' at
/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 522

 

 
DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0x2ac0dc7a8be0
)', 'SELECT COUNT(*) FROM Attributes WHERE Description LIKE \'%uno...')
called at /usr/local/rt/plugins/RTx-S3Invoker/html/Search/Invoker line
36

 

A similar error appeared in the browser.  After contemplating Invoker, I
decided to run the query manually against the database with psql.  I
received the same error, so Postgres does not like this query format for
some reason.  Googling turned up this:  
http://archives.postgresql.org/pgsql-general/2004-02/msg01210.php in
which the inimitable Tom Lane says:

 

"Postgres currently implements the SQL92 definition, which is

that you can't refer to an ungrouped column except within an aggregate

function call.  So you need to call out all the columns to be referenced

in GROUP BY."

 

So I changed Invoker thusly.  

 

Before:

 

my $WHERE = sprintf("WHERE Description LIKE '%s' AND (
(Name='SavedSearch' AND ( (ObjectType='RT::User' AND ObjectId=%i) %s ) )
OR ( ObjectType='RT::System' AND (Name LIKE 'Search - %' OR
Name='SavedSearch') ) ) ORDER BY Description",

                    "%\Q${do}\E%",

                    $session{'CurrentUser'}->Id,

                    scalar @groups ? "OR ( ObjectType='RT::Group' AND
ObjectId IN (". join(',', @groups) .') )' : ''

                   );

 

 

After:

 

my $WHERE = sprintf("WHERE Description LIKE '%s' AND (
(Name='SavedSearch' AND ( (ObjectType='RT::User' AND ObjectId=%i) %s ) )
OR ( ObjectType='RT::System' AND (Name LIKE 'Search - %' OR
Name='SavedSearch') ) ) GROUP BY Description, Objecttype, objectid, id
ORDER BY Description",

                    "%\Q${do}\E%",

                    $session{'CurrentUser'}->Id,

                    scalar @groups ? "OR ( ObjectType='RT::Group' AND
ObjectId IN (". join(',', @groups) .') )' : ''

                   );

 

 

This worked!  great.  

 

 

 

Here are the questions:

 

1.  It appears that Postgres *may* support SELECT with undefined GROUP
BY in later releases, but I have not been able to test this yet.  is
8.1.11 modern enough to make this worth filing a bug report?  It's the
default postgres package that comes with CentOS 5.3, so maybe it's not
so ancient.

 

2.  It doesn't look like calling out a GROUP BY explicitly like this
would work against MySQL installs, but I'm really not sure.  Would there
be a better way of constructing the query than what I have done here?
(SELECT DISTINCT maybe?)

 

 

Thanks all for yout time!

 

Ian Smith

Director of Information Technology

________________________________


Production Resource Group

201-758-4315 findme
201-868-7195 fax
ismith at prg.com <mailto:ismith at prg.com> 

www.prg.com <http://www.prg.com/>  

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090828/095257f5/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: invoke-modified.tar.gz
Type: application/x-gzip
Size: 1509 bytes
Desc: invoke-modified.tar.gz
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090828/095257f5/attachment.bin>