pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
RE: [GENERAL] pqReadData() -- backend closed the channel unexpect edly.
6+ messages / 3 participants
[nested] [flat]

* RE: [GENERAL] pqReadData() -- backend closed the channel unexpect edly.
@ 1999-02-09 17:15 Jackson, DeJuan <djackson@cpsgroup.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Jackson, DeJuan @ 1999-02-09 17:15 UTC (permalink / raw)
  To: Ludovico Magnocavallo <ludo@experian.it>; pgsql-general@postgreSQL.org; +Cc: pgsql-sql

1st suggestion - If there is no reason behind have temp tables rewrite
your sql to eliminate them (this reduces to just 24 queries):
 INSERT INTO week (address, origbytes, destbytes, when)
 SELECT o.origine, sum(o.bytes), sum(d.bytes), '09/02/1999 9:00:01'
   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 9:00:01' AND '09/02/1999 9:59:59' AND
    d.when BETWEEN '09/02/1999 9:00:01' AND '09/02/1999 9:59:59'
  GROUP BY address, 4;
2nd suggestion - Don't delete the temp tables since they will just be
recreated all. Instead do:
 DELETE FROM temporig;
 DELETE FROM temdest;
3rd suggestion - If you are just stubborn or you actually have a reason
for creating and droping those tables all the time then you might want
to vacuum after you drop the tables (I don't even think this will help
[much]).

You might be able to rewrite your loop into a single SQL statement.
Hope this helps,
	-DEJ

> -----Original Message-----
> I have recently installed postgresql 6.4.2 on a Linux 2.2.0 
> box, and I keep
> getting this error message when I queue a a few queries in a 
> Perl script:
> 
> pqReadData() -- backend closed the channel unexpectedly.
> 
> My script has a loop that performs three queries for every 
> hour of a single
> day: the first two queries select records from a master table 
> ("day", see
> below) and insert them into two temporary tables ("tmporig" 
> and "tmpfrom"),
> and the third query selects records out of those two temp tables, and
> inserts them into a fourth table ("week"). for every step of 
> my loop, I
> create and drop the two temp tables. So that means 24 (hours 
> = steps in my
> loop) x 3 queries = 72 queries, * 24 x 1 drop.
> What happens is, the loop never gets past step 11 or 12, and 
> then I get the
> error above.
> 
> Should I insert code in my script to wait for some event from 
> postgresql, or
> what?
> 
> Here are the details on my tables, queries, etc.
> 
> Table    = day
> +----------------------------------+--------------------------
> --------+-----
> --+
> |              Field               |              Type        
>         |
> Length|
> +----------------------------------+--------------------------
> --------+-----
> --+
> | origine                          | inet                     
>         |
> var |
> | destinazione                     | inet                     
>         |
> var |
> | packets                          | int4                     
>         |
> 4 |
> | bytes                            | int4                     
>         |
> 4 |
> | when                             | datetime                 
>         |
> 8 |
> +----------------------------------+--------------------------
> --------+-----
> --+
> 
> Table    = week
> +----------------------------------+--------------------------
> --------+-----
> --+
> |              Field               |              Type        
>         |
> Length|
> +----------------------------------+--------------------------
> --------+-----
> --+
> | address                          | inet                     
>         |
> var |
> | origbytes                        | int4                     
>         |
> 4 |
> | destbytes                        | int4                     
>         |
> 4 |
> | when                             | datetime                 
>         |
> 8 |
> +----------------------------------+--------------------------
> --------+-----
> --+
> 
> - Loop starts here
> 
> - 1st query (the datetime values get changed in the loop, from
> 00:00:00/00:59:59 to 23:00:00/23:59:59):
> select origine as address, sum(bytes) as origbytes into 
> tmporig from day
> where origine << '194.74.133.0/24' and when between 
> '09/02/1999 9:00:01' and
> '09/02/1999 9:59:59' group by address;
> (table tmporig gets created)
> 
> - 2nd query:
>  select destinazione as address, sum(bytes) as destbytes into 
> tmpdest from
> day where destinazione << '194.74.133.0/24' and when between 
> '09/02/1999
> 9:00:01' and '09/02/1999 9:59:59' group by address;
> (table tmpfrom gets created)
> 
> - 3rd query:
> insert into week select o.address,o.origbytes, d.destbytes, 
> '09/02/1999
> 9:00:01' as when from tmporig o, tmpdest d where o.address=d.address;
> 
> - Temp tables get dropped here and the loop continues
> 
> - output from postmaster does not say anything, here are the 
> last lines from
> one of the crashes (notice that postgresql hangs halfway 
> through the loop
> (12:00:00):
> 
> StartTransactionCommand
> query:  select destinazione as address, sum(bytes) as 
> destbytes into tmpdest
> fr
> om day where destinazione << '194.74.133.0/24' and when 
> between '09/02/1999
> 12:
> 00:01' and '09/02/1999 12:59:59' group by address
> ProcessQuery
> CommitTransactionCommand
> LockReleaseAll: lockmethod=1, pid=17290
> LockReleaseAll: reinitializing lockQueue
> LockReleaseAll: done
> StartTransactionCommand
> query:  insert into week select o.address,o.origbytes, d.destbytes,
> '09/02/1999
>  12:00:01' as when from tmporig o, tmpdest d where o.address=d.address
> ProcessQuery
> 
> 
> - output from the script (perl + dbi) from the same crash:
> 
> dbd_st_execute
> dbd_st_execute: statement = > select destinazione as address, 
> sum(bytes) as
> des
> tbytes into tmpdest from day where destinazione << 
> '194.74.133.0/24' and
> when b
> etween '09/02/1999 12:00:01' and '09/02/1999 12:59:59' group 
> by address<
>     <- execute= -1 at ipacct2.pl line 27.
>     -> execute for DBD::Pg::st (DBI::st=HASH(0x8171e48)~0x8171e90
> '09/02/1999 1
> 2:00:01')
> dbd_bind_ph
>          bind :p1 <== '09/02/1999 12:00:01' (type 0)
> dbd_st_rebind
> bind :p1 <== '09/02/1999 12:00:01' (size 19/20/19, ptype 4, 
> otype 1043)
> dbd_st_execute
> dbd_st_execute: statement = > insert into week select 
> o.address,o.origbytes,
> d.
> destbytes, '09/02/1999 12:00:01' as when from tmporig o, 
> tmpdest d where
> o.addr
> ess=d.address<
>     ERROR EVENT 7 'pqReadData() -- backend closed the channel 
> unexpectedly.
>         This probably means the backend terminated abnormally 
> before or
> while p
> rocessing the request.
> 
> 
> Thanks for any help,
> 
> Ludovico
> 
> 



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [GENERAL] pqReadData() -- backend closed the channel unexpect edly.
@ 1999-02-09 17:30 Ludovico Magnocavallo <ludo@experian.it>
  0 siblings, 0 replies; 6+ messages in thread

From: Ludovico Magnocavallo @ 1999-02-09 17:30 UTC (permalink / raw)
  To: Jackson, DeJuan <djackson@cpsgroup.com>; pgsql-general@postgreSQL.org; +Cc: pgsql-sql

Thanks a lot for your suggestion, that in fact gets me through the loop.
Only problem is, I need to group by origine and get that sum(bytes) and by
destinazione and get that sum(bytes). They are different. Your statement
only groups for origine (in fact it groups by address, which is non-existent
in the day table, and gets me null values).
In the meantime, I have discovered that the number of queries is irrelevant
to the crash, the crash happens in the INSERT statement when the datetime
field is set to 12:00:00 or 12:59:59 or whatever, but always at 12.

Ludo




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* RE: [GENERAL] pqReadData() -- backend closed the channel unexpect edly.
@ 1999-02-09 20:16 Jackson, DeJuan <djackson@cpsgroup.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Jackson, DeJuan @ 1999-02-09 20:16 UTC (permalink / raw)
  To: Ludovico Magnocavallo <ludo@experian.it>; pgsql-general@postgreSQL.org; +Cc: pgsql-sql

> Thanks a lot for your suggestion, that in fact gets me 
> through the loop.
> Only problem is, I need to group by origine and get that 
> sum(bytes) and by
> destinazione and get that sum(bytes). They are different. 
That's why I self join to the 'day' table and sum(o.bytes) and
sum(d.bytes), which should give you different results if origine and
destinazione aren't always the same on each row.

> Your statement
> only groups for origine (in fact it groups by address, which 
> is non-existent
> in the day table, and gets me null values).
You are correct that should have been 'o.origine', sorry I was cut and
pasting your sql and missed that replace.  But the query should work. if
you make that one change.
Corrected query: 
 INSERT INTO week (address, origbytes, destbytes, when)
 SELECT o.origine, sum(o.bytes), sum(d.bytes), '09/02/1999 9:00:01'
   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 9:00:01' AND '09/02/1999 9:59:59' AND
    d.when BETWEEN '09/02/1999 9:00:01' AND '09/02/1999 9:59:59'
  GROUP BY o.origine, 4;

> In the meantime, I have discovered that the number of queries 
> is irrelevant
> to the crash, the crash happens in the INSERT statement when 
> the datetime
> field is set to 12:00:00 or 12:59:59 or whatever, but always at 12.
> 
> Ludo
Rerun your loop and print out your date as you go until you get the
crash, and send it in.  You might actually want to send in the perl-code
in question as well.
BTW. Your query will miss anything that happens on the hour.

	-DEJ



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [GENERAL] pqReadData() -- backend closed the channel unexpect edly.
@ 1999-02-09 21:55 Ludovico Magnocavallo <ludo@experian.it>
  0 siblings, 0 replies; 6+ messages in thread

From: Ludovico Magnocavallo @ 1999-02-09 21:55 UTC (permalink / raw)
  To: Jackson, DeJuan <djackson@cpsgroup.com>; pgsql-general@postgreSQL.org; +Cc: pgsql-sql

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.





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* RE: [GENERAL] pqReadData() -- backend closed the channel unexpect edly.
@ 1999-02-09 22:50 Jackson, DeJuan <djackson@cpsgroup.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Jackson, DeJuan @ 1999-02-09 22:50 UTC (permalink / raw)
  To: Ludovico Magnocavallo <ludo@experian.it>; Jackson, DeJuan <djackson@cpsgroup.com>; pgsql-general@postgreSQL.org; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* pqReadData() -- backend closed the channel unexpectedly.
@ 1999-10-13 01:25 Ismail Kizir <ikizir@teklan.com.tr>
  0 siblings, 0 replies; 6+ messages in thread

From: Ismail Kizir @ 1999-10-13 01:25 UTC (permalink / raw)
  To: pgsql-sql

Hi All,

When I give the attached function, i get this error :

"pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible.
  Terminating.  "

Does someone explain me why?
Thanks in Advance
Regards
Ismail Kizir

Attachments:

  crt.sql (application/octet-stream, 3.3K) download

^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~1999-10-13 01:25 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-09 17:15 RE: [GENERAL] pqReadData() -- backend closed the channel unexpect edly. Jackson, DeJuan <djackson@cpsgroup.com>
1999-02-09 17:30 Re: [GENERAL] pqReadData() -- backend closed the channel unexpect edly. Ludovico Magnocavallo <ludo@experian.it>
1999-02-09 20:16 RE: [GENERAL] pqReadData() -- backend closed the channel unexpect edly. Jackson, DeJuan <djackson@cpsgroup.com>
1999-02-09 21:55 Re: [GENERAL] pqReadData() -- backend closed the channel unexpect edly. Ludovico Magnocavallo <ludo@experian.it>
1999-02-09 22:50 RE: [GENERAL] pqReadData() -- backend closed the channel unexpect edly. Jackson, DeJuan <djackson@cpsgroup.com>
1999-10-13 01:25 pqReadData() -- backend closed the channel unexpectedly. Ismail Kizir <ikizir@teklan.com.tr>

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