Returned mail: see transcript for details - Mailing list pgsql-general

The original message was received at Thu, 24 Jan 2002 14:41:45 +0100 (MET)
from mail2.zrz.TU-Berlin.DE [130.149.4.14]

   ----- The following addresses had permanent fatal errors -----
fritzlg@gmx.de
    (reason: 550 {mx027-rz3} The recipient does not accept mails from 'yahoo.com' over foreign mailservers)
    (expanded from: <lehmannf@math.tu-berlin.de>)

   ----- Transcript of session follows -----
>>> DATA
<<< 550 {mx027-rz3} The recipient does not accept mails from 'yahoo.com' over foreign mailservers
550 5.1.1 fritzlg@gmx.de... User unknown
<<< 503 {mx027-rz3} RCPT first (#5.5.1)

Reporting-MTA: dns; mail.math.TU-Berlin.DE
Received-From-MTA: DNS; mail2.zrz.TU-Berlin.DE
Arrival-Date: Thu, 24 Jan 2002 14:41:45 +0100 (MET)

Final-Recipient: RFC822; lehmannf@math.tu-berlin.de
Action: expanded (to multi-recipient alias)
Status: 2.0.0
Last-Attempt-Date: Thu, 24 Jan 2002 14:41:47 +0100 (MET)

Final-Recipient: RFC822; lehmannf@math.tu-berlin.de
X-Actual-Recipient: RFC822; fritzlg@gmx.de
Action: failed
Status: 5.1.1
Remote-MTA: DNS; mx0.gmx.de
Diagnostic-Code: SMTP; 550 {mx027-rz3} The recipient does not accept mails from 'yahoo.com' over foreign mailservers
Last-Attempt-Date: Thu, 24 Jan 2002 14:41:47 +0100 (MET)

-- Start of included mail From:  Jan Wieck <janwieck@yahoo.com>

Subject:  Re: [SQL] ERROR (Bug?) in RULE processing ?
To:  Fritz Lehmann-Grube <lehmannf@math.tu-berlin.de>
Date:  Thu, 24 Jan 2002 08:40:01 -0500 (EST)
cc:  Jan Wieck <janwieck@yahoo.com>


Fritz Lehmann-Grube wrote:
>
> Thanks very much, Jan and Tom !
>
> I learned that I simply cannot use an aggregate in a where-clause:

    Well,  you can, if you rewrite it as an aggregating subquery.
    Meaning  that  you  do  ...  SELECT  count(...   instead   of
    count(SELECT...,  but  that's  more  or less academic.  Still
    there are possibile queries that cannot  get  rewritten.  For
    example  if your original INSERT is an INSERT ... SELECT that
    itself uses aggregates in the targetlist.

    So I continue suggesting a trigger.


Jan

>
> arbeitsdb=# select id,type,name from documents d WHERE
> arbeitsdb-# ((d.type = 1) AND (count( (SELECT element_id
> arbeitsdb(#   FROM element_contained_in WHERE (element_id =
> currval('docs_seq')) )) = 0)) ;
> ERROR:  Aggregates not allowed in WHERE clause
>
> That should be in the Manuals ! I didn't find it there ( Bruce Momjian's
> and Jens Hartwig's ).
>
> Greetings Fritz
>
> Jan Wieck wrote:
> >
> > Fritz Lehmann-Grube wrote:
> > >
> > > Can somebody tell what this errormessage means ?
> > > ERROR:  fireRIRrules: failed to remove aggs from qual
> > >
> > > I don't know what "aggs" and "qual" is.
> >
> >     The  rewrite  rule  system  was not able to combine the given
> >     rule into your insert statement,  because  aggregate  clauses
> >     (count()  here)  in the qualification (WHERE ...) are in fact
> >     impossible.
> >
> >     Your best bet it to implement that with a trigger.
> >
> > Jan
> >
> > >
> > > What I did:
> > > arbeitsdb=# select id,type,subtype,name from documents;
> > >  id | type | subtype |     name
> > > ----+------+---------+---------------
> > >   1 |    1 |       5 | motiv_el_01
> > >   2 |    1 |       1 | def_el_01
> > >   3 |    2 |      10 | deduc_el_01
> > >   4 |    2 |      18 | example_el_01
> > > (4 rows)
> > >
> > > arbeitsdb=# insert into documents(type,subtype,name)
> > > arbeitsdb-# values (2,16,'foo');
> > > INSERT 55505 1
> > > arbeitsdb=# delete from documents where oid=55505;
> > > DELETE 1
> > > arbeitsdb=# CREATE RULE doc_contained AS ON INSERT TO documents WHERE
> > > arbeitsdb-# ((NEW.type = 1) AND (count( (SELECT element_id
> > > arbeitsdb(#   FROM element_contained_in WHERE (element_id =
> > > currval('docs_seq')) )) = 0))
> > > arbeitsdb-# DO INSTEAD NOTHING;
> > > CREATE
> > > arbeitsdb=# insert into documents(type,subtype,name)
> > > arbeitsdb-# values (2,16,'foo');
> > > ERROR:  fireRIRrules: failed to remove aggs from qual
> > >
> > > Thanks  Fritz
> > >
>


--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

-- End of included mail.

----- End of forwarded message from Mail Delivery Subsystem -----

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com