Postgres Pro
Downloads
Home   >   mailing lists

Thread: Where is a mistake?

Where is a mistake?

From
Alexandr Listopad
Date:
CREATE VIEW v1 AS SELECT n.net AS net,                       n.sh_desc AS net_name,                       count(s.ip)
AStotal_q        sum(s.bytes)/1024 AS Kb                       FROM zgia_nets n,                       squid_logs s
                 WHERE network_sub(s.ip,n.net)                       AND
date_eq(dt::date,'24-05-2000')                      AND                       s.peer_stat != 'NONE'
 GROUP BY net,net_name                       ORDER BY Kb;
 

stats=> select t1.net,t1.net_name,t1.total_q,t1.total_q/sum(t2.total_q) from v1 t1,
v1 t2 group by 1,2,3;

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.


where is a mistake? or is it a bug?

I have PGSQL v 6.5.3.


--Laa.

Re: Where is a mistake?

From
Ed Loehr
Date:
Alexandr Listopad wrote:
> 
> CREATE VIEW v1 AS SELECT n.net AS net,
>                         n.sh_desc AS net_name,
>                         count(s.ip) AS total_q
>                         sum(s.bytes)/1024 AS Kb
>                         FROM zgia_nets n,
>                         squid_logs s
>                         WHERE network_sub(s.ip,n.net)
>                         AND
>                         date_eq(dt::date,'24-05-2000')
>                         AND
>                         s.peer_stat != 'NONE'
>                         GROUP BY net,net_name
>                         ORDER BY Kb;
> 
> stats=> select t1.net,t1.net_name,t1.total_q,t1.total_q/sum(t2.total_q) from v1 t1,
> v1 t2 group by 1,2,3;
> 
> 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.
> 
> where is a mistake? or is it a bug?
> 
> I have PGSQL v 6.5.3.

Mistake #1:  using 6.5.3 instead of 7.0  :)
Mistake #2:  aggregates are not supported in views  :(

Loaded question:  What does your server log show when this happens?

Regards,
Ed Loehr

Re: Where is a mistake?

From
Tom Lane
Date:
Ed Loehr <eloehr@austin.rr.com> writes:
>> I have PGSQL v 6.5.3.

> Mistake #1:  using 6.5.3 instead of 7.0  :)

Check --- 7.0 has a lot of bugfixes in this area.

> Mistake #2:  aggregates are not supported in views  :(

Not so much aggregates as GROUP BY.  In this case he's attempting to do
two levels of aggregation and grouping, one in the view and the other
in the calling SELECT.  There's no way to make that work in the current
implementation of views, because it can't be rewritten into a single
legal-SQL statement.  I trust that 7.0 won't crash, but it will tell you
it can't do it.

We're planning to fix this for 7.2, by redoing the querytree
representation so that views can be handled as separate processing steps
in a pipeline, rather than as a rule that transforms the original query.
It's not a small job though :-(
        regards, tom lane

Examples.

From
Alexandr Listopad
Date:
hi

Is there many (many-many...) examples of SQL-queries on the i-net?
Where, if yes?

--Laa.