Thread: Views which lead to nested aggregates
I sent this message to pgsql-sql but got no reply and since I'm not sure if it's a missing feature, bug or something else I'll try sending it here. Briefly, I'm getting the error ERROR: ExecAgg: Bad Agg->Target for Agg 0 when layering views to get "nested" aggregates. I tried the following SQL under PostgreSQL 6.3 and 6.4: create table contents ( id int not null, ix int not null, volid int not null, level int not null, bdate datetime not null ); create view freecount as select c1.id as id, c1.ix as ix, count(c2.ix) as freeness from contents c1, contentsc2 where c1.volid = c2.volid and c1.bdate <= c2.bdate and c1.level >= c2.level groupby c1.id, c1.ix; Under 6.3, doing the view creation as an ordinary users I got ERROR: pg_rewrite: Permission denied. which, if I recall, means postgres view support wasn't quite up to letting everyone creates views. Doing the view creation as the postgres superuser succeeded but doing select * from freecount; then crashed the backend. So I installed the recently announced postsgres 6.4 RPM for Linux and tried again. This time, I could create the view as a normal user and it worked fine for that simple select. However, what I actually want to do on top of that view is create view freetapes as select id, min(freeness) - 1 from freecount group by id; (i.e. do the nested aggregation that SQL syntax won't let me do directly.) That view creates successfully but doing a select * from freetapes produces the error message ERROR: ExecAgg: Bad Agg->Target for Agg 0 and doing the explicit query select id, min(freeness) - 1 from freecount group by id; gives the same message. I'm not familiar with postgres internals but it looks as though the internal handling of views is still having trouble with those two levels of aggregations despite the underlying queries being OK. As a data point, the view creation and queries work fine under Informix IDS 7.3 and Sybase. Is this problem with postgres something which is a fixable bug, a missing feature request that is planned to arrive soon (maybe it's in 6.5?) or a missing feature which isn't going to happen any time soon? --Malcolm -- Malcolm Beattie <mbeattie@sable.ox.ac.uk> Unix Systems Programmer Oxford University Computing Services
Malcolm Beattie wrote: > ... > gives the same message. I'm not familiar with postgres internals but > it looks as though the internal handling of views is still having > trouble with those two levels of aggregations despite the underlying > queries being OK. As a data point, the view creation and queries work > fine under Informix IDS 7.3 and Sybase. Is this problem with postgres > something which is a fixable bug, a missing feature request that is > planned to arrive soon (maybe it's in 6.5?) or a missing feature which > isn't going to happen any time soon? Up to now (v6.5) this kind of nested aggregates isn't supported. Not directly over SQL, nor by views. To be sure anything is fine, your views (and however you select from them) should be expressable with a regular SELECT too. In fact the rewrite system has to try to build such a query for it - so if you can't how should the rewriter can? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) #
wieck@debis.com (Jan Wieck) writes: > Up to now (v6.5) this kind of nested aggregates isn't > supported. Not directly over SQL, nor by views. To be sure > anything is fine, your views (and however you select from > them) should be expressable with a regular SELECT too. In > fact the rewrite system has to try to build such a query for > it - so if you can't how should the rewriter can? Still, it ought to either do it or produce a useful error message. 6.4's error message doesn't qualify as useful in my book. But 6.5's behavior is far worse: it accepts the query and cheerfully generates a wrong result! That's definitely a bug. Looking ahead to the larger problem, I believe that the executor is perfectly capable of handling nested aggregate plans --- the trick is to get the planner to produce one. Maybe we need an extension to the parsetree language? It doesn't seem like this ought to be hard to support, it's just that there's no parsetree configuration that represents what we want done. Or, maybe we should rethink the division of labor between the rewriter and planner --- if the rewriter could output a partially-converted plan tree, instead of a parse tree, then it could do as it pleased, but still leave the messy details of lowlevel plan optimization to the planner. regards, tom lane