pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Malcolm Beattie <mbeattie@sable.ox.ac.uk>
To: pgsql-sql@postgresql.org
Subject: ExecAgg: Bad Agg->Target for Agg 0
Date: Wed, 16 Jun 1999 16:54:54 +0100 (BST)
Message-ID: <E10uI1e-0007jX-00@sable.ox.ac.uk> (raw)

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 <mbeattie@sable.ox.ac.uk>
Unix Systems Programmer
Oxford University Computing Services



Message-ID: <E10uI1e-0007jX-00@sable.ox.ac.uk>
Permalink:  ../E10uI1e-0007jX-00@sable.ox.ac.uk/
Also on:    postgresql.org/message-id/E10uI1e-0007jX-00@sable.ox.ac.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: mbeattie@sable.ox.ac.uk
  Subject: Re: ExecAgg: Bad Agg->Target for Agg 0
  In-Reply-To: <E10uI1e-0007jX-00@sable.ox.ac.uk>
  References: <E10uI1e-0007jX-00@sable.ox.ac.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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