[rt-users] Problem Tools, Reports Postgres 3.8.2
Kenneth Marshall
ktm at rice.edu
Thu Mar 12 09:20:52 EDT 2009
It looks like the problem is caused by trying to change the
case of a timestamp, which is nonsensical. Could you send me
a sample advanced search input that I could try with my setup
and queues to see if I can reproduce the problem locally?
You could also define a lower() function that does nothing
(ack, cough, cough,... I will deny I ever suggested it...).
Cheers,
Ken
On Thu, Mar 12, 2009 at 12:55:48PM +0000, Kristian Davies wrote:
> Unfortunately I'd already tried those. I did however get excellent
> results from your full text search wiki page though.
>
>
>
> Mar 12 12:48:19 ghole postgres[13129]: [2-1] ERROR: function
> lower(timestamp without time zone) does not exist at character 259
> Mar 12 12:48:19 ghole postgres[13129]: [2-2] HINT: No function
> matches the given name and argument types. You might need to add
> explicit type casts.
> Mar 12 12:48:19 ghole postgres[13129]: [2-3] STATEMENT: SELECT
> COUNT(main.id) AS id, Users_1.Name AS col1 FROM ( SELECT DISTINCT
> main.id FROM Tickets main LEFT JOIN Users
> Mar 12 12:48:19 ghole postgres[13129]: [2-4] Users_1 ON ( Users_1.id
> = main.Owner ) WHERE (LOWER(main.Status) != 'deleted') AND
> (LOWER(main.Status) = 'resolved' AND
> Mar 12 12:48:19 ghole postgres[13129]: [2-5] main.Queue = '1' AND
> lower(main.Resolved) > '2008-01-01 00:00:00') AND (LOWER(main.Type) =
> 'ticket') AND (main.EffectiveId =
> Mar 12 12:48:19 ghole postgres[13129]: [2-6] main.id) )
> distinctquery, Tickets main WHERE (main.id = distinctquery.id) GROUP
> BY Users_1.Name
> Mar 12 12:48:20 ghole postgres[13130]: [2-1] ERROR: function
> lower(timestamp without time zone) does not exist at character 209
> Mar 12 12:48:20 ghole postgres[13130]: [2-2] HINT: No function
> matches the given name and argument types. You might need to add
> explicit type casts.
> Mar 12 12:48:20 ghole postgres[13130]: [2-3] STATEMENT: SELECT
> COUNT(DISTINCT main.id) FROM Tickets main LEFT JOIN Users Users_1 ON
> ( Users_1.id = main.Owner ) WHERE
> Mar 12 12:48:20 ghole postgres[13130]: [2-4] (LOWER(main.Status) !=
> 'deleted') AND (LOWER(main.Status) = 'resolved' AND main.Queue = '1'
> AND lower(main.Resolved) >
> Mar 12 12:48:20 ghole postgres[13130]: [2-5] '2008-01-01 00:00:00')
> AND (LOWER(main.Type) = 'ticket') AND (main.EffectiveId = main.id)
>
> [Thu Mar 12 12:48:19 2009] [debug]: RT::Date used Time::ParseDate to
> make '01/01/2008' 1199145600
> (/disk1/web/vhost/rt.local/rt/bin/../lib/RT/Date.pm:209)
> [Thu Mar 12 12:48:19 2009] [debug]: RT::Date used Time::ParseDate to
> make '2008-01-01 00:00:00' 1199145600
> (/disk1/web/vhost/rt.local/rt/bin/../lib/RT/Date.pm:209)
> [Thu Mar 12 12:48:19 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 12:48:19 2009] [warning]: RT::Handle=HASH(0xa4a0600)
> couldn't execute the query 'SELECT COUNT(main.id) AS id, Users_1.Name
> AS col1 FROM ( SELECT DISTINCT main.id FROM Tickets main 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) > '2008-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(0xa4a0600)',
> 'SELECT COUNT(main.id) AS id, Users_1.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(0xabf9988)')
> called at /disk1/web/vhost/rt.local/rt/bin/../lib/RT/Tickets_Overlay.pm
> line 2672
> RT::Tickets::_DoSearch('RT::Report::Tickets=HASH(0xabf9988)')
> called at /disk1/web/vhost/rt.local/rt/bin/../lib/RT/Report/Tickets.pm
> line 152
> RT::Report::Tickets::_DoSearch('RT::Report::Tickets=HASH(0xabf9988)')
> called at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder.pm line
> 498
> DBIx::SearchBuilder::Next('RT::Report::Tickets=HASH(0xabf9988)')
> called at /disk1/web/vhost/rt.local/rt/bin/../lib/RT/Report/Tickets.pm
> line 253
> RT::Report::Tickets::Next('RT::Report::Tickets=HASH(0xabf9988)')
> 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(0xabfcbd0)',
> '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/2008', '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(0xa9ff810)',
> 'ResolvedBefore', '', 'ResolvedAfter', '01/01/2008', '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/2008', 'Queue',
> 'Systems', ...) called at
> /disk1/web/vhost/rt.local/rt/local/html/autohandler line 311
> HTML::Mason::Commands::__ANON__('ResolvedBefore', '',
> 'ResolvedAfter', '01/01/2008', '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(0xaa03c7c)',
> 'ResolvedBefore', '', 'ResolvedAfter', '01/01/2008', '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/2008', '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(0xa811938)')
> 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(0xa811938)')
> 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(0x9030968)',
> 'Apache2::RequestRec=SCALAR(0xa4591b0)') 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(0xa4591b0)')
> called at -e line 0
> eval {...} called at -e line 0 (/usr/lib/perl5/5.8.8/Carp.pm:272)
> [Thu Mar 12 12:48:19 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 12:48:20 2009] [debug]: RT::Date used Time::ParseDate to
> make '2008-01-01 00:00:00' 1199145600
> (/disk1/web/vhost/rt.local/rt/bin/../lib/RT/Date.pm:209)
> [Thu Mar 12 12:48:20 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 12:48:20 2009] [warning]: RT::Handle=HASH(0xa49f128)
> couldn't execute the query 'SELECT COUNT(DISTINCT main.id) FROM
> Tickets main 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) > '2008-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(0xa49f128)',
> '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(0xab6e00c)')
> called at /disk1/web/vhost/rt.local/rt/bin/../lib/RT/Tickets_Overlay.pm
> line 2678
> RT::Tickets::_DoCount('RT::Report::Tickets=HASH(0xab6e00c)')
> called at /usr/lib/perl5/site_perl/5.8.8/DBIx/SearchBuilder.pm line
> 1380
> DBIx::SearchBuilder::Count('RT::Report::Tickets=HASH(0xab6e00c)')
> called at /disk1/web/vhost/rt.local/rt/bin/../lib/RT/Tickets_Overlay.pm
> line 2597
> RT::Tickets::Count('RT::Report::Tickets=HASH(0xab6e00c)')
> 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(0xaa43f08)',
> '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(0xaa65bec)',
> '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(0xa9e00d8)')
> 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(0xa9e00d8)')
> 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(0x9030968)',
> 'Apache2::RequestRec=SCALAR(0xa459198)') 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(0xa459198)')
> called at -e line 0
> eval {...} called at -e line 0 (/usr/lib/perl5/5.8.8/Carp.pm:272)
>
More information about the rt-users
mailing list