Merge lp:~therp-nl/openobject-addons/6.1-ronald@therp.nl_fix_lp1015995 into lp:openobject-addons/6.1

Proposed by Ronald Portier (Therp)
Status: Work in progress
Proposed branch: lp:~therp-nl/openobject-addons/6.1-ronald@therp.nl_fix_lp1015995
Merge into: lp:openobject-addons/6.1
Diff against target: 60 lines (+18/-33)
1 file modified
account_analytic_analysis/account_analytic_analysis.py (+18/-33)
To merge this branch: bzr merge lp:~therp-nl/openobject-addons/6.1-ronald@therp.nl_fix_lp1015995
Reviewer Review Type Date Requested Status
OpenERP Core Team Pending
Review via email: mp+111813@code.launchpad.net

Description of the change

Replace view definition for account_analytic_analysis_summary_user to prevent cpu meltdown and postgres crash.

New view definition does NOT include users, or accounts, without amount, but they are filtered out on use anyway.

To post a comment you must log in.
Revision history for this message
Stefan Rijnhart (Opener) (stefan-opener) wrote :

Unmerged revisions

6861. By Ronald Portier (Therp)

[FIX] Fix lp1015995, problems with runaway cpu usage and postgres crash on creating view account_analytic_analysis_summary_user.

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'account_analytic_analysis/account_analytic_analysis.py'
2--- account_analytic_analysis/account_analytic_analysis.py 2012-01-31 13:36:57 +0000
3+++ account_analytic_analysis/account_analytic_analysis.py 2012-06-25 10:37:26 +0000
4@@ -419,39 +419,24 @@
5
6 def init(self, cr):
7 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
8- cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
9- 'SELECT ' \
10- '(u.account_id * u.max_user) + u."user" AS id, ' \
11- 'u.account_id AS account_id, ' \
12- 'u."user" AS "user", ' \
13- 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
14- 'FROM ' \
15- '(SELECT ' \
16- 'a.id AS account_id, ' \
17- 'u1.id AS "user", ' \
18- 'MAX(u2.id) AS max_user ' \
19- 'FROM ' \
20- 'res_users AS u1, ' \
21- 'res_users AS u2, ' \
22- 'account_analytic_account AS a ' \
23- 'GROUP BY u1.id, a.id ' \
24- ') AS u ' \
25- 'LEFT JOIN ' \
26- '(SELECT ' \
27- 'l.account_id AS account_id, ' \
28- 'l.user_id AS "user", ' \
29- 'SUM(l.unit_amount) AS unit_amount ' \
30- 'FROM account_analytic_line AS l, ' \
31- 'account_analytic_journal AS j ' \
32- 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
33- 'GROUP BY l.account_id, l.user_id ' \
34- ') AS l '
35- 'ON (' \
36- 'u.account_id = l.account_id ' \
37- 'AND u."user" = l."user"' \
38- ') ' \
39- 'GROUP BY u."user", u.account_id, u.max_user' \
40- ')')
41+ cr.execute('''CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (
42+ with mu as
43+ (select max(id) as max_user from res_users)
44+ , lu AS
45+ (SELECT
46+ l.account_id AS account_id,
47+ coalesce(l.user_id, 0) AS user_id,
48+ SUM(l.unit_amount) AS unit_amount
49+ FROM account_analytic_line AS l,
50+ account_analytic_journal AS j
51+ WHERE (j.type = 'general' ) and (j.id=l.journal_id)
52+ GROUP BY l.account_id, l.user_id
53+ )
54+ select (lu.account_id * mu.max_user) + lu.user_id as id,
55+ lu.account_id as account_id,
56+ lu.user_id as "user",
57+ unit_amount
58+ from lu, mu)''')
59
60 account_analytic_account_summary_user()
61