611974 - collectstats.pl --regenerate fails with PostgreSQL 8.4.x (sql_from_days() doesn't accept integers as argument)

Status: RESOLVED FIXED

(Bugzilla :: Reporting/Charting, defect)

Description

[sam morris]

User-Agent:       Mozilla/5.0 (X11; U; Linux x86_64; en-US) AppleWebKit/534.3 (KHTML, like Gecko) Chrome/6.0.472.62 Safari/534.3
Build Identifier: 4.0rc1

After upgrading to 4.0rc1 and updating the bug status workflow, I was prompted to run collectstats --regenerate by the upgrade script. This failed with the following message:

DBD::Pg::db selectcol_arrayref failed: ERROR:  function to_timestamp(integer, unknown) does not exist
LINE 3:                          WHERE bugs.creation_ts < TO_TIMESTA...
                                                          ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts. [for Statement "SELECT bug_id 
                          FROM bugs  
                         WHERE bugs.creation_ts < TO_TIMESTAMP(2455100::int, 'J')::date AND bugs.creation_ts >= TO_TIMESTAMP(2455099::int, 'J')::date ORDER BY bug_id"] at ./collectstats.pl line 381
	main::regenerate_stats('./data/mining', '-All-', 'HASH(0x2da8368)', 'HASH(0x2da8398)', 'HASH(0x2da8338)') called at ./collectstats.pl line 153


Reproducible: Always

Comment 1

[sam morris]

Attachment: fix Bugzilla::DB::Pg sql_from_days

Fix argument types for postgresql's sql_from_days function

Comment 2

[Frédéric Buclin]

This problem exists in all supported branches, including 3.2 and 3.4.

When you say that you updated the bug status workflow, which page asked you to run collectstats --regenerate? Because updating the workflow shouldn't ask you to do it, at all!

Comment 3

[Frédéric Buclin]

Comment on attachment 490365 [details] [diff] [review]
fix Bugzilla::DB::Pg sql_from_days

>-    return "TO_TIMESTAMP(${days}::int, 'J')::date";
>+    return "TO_TIMESTAMP('${days}', 'J')::date";

Nit: you could write '$days' instead of '${days}'. Anyway, this fixes the problem. Thanks for the patch. r=LpSolit

Comment 4

[sam morris]

(In reply to comment #2)
> When you say that you updated the bug status workflow, which page asked you to
> run collectstats --regenerate? Because updating the workflow shouldn't ask you
> to do it, at all!

It is suggested when contrib/convert-workflow.pl completes.

Comment 5

[Frédéric Buclin]

Committing to: bzr+ssh://[e-mail]/bugzilla/trunk/
modified Bugzilla/DB/Pg.pm
Committed revision 7603.

Committing to: bzr+ssh://[e-mail]/bugzilla/4.0/
modified Bugzilla/DB/Pg.pm
Committed revision 7483.

Committing to: bzr+ssh://[e-mail]/bugzilla/3.6/
modified Bugzilla/DB/Pg.pm
Committed revision 7209.

Comment 6

[Max Kanat-Alexander]

You really should be using $dbh->quote to put quotes on things, not using manual quotes. But I agree that the patch is fine as checked in.