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

[Martin B. <boe...@gm...>]

OK,

I ran the ./ar.pl call from the command line with 'perl -d' and traced the 
execution. It stopped at SL/AA.pl(955) $sth->execute(); when it sends data to 
the database => Problem must be with Postgresql.

Switched on verbosity and debugging on postgres and found that when I send the 
query I get a:

> LOG:  could not send data to client: Operation not permitted

Google doesn't know anything about it, but I am highly sure its because I have 
an IPv6 address in /etc/hosts for localhost AND and IPv4. Changing it to 
127.0.0.1 and the timeout disappears. Instead I get a:

> DBD::Pg::st execute failed: out of memory for query result
> lost synchronization with server: got message type " ", length 1382381157

Also strange: To doublecheck that this is the cause (for whatever reasons, 
btw) I wrote this tiny perl program that just runs the query:

> use DBI;
> my $dbh = DBI->connect( 'dbi:Pg:dbname=ledger_maxnet;host=localhost',
>                         'pgsql',
>                         '',
>   );
>
> my $sth = $dbh->prepare($query);
> $sth->execute;

$query holds the SQL query that fails on the web-application. This ALWAYS 
WORKS, I called it hundred times.. You can skip the ";host=localhost" 
parameter, it makes no differences, it just works.

So it remains mystic. I have a memory error now I don't know how to fix. I 
already changed max_connections, shared_buffers, work_mem etc..  but it 
doesn't solve it. Also I tried to tune kernel parameters like 
kern.ipc.shmmax . The postgresql community suggests to use cursors for large 
results - but after all 256 rows isn't to much for that 1GB ram+1GB swap 
machine that I run it on by far.


Martin




On Tuesday 05 February 2008 11:37:57 Armaghan Saqib wrote:
> Martin Boese wrote:
> > On Monday 04 February 2008 17:16:21 Armaghan Saqib wrote:
> > (...)
> >
> >> 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
> >
> > I am using 8.2.5 . I actually upgraded from 8.1 just to see if this
> > problem will disappear.
> > I suspect more a DBD/DBI problem... Is there any way to test SL cgi
> > script from the command line?
>
> Put the following in a shell script and run in the sql-ledger folder.
> Make sure that line starting with ./ar.pl till end is one line.
>
> #!/usr/local/bin/bash
>
> ./ar.pl
> "path=bin/mozilla&login=armaghan&password=armaghan&action=continue&nextsub=
>transactions&summarY=1&open=Y&l_amount=Y&l
> _description=Y&l_invnumber=Y&l_name=Y&l_paid=Y&l_transdate=Y&vc=customer&AR
>AP=AR&outstanding=1"
>
> --
> Purpose-built SQL-Ledger Hosting
> http://www.ledger123.com/
>
> Free trial available.