pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jackson, DeJuan <djackson@cpsgroup.com>
To: Ludovico Magnocavallo <ludo@experian.it>
To: Jackson, DeJuan <djackson@cpsgroup.com>
To: pgsql-general@postgreSQL.org
Cc: PGSQL SQL <pgsql-sql@postgreSQL.org>
Subject: RE: [GENERAL] pqReadData() -- backend closed the channel unexpect edly.
Date: Tue, 9 Feb 1999 16:50:46 -0600
Message-ID: <F10BB1FAF801D111829B0060971D839F65C2E5@cpsmail> (raw)

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.
> 
> 



view thread (6+ messages)  latest in thread

Message-ID: <F10BB1FAF801D111829B0060971D839F65C2E5@cpsmail>
Permalink:  ../F10BB1FAF801D111829B0060971D839F65C2E5@cpsmail/
Also on:    postgresql.org/message-id/F10BB1FAF801D111829B0060971D839F65C2E5@cpsmail

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: djackson@cpsgroup.com, ludo@experian.it, pgsql-general@postgreSQL.org
  Subject: Re: [GENERAL] pqReadData() -- backend closed the channel unexpect edly.
  In-Reply-To: <F10BB1FAF801D111829B0060971D839F65C2E5@cpsmail>
  References: <F10BB1FAF801D111829B0060971D839F65C2E5@cpsmail>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox