pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedWhere is a mistake?
3+ messages / 3 participants
[nested] [flat]
* Where is a mistake?
@ 2000-05-26 14:31 Alexandr Listopad <laa@laa.zp.ua>
2000-05-26 15:32 ` Re: Where is a mistake? Ed Loehr <eloehr@austin.rr.com>
0 siblings, 1 reply; 3+ messages in thread
From: Alexandr Listopad @ 2000-05-26 14:31 UTC (permalink / raw)
To: pgsql-interfaces
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.
--
Laa.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Where is a mistake?
2000-05-26 14:31 Where is a mistake? Alexandr Listopad <laa@laa.zp.ua>
@ 2000-05-26 15:32 ` Ed Loehr <eloehr@austin.rr.com>
2000-05-26 16:03 ` Re: Where is a mistake? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Ed Loehr @ 2000-05-26 15:32 UTC (permalink / raw)
To: Alexandr Listopad <laa@laa.zp.ua>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Where is a mistake?
2000-05-26 14:31 Where is a mistake? Alexandr Listopad <laa@laa.zp.ua>
2000-05-26 15:32 ` Re: Where is a mistake? Ed Loehr <eloehr@austin.rr.com>
@ 2000-05-26 16:03 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2000-05-26 16:03 UTC (permalink / raw)
To: Ed Loehr <eloehr@austin.rr.com>; +Cc: Alexandr Listopad <laa@laa.zp.ua>; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2000-05-26 16:03 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-26 14:31 Where is a mistake? Alexandr Listopad <laa@laa.zp.ua>
2000-05-26 15:32 ` Ed Loehr <eloehr@austin.rr.com>
2000-05-26 16:03 ` Tom Lane <tgl@sss.pgh.pa.us>
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