[rt-users] Problem Tools, Reports Postgres 3.8.2

Kenneth Marshall ktm at rice.edu
Thu Mar 12 08:42:30 EDT 2009


On Thu, Mar 12, 2009 at 11:00:21AM +0000, Kristian Davies wrote:
> Postgres: 8.3.6
> RT 3.8.2
> CentOS 4
> 
> My graphing isn't working under Tools>Reports.
> 
> Any ideas?
> 
> Cheers,
> Kristian
> 
> 
> /var/log/message
> 
> Mar 12 10:50:27 ghole postgres[11326]: [2-1] ERROR:  function
> lower(timestamp without time zone) does not exist at character 315
> Mar 12 10:50:27 ghole postgres[11326]: [2-2] HINT:  No function
> matches the given name and argument types. You might need to add
> explicit type casts.
> Mar 12 10:50:27 ghole postgres[11326]: [2-3] STATEMENT:  SELECT
> COUNT(main.id) AS id, Users_2.Name AS col1 FROM ( SELECT DISTINCT
> main.id FROM Tickets main LEFT JOIN Users
> Mar 12 10:50:27 ghole postgres[11326]: [2-4]  Users_2  ON ( Users_2.id
> = main.Owner ) LEFT JOIN Users Users_1  ON ( Users_1.id = main.Owner )
>  WHERE (LOWER(main.Status) !=
> Mar 12 10:50:27 ghole postgres[11326]: [2-5]  'deleted') AND
> (LOWER(main.Status) = 'resolved' AND main.Queue = '1' AND
> LOWER(main.Resolved) > '2009-01-01 00:00:00') AND
> Mar 12 10:50:27 ghole postgres[11326]: [2-6]  (LOWER(main.Type) =
> 'ticket') AND (main.EffectiveId = main.id)  ) distinctquery, Tickets
> main WHERE (main.id =
> Mar 12 10:50:27 ghole postgres[11326]: [2-7]  distinctquery.id)  GROUP
> BY Users_1.Name
> Mar 12 10:50:28 ghole postgres[11327]: [2-1] ERROR:  function
> lower(timestamp without time zone) does not exist at character 265
> Mar 12 10:50:28 ghole postgres[11327]: [2-2] HINT:  No function
> matches the given name and argument types. You might need to add
> explicit type casts.
> Mar 12 10:50:28 ghole postgres[11327]: [2-3] STATEMENT:  SELECT
> COUNT(DISTINCT main.id) FROM Tickets main LEFT JOIN Users Users_2  ON
> ( Users_2.id = main.Owner ) LEFT JOIN
> Mar 12 10:50:28 ghole postgres[11327]: [2-4]  Users Users_1  ON (
> Users_1.id = main.Owner )  WHERE (LOWER(main.Status) != 'deleted') AND
> (LOWER(main.Status) = 'resolved' AND
> Mar 12 10:50:28 ghole postgres[11327]: [2-5]  main.Queue = '1' AND
> LOWER(main.Resolved) > '2009-01-01 00:00:00') AND (LOWER(main.Type) =
> 'ticket') AND (main.EffectiveId =
> Mar 12 10:50:28 ghole postgres[11327]: [2-6]  main.id)
> 
> /rt.log
> 
> HINT:  No function matches the given name and argument types. You
> might need to add explicit type casts. at
> /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 505.
> (/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:505)
> [Thu Mar 12 10:50:27 2009] [warning]: RT::Handle=HASH(0xaa0d998)
> couldn't execute the query 'SELECT COUNT(main.id) AS id, Users_2.Name
> AS col1 FROM ( SELECT DISTINCT main.id FROM Tickets main LEFT JOIN
> Users Users_2  ON ( Users_2.id = main.Owner ) LEFT JOIN Users Users_1
> ON ( Users_1.id = main.Owner )  WHERE (LOWER(main.Status) !=
> 'deleted') AND (LOWER(main.Status) = 'resolved' AND main.Queue = '1'
> AND LOWER(main.Resolved) > '2009-01-01 00:00:00') AND
> (LOWER(main.Type) = 'ticket') AND (main.EffectiveId = main.id)  )
> distinctquery, Tickets main WHERE (main.id = distinctquery.id)  GROUP
> BY Users_1.Name ' at
> /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 518
>         DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0xaa0d998)',
> 'SELECT COUNT(main.id) AS id, Users_2.Name AS col1 FROM ( SELE...')
> called at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder.pm line
> 238
>         DBIx::SearchBuilder::_DoSearch('RT::Report::Tickets=HASH(0xb166a58)')
> called at /disk1/web/vhost/rt.local/rt/bin/../lib/RT/Tickets_Overlay.pm
> line 2672
>         RT::Tickets::_DoSearch('RT::Report::Tickets=HASH(0xb166a58)')
> called at /disk1/web/vhost/rt.local/rt/bin/../lib/RT/Report/Tickets.pm
> line 152
>         RT::Report::Tickets::_DoSearch('RT::Report::Tickets=HASH(0xb166a58)')
> called at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder.pm line
> 498
>         DBIx::SearchBuilder::Next('RT::Report::Tickets=HASH(0xb166a58)')
> called at /disk1/web/vhost/rt.local/rt/bin/../lib/RT/Report/Tickets.pm
> line 236
>         RT::Report::Tickets::Next('RT::Report::Tickets=HASH(0xb166a58)')
> called at /disk1/web/vhost/rt.local/rt/share/html/Search/Elements/Chart
> line 71
>         HTML::Mason::Commands::__ANON__('Query', 'Status =
> \'resolved\' AND Queue = \'Systems\' AND Resolved > ...',
> 'PrimaryGroupBy', 'Owner') called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Component.pm line 135
>         HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0xb169ca0)',
> 'Query', 'Status = \'resolved\' AND Queue = \'Systems\' AND Resolved >
> ...', 'PrimaryGroupBy', 'Owner') called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1278
>         eval {...} called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1268
>         HTML::Mason::Request::comp('undef', 'undef', 'Query', 'Status
> = \'resolved\' AND Queue = \'Systems\' AND Resolved > ...',
> 'PrimaryGroupBy', 'Owner') called at
> /disk1/web/vhost/rt.local/rt/share/html/Tools/Reports/ResolvedByDates.html
> line 83
>         HTML::Mason::Commands::__ANON__('ResolvedBefore', '',
> 'ResolvedAfter', '01/01/2009', 'Queue', 'Systems') called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Component.pm line 135
>         HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0xaf6cc00)',
> 'ResolvedBefore', '', 'ResolvedAfter', '01/01/2009', 'Queue',
> 'Systems') called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1278
>         eval {...} called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1268
>         HTML::Mason::Request::comp('undef', 'undef', 'undef',
> 'ResolvedBefore', '', 'ResolvedAfter', '01/01/2009', 'Queue',
> 'Systems', ...) called at
> /disk1/web/vhost/rt.local/rt/local/html/autohandler line 311
>         HTML::Mason::Commands::__ANON__('ResolvedBefore', '',
> 'ResolvedAfter', '01/01/2009', 'Queue', 'Systems') called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Component.pm line 135
>         HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0xaf70ec4)',
> 'ResolvedBefore', '', 'ResolvedAfter', '01/01/2009', 'Queue',
> 'Systems') called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1273
>         eval {...} called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1268
>         HTML::Mason::Request::comp('undef', 'undef', 'undef',
> 'ResolvedBefore', '', 'ResolvedAfter', '01/01/2009', 'Queue',
> 'Systems', ...) called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 467
>         eval {...} called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 467
>         eval {...} called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 419
>         HTML::Mason::Request::exec('RT::Interface::Web::Request=HASH(0xad7ed30)')
> called at /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/ApacheHandler.pm
> line 168
>         HTML::Mason::Request::ApacheHandler::exec('RT::Interface::Web::Request=HASH(0xad7ed30)')
> called at /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/ApacheHandler.pm
> line 825
>         HTML::Mason::ApacheHandler::handle_request('HTML::Mason::ApacheHandler=HASH(0x9078598)',
> 'Apache2::RequestRec=SCALAR(0xa9c66f0)') called at
> /disk1/web/vhost/rt.local/rt/bin/webmux.pl line 149
>         eval {...} called at /disk1/web/vhost/rt.local/rt/bin/webmux.pl line 149
>         RT::Mason::handler('Apache2::RequestRec=SCALAR(0xa9c66f0)')
> called at -e line 0
>         eval {...} called at -e line 0 (/usr/lib/perl5/5.8.8/Carp.pm:272)
> [Thu Mar 12 10:50:28 2009] [debug]: RT's GnuPG libraries couldn't
> successfully read your configured GnuPG home directory
> (/disk1/web/vhost/rt.local/rt/var/data/gpg). PGP support has been
> disabled (/disk1/web/vhost/rt.local/rt/bin/../local/lib/RT/Config.pm:339)
> [Thu Mar 12 10:50:28 2009] [debug]: RT::Date used Time::ParseDate to
> make '2009-01-01 00:00:00' 1230768000
> (/disk1/web/vhost/rt.local/rt/bin/../lib/RT/Date.pm:209)
> [Thu Mar 12 10:50:28 2009] [warning]: DBD::Pg::st execute failed:
> ERROR:  function lower(timestamp without time zone) does not exist
> LINE 1: ...ain.Status) = 'resolved' AND main.Queue = '1' AND LOWER(main...
>                                                              ^
> HINT:  No function matches the given name and argument types. You
> might need to add explicit type casts. at
> /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 505.
> (/usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm:505)
> [Thu Mar 12 10:50:28 2009] [warning]: RT::Handle=HASH(0xaa0c4d0)
> couldn't execute the query 'SELECT COUNT(DISTINCT main.id) FROM
> Tickets main LEFT JOIN Users Users_2  ON ( Users_2.id = main.Owner )
> LEFT JOIN Users Users_1  ON ( Users_1.id = main.Owner )  WHERE
> (LOWER(main.Status) != 'deleted') AND (LOWER(main.Status) = 'resolved'
> AND main.Queue = '1' AND LOWER(main.Resolved) > '2009-01-01 00:00:00')
> AND (LOWER(main.Type) = 'ticket') AND (main.EffectiveId = main.id) '
> at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line
> 518
>         DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0xaa0c4d0)',
> 'SELECT COUNT(DISTINCT main.id) FROM Tickets main LEFT JOIN Us...')
> called at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder.pm line
> 294
>         DBIx::SearchBuilder::_DoCount('RT::Report::Tickets=HASH(0xb0db1dc)')
> called at /disk1/web/vhost/rt.local/rt/bin/../lib/RT/Tickets_Overlay.pm
> line 2678
>         RT::Tickets::_DoCount('RT::Report::Tickets=HASH(0xb0db1dc)')
> called at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder.pm line
> 1380
>         DBIx::SearchBuilder::Count('RT::Report::Tickets=HASH(0xb0db1dc)')
> called at /disk1/web/vhost/rt.local/rt/bin/../lib/RT/Tickets_Overlay.pm
> line 2597
>         RT::Tickets::Count('RT::Report::Tickets=HASH(0xb0db1dc)')
> called at /disk1/web/vhost/rt.local/rt/share/html/Search/Chart line 93
>         HTML::Mason::Commands::__ANON__('Query', 'Status =
> \'resolved\' AND Queue = \'Systems\' AND Resolved > ...',
> 'PrimaryGroupBy', 'Owner') called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Component.pm line 135
>         HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0xafb1350)',
> 'Query', 'Status = \'resolved\' AND Queue = \'Systems\' AND Resolved >
> ...', 'PrimaryGroupBy', 'Owner') called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1278
>         eval {...} called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1268
>         HTML::Mason::Request::comp('undef', 'undef', 'undef', 'Query',
> 'Status = \'resolved\' AND Queue = \'Systems\' AND Resolved > ...',
> 'PrimaryGroupBy', 'Owner') called at
> /disk1/web/vhost/rt.local/rt/local/html/autohandler line 311
>         HTML::Mason::Commands::__ANON__('Query', 'Status =
> \'resolved\' AND Queue = \'Systems\' AND Resolved > ...',
> 'PrimaryGroupBy', 'Owner') called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Component.pm line 135
>         HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0xafd2ef4)',
> 'Query', 'Status = \'resolved\' AND Queue = \'Systems\' AND Resolved >
> ...', 'PrimaryGroupBy', 'Owner') called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1273
>         eval {...} called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 1268
>         HTML::Mason::Request::comp('undef', 'undef', 'undef', 'Query',
> 'Status = \'resolved\' AND Queue = \'Systems\' AND Resolved > ...',
> 'PrimaryGroupBy', 'Owner') called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 467
>         eval {...} called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 467
>         eval {...} called at
> /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/Request.pm line 419
>         HTML::Mason::Request::exec('RT::Interface::Web::Request=HASH(0xaf4d518)')
> called at /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/ApacheHandler.pm
> line 168
>         HTML::Mason::Request::ApacheHandler::exec('RT::Interface::Web::Request=HASH(0xaf4d518)')
> called at /usr/lib/perl5/site_perl/5.8.8/HTML/Mason/ApacheHandler.pm
> line 825
>         HTML::Mason::ApacheHandler::handle_request('HTML::Mason::ApacheHandler=HASH(0x9078598)',
> 'Apache2::RequestRec=SCALAR(0xa9c66d8)') called at
> /disk1/web/vhost/rt.local/rt/bin/webmux.pl line 149
>         eval {...} called at /disk1/web/vhost/rt.local/rt/bin/webmux.pl line 149
>         RT::Mason::handler('Apache2::RequestRec=SCALAR(0xa9c66d8)')
> called at -e line 0
>         eval {...} called at -e line 0 (/usr/lib/perl5/5.8.8/Carp.pm:272)
> _______________________________________________

Kristian,

I reported this bug and it has been fixed in CVS and should appear
in 3.8.3. Here is a diff of my patched version of lib/RT/Report/Tickets.pm.
Make a copy of that file to local/lib/RT/Report/Tickets.pm and apply this
patch.

Cheers,
Ken


--- lib/RT/Report/Tickets.pm	2009-01-21 13:12:22.000000000 -0600
+++ local/lib/RT/Report/Tickets.pm	2009-02-03 11:06:35.000000000 -0600
@@ -169,13 +169,16 @@
     if ($field =~ /^(.*)(Daily|Monthly|Annually)$/) {
         my ($field, $grouping) = ($1, $2);
         if ( $grouping =~ /Daily/ ) {
-            $args{'FUNCTION'} = "SUBSTR($field,1,10)";
+#            $args{'FUNCTION'} = "SUBSTR($field,1,10)";
+            $args{'FUNCTION'} = "SUBSTR(${field}::text,1,10)";
         }
         elsif ( $grouping =~ /Monthly/ ) {
-            $args{'FUNCTION'} = "SUBSTR($field,1,7)";
+#            $args{'FUNCTION'} = "SUBSTR($field,1,7)";
+            $args{'FUNCTION'} = "SUBSTR(${field}::text,1,7)";
         }
         elsif ( $grouping =~ /Annually/ ) {
-            $args{'FUNCTION'} = "SUBSTR($field,1,4)";
+#            $args{'FUNCTION'} = "SUBSTR($field,1,4)";
+            $args{'FUNCTION'} = "SUBSTR(${field}::text,1,4)";
         }
     } elsif ( $field =~ /^(?:CF|CustomField)\.{(.*)}$/ ) { #XXX: use CFDecipher method
         my $cf_name = $1;
@@ -190,24 +193,25 @@
     } elsif ( $field =~ /^(?:(Owner|Creator|LastUpdatedBy))(?:\.(.*))?$/ ) {
         my $type = $1 || '';
         my $column = $2 || 'Name';
-        my $u_alias = $self->Join(
-            TYPE   => 'LEFT',
-            ALIAS1 => 'main',
-            FIELD1 => $type,
-            TABLE2 => 'Users',
-            FIELD2 => 'id',
-        );
+	my $u_alias = $self->{"_sql_report_${type}_users_${column}"}
+	    ||= $self->Join(
+	    TYPE => 'LEFT',
+	    ALIAS1 => 'main',
+    	    FIELD1 => $type,
+	    TABLE2 => 'Users',
+	    FIELD2 => 'id',
+	);
         @args{qw(ALIAS FIELD)} = ($u_alias, $column);
     } elsif ( $field =~ /^(?:Watcher|(Requestor|Cc|AdminCc))(?:\.(.*))?$/ ) {
         my $type = $1 || '';
         my $column = $2 || 'Name';
-        if ( my $u_alias = $self->{"_sql_report_watcher_users_alias_$type"} ) {
-            @args{qw(ALIAS FIELD)} = ($u_alias, $column);
-        } else {
-            my ($g_alias, $gm_alias, $u_alias) = $self->_WatcherJoin( $type );
-            @args{qw(ALIAS FIELD)} = ($u_alias, $column);
+	my $u_alias = $self->{"_sql_report_watcher_users_alias_$type"};
+	unless ( $u_alias ) {
+	    my ($g_alias, $gm_alias);
+	    ($g_alias, $gm_alias, $u_alias) = $self->_WatcherJoin( $type );
             $self->{"_sql_report_watcher_users_alias_$type"} = $u_alias;
         }
+	@args{qw(ALIAS FIELD)} = ($u_alias, $column);
     }
     return %args;
 }



More information about the rt-users mailing list