Received: from oxmail.ox.ac.uk (oxmail4.ox.ac.uk [163.1.2.33]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA38151 for ; Wed, 16 Jun 1999 11:54:56 -0400 (EDT) (envelope-from mbeattie@sable.ox.ac.uk) Received: from sable.ox.ac.uk ([163.1.2.4]) by oxmail.ox.ac.uk with esmtp (Exim 2.10 #1) id 10uI1e-0003Hz-00 for pgsql-sql@postgresql.org; Wed, 16 Jun 1999 16:54:54 +0100 Received: from mbeattie by sable.ox.ac.uk with local (Exim 2.12 #1) id 10uI1e-0007jX-00 for pgsql-sql@postgresql.org; Wed, 16 Jun 1999 16:54:54 +0100 Subject: ExecAgg: Bad Agg->Target for Agg 0 To: pgsql-sql@postgresql.org Date: Wed, 16 Jun 1999 16:54:54 +0100 (BST) From: Malcolm Beattie X-Mailer: ELM [version 2.4ME+ PL54 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Message-Id: 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, contents c2 where c1.volid = c2.volid and c1.bdate <= c2.bdate and c1.level >= c2.level group by 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 (though there's an entirely separate buglet if you try to subtract one at count() time instead of min() time :-). 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? (I normally only subscribe to pgsql-admin and pgsql-interfaces but I've now subscribed to pgsql-sql since that seemed to be the most appropriate choice for this query: if not, I apologise.) --Malcolm -- Malcolm Beattie Unix Systems Programmer Oxford University Computing Services