server dropping connection

All,

I've been trying to run a nested query via a browser using a php script.
The script looks at a table with a couple years worth of data (6M rows).
If I constrain the search to five or six days of the data it works fine
and displays the result set in the browser, but if I have it parse the
entire table it errors out with this message:

server closed the connection unexpectedly This probably means the server
terminated abnormally before or while processing the request.

The error points at line 33, which is the $query = "SELECT..." in the
script.
The full table query runs fine in psql and the very limited query runs
fine in the php script so I'm thinking there might be some type of
resource starvation or client connection timeout happening. In psql the
full query over two years of data returns a result set (limit 50) in about
three minutes. Any ideas where I should begin looking?

thanks,

r



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: server dropping connection

2006-05-02 (È­), 18:13 -0600, Reed Loefgren ¾²½Ã±æ:
> All,
>=20
> I've been trying to run a nested query via a browser using a php script=
..=20
> The script looks at a table with a couple years worth of data (6M rows)=
..=20
> If I constrain the search to five or six days of the data it works fine=
=20
> and displays the result set in the browser, but if I have it parse the=20
> entire table it errors out with this message:
>=20
> server closed the connection unexpectedly This probably means the serve=
r=20
> terminated abnormally before or while processing the request.
>=20
> The error points at line 33, which is the $query =3D "SELECT..." in the=
=20
> script.
> The full table query runs fine in psql and the very limited query runs=20
> fine in the php script so I'm thinking there might be some type of=20
> resource starvation or client connection timeout happening. In psql the=
=20
> full query over two years of data returns a result set (limit 50) in ab=
out=20
> three minutes. Any ideas where I should begin looking?
>=20
> thanks,

http://httpd.apache.org/docs/1.3/mod/core.html#timeout

says


The TimeOut directive currently defines the amount of time Apache will
wait for three things:

1. The total amount of time it takes to receive a GET request.
2. The amount of time between receipt of TCP packets on a POST or
PUT request.
3. The amount of time between ACKs on transmissions of TCP packets
in responses
4.=20
>=20

i am interested in what the change would be if you wrote a server-side
function, however.

> r
>=20
>=20
>=20
> ---------------------------(end of broadcast)--------------------------=
-
> TIP 5: don't forget to increase your free space map settings
>=20
>=20
>=20
--=20
my site wa=
s
made to help students of many languages learn them faster.






---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: server dropping connection

On May 02 06:13, Reed Loefgren wrote:
> I've been trying to run a nested query via a browser using a php script.
> The script looks at a table with a couple years worth of data (6M rows).
> If I constrain the search to five or six days of the data it works fine
> and displays the result set in the browser, but if I have it parse the
> entire table it errors out with this message:
>
> server closed the connection unexpectedly This probably means the server
> terminated abnormally before or while processing the request.

Just making rhetoric: You removed script execution time limits via
set_time_limit(), right?

I'll suggest turning error_reporting() to E_ALL and using asynchronous
query execution functions, instead of synchron ones. For instance:

$query_cmd = "SELECT ...";
$res = pg_send_query($conn, $query_cmd);
if (!$res)
{
/* Error handling stuff... */
}

while (pg_connection_busy($conn))
{
/*
* Sorry, no clue about connection socket in here.
* We're waiting without select() or poll().
* (I know, I know... PHP sucks.)
*/
usleep(100000); /* 0.1 seconds. */
}

/* Seems like result is ready now. */

$res = pg_get_result($conn);
if (!$res)
{
/* Error handling. */
}

IMHO, this method can output more verbose (and accurate?) query status
when compared to an odd pg_query() call.


Regards.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match