Re: [SL] Timeout on AR::Reports::Outstanding

[Armaghan S. <sa...@le...>]

Martin Boese wrote:
> AR::Reports::Outstanding::next should show me all outstanding invoices. But 
> loading the page times out (it stays busy all time without displaying 
> anything on all browsers).
>
> If I stop the postgresql database server while waiting for the result I get 
> the following error:
>
>   
>> DBD::Pg::st execute failed: server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>> Error!
>>
>> SELECT a.id, a.invnumber, a.ordnumber, a.transdate,
>> a.duedate, (SELECT sum(ac.amount)
>> FROM acc_trans ac
>> JOIN chart c ON (c.id = ac.chart_id)
>> WHERE ac.trans_id = a.id
>> AND ac.approved = '1'
>> AND c.link LIKE '%_tax%') * 1 AS tax,
>> a.amount, (SELECT SUM(ac.amount) * -1 * 1
>> FROM acc_trans ac
>> JOIN chart c ON (c.id = ac.chart_id)
>> WHERE ac.trans_id = a.id
>> AND ac.approved = '1'
>> AND (c.link LIKE '%AR_paid%'
>> OR c.link LIKE '%AR_discount%'
>> OR c.link = '')) AS paid,
>> a.invoice, a.datepaid, a.terms, a.notes,
>> a.shipvia, a.waybill, a.shippingpoint,
>> e.name AS employee, vc.name, vc.customernumber,
>> a.customer_id, a.till, m.name AS manager, a.curr,
>> a.exchangerate, d.description AS department,
>> a.ponumber, a.warehouse_id, w.description AS warehouse,
>> a.description, a.dcn, pm.description AS paymentmethod,
>> a.datepaid - a.duedate AS paymentdiff,
>> ad.address1, ad.address2, ad.city, ad.zipcode, ad.country
>>
>> FROM ar a
>> JOIN customer vc ON (a.customer_id = vc.id)
>> JOIN address ad ON (ad.trans_id = vc.id)
>> LEFT JOIN employee e ON (a.employee_id = e.id)
>> LEFT JOIN employee m ON (e.managerid = m.id)
>> LEFT JOIN department d ON (a.department_id = d.id)
>> LEFT JOIN warehouse w ON (a.warehouse_id = w.id)
>> LEFT JOIN paymentmethod pm ON (pm.id = a.paymentmethod_id)
>>
>>
>> WHERE a.approved = '1'
>> ORDER by 4 ASC,2,17
>> server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>>     
>
>
> So it seems that the server was busy fetching an sql-select. However if I take 
> this query and paste it manually into psql I __instantly__ get the result 
> (256 rows), so I don't think it's a problem of the database. Also my database 
> is very small - about 64kb tar-gzip.
>
> I am using FreeBSD 6.2 with Perl 5.8.8 (base system installations) with:
> p5-DBD-Pg-1.49, p5-DBI-1.60.1, Sql-ledger 2.8.11 (happend also in earlier 
> versions), apache-2.0.61_2. I tried Lighttpd, which produces same problem.
>
> Same happens with AR::Reports::Transaction.
>
> Anyone knows how to fix this? My collegues already want to purchase a 
> commercial product because of this...(pastel partner) :-(
>
> Thanks,
> martin
Seems really strange error. Is this happening with all reports or just 
with these two?

Sometimes the left joins can take the database down for simple queries 
if there is no department/warehouse defined. But your testing does not 
point to database.

Which version of postgres? Try doing vacuumdb to the database

-- 
Purpose-built SQL-Ledger Hosting
http://www.ledger123.com/

Free trial available.
--