Returned mail: see transcript for details

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(at)gmx(dot)de
(reason: 550 {mx027-rz3} The recipient does not accept mails from 'yahoo.com' over foreign mailservers)
(expanded from: <lehmannf(at)math(dot)tu-berlin(dot)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(at)gmx(dot)de(dot)(dot)(dot) 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(at)math(dot)tu-berlin(dot)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(at)math(dot)tu-berlin(dot)de
X-Actual-Recipient: RFC822; fritzlg(at)gmx(dot)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(at)yahoo(dot)com>

Subject: Re: [SQL] ERROR (Bug?) in RULE processing ?
To: Fritz Lehmann-Grube <lehmannf(at)math(dot)tu-berlin(dot)de>
Date: Thu, 24 Jan 2002 08:40:01 -0500 (EST)
cc: Jan Wieck <janwieck(at)yahoo(dot)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(at)Yahoo(dot)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(at)Yahoo(dot)com #

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