RTx::S3Invoker problems with Postgres 8.1.11 and RT 3.8.4

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:
PostgreSQL: Re: field must appear in the GROUP BY clause or be used 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@prg.com mailto:ismith@prg.com

www.prg.com http://www.prg.com/

invoke-modified.tar.gz (1.47 KB)

No quite sure why this went to the list rather than rt.cpan,
and with an archive rather than a diff, but thanks for the report.
This has been fixed in 0.15 which is on its way to a CPAN
mirror near you.

Cambridge Energy Alliance: Save money. Save the planet.

Mostly because I wasn’t sure it was bug-worthy, and wanted some
feedback. It came as an archive because I am unfamiliar with the
process! :slight_smile:

Thanks,

-Ian