Thread: RE: [GENERAL] pqReadData() -- backend closed the channel unexpect edly.

You are correct... My query assumes uniqueness in o.origine and
d.destinazione which is obviously incorrect.

try these two queries, and let me know the results:
 INSERT INTO week (address, origbytes, when)
 SELECT o.origine, sum(o.bytes), '09/02/1999 00:59:59'::datetime
   FROM day o
  WHERE o.origine << '194.74.133.0/24' AND
   o.when BETWEEN '09/02/1999 00:00:01' AND '09/02/1999 00:59:59'
  GROUP BY o.origine,3;

 UPDATE week
    SET destbytes = sum(d.bytes)
   FROM day d
  WHERE d.destinazione=week.address AND
   week.when = '09/02/1999 00:59:59' AND
   d.when BETWEEN '09/02/1999 00:00:01' AND '09/02/1999 00:59:59'
  GROUP BY d.destinazione;

Have you figured out the problem with the problem you were having with
the date around 12:00?
Let me know,
    DEJ


> -----Original Message-----
> Thanks again, Jackson, but your query is not right :(
> Here is a sample output:
>
> your query:
>
>  SELECT o.origine, sum(o.bytes), sum(d.bytes), '09/02/1999
> 00:59:59'::datetime
>    FROM day o, day d
> where o.origine=d.destinazione
> and o.origine << '194.74.133.0/24'
> and o.when between '09/02/1999 00:00:01' and '09/02/1999 00:59:59'
> and d.when between '09/02/1999 00:00:01' and '09/02/1999 00:59:59'
>   GROUP BY o.origine,4;
>
> result (only for host 194.74.133.11, query is too long):
>
>  194.74.133.11|18569856|13493249|09/02/1999 00:59:59.00 MET
>
> simple query to check first sum:
>
> SELECT o.origine, sum(o.bytes)
>    FROM day o
> where o.origine = '194.74.133.11'
> and o.when between '09/02/1999 00:00:01' and '09/02/1999 00:59:59'
>   GROUP BY o.origine;
>
> 194.74.133.11|250944
>
> Huge difference! Moreover, your query is slow, much much
> slower than two
> separate queries. I would have used it though, if it worked.
> Postgresql
> keeps crashing on my loop. :(
>
> Thanks
>
> L.
>
>