Обсуждение: max( bool )?

Question and a problem:

I have this query
 select distinct not exists (   select sc1caption from tblstsc1   where (tblstsc1options.surid like surid || '.%'
 or surid=tblstsc1options.surid)     and surid!=tblstsc1options.surid     and sc1caption is not null ) from
tblstsc1optionswhere '164' like surid || '.%' or surid like '164' || '.%' ;
 

Which with current data returns two records, one true and one false.  What
I want to know is if any of the records (fields) are true.  It doesn't
appear max() will do this: ERROR:  Unable to select an aggregate function max(bool)

What can I do?  FYI, this query will be run from within a trigger function
(plpgsql).


Now for the problem..  In attempting to get what I want, I wrote this:

select not exists (select distinct max((select sc1caption from tblstsc1
where (tblstsc1options.surid like surid || '.%' or
surid=tblstsc1options.surid) and surid!=tblstsc1options.surid and
sc1caption is not null)) from tblstsc1options where '164' like surid ||
'.%' or surid like '164' || '.%');

And I get this notice:
 NOTICE:  PortalHeapMemoryFree: 0x0x824a6e8 not in alloc set! NOTICE:  Buffer Leak: [059] (freeNext=54, freePrev=58,
relname=tblstsc1,
blockNum=0, flags=0x14, refcount=-4 -1)

Thanks,
-Cedar

Cedar Cox <cedarc@visionforisrael.com> writes:
> Now for the problem..  In attempting to get what I want, I wrote this:

> select not exists (select distinct max((select sc1caption from tblstsc1
> where (tblstsc1options.surid like surid || '.%' or
> surid=tblstsc1options.surid) and surid!=tblstsc1options.surid and
> sc1caption is not null)) from tblstsc1options where '164' like surid ||
> '.%' or surid like '164' || '.%');

> And I get this notice:

>   NOTICE:  PortalHeapMemoryFree: 0x0x824a6e8 not in alloc set!
>   NOTICE:  Buffer Leak: [059] (freeNext=54, freePrev=58, relname=tblstsc1,
> blockNum=0, flags=0x14, refcount=-4 -1)

(a) What Postgres version is this?

(b) Could we see the schemas for the tables?  (pg_dump -s output is the
best way)
        regards, tom lane

On Wed, 4 Apr 2001, Tom Lane wrote:
> Cedar Cox <cedarc@visionforisrael.com> writes:
> > And I get this notice:
> 
> >   NOTICE:  PortalHeapMemoryFree: 0x0x824a6e8 not in alloc set!
> >   NOTICE:  Buffer Leak: [059] (freeNext=54, freePrev=58, relname=tblstsc1,
> > blockNum=0, flags=0x14, refcount=-4 -1)
> 
> (a) What Postgres version is this?
> 
> (b) Could we see the schemas for the tables?  (pg_dump -s output is the
> best way)

Ver 7.0.2 (sorry, I meant to include this... I think)

Attached is (b).  I have all kinds of horrid custom RI triggers.  I can
send the whole DB schema if necessary.

-Cedar

Cedar Cox <cedarc@visionforisrael.com> writes:
> And I get this notice:
>> 
> NOTICE:  PortalHeapMemoryFree: 0x0x824a6e8 not in alloc set!
> NOTICE:  Buffer Leak: [059] (freeNext=54, freePrev=58, relname=tblstsc1,
> blockNum=0, flags=0x14, refcount=-4 -1)
>> 
> Ver 7.0.2 (sorry, I meant to include this... I think)

Okay, this is a known bug; it's fixed in 7.0.3.  I'd advise staying away
from that sort of construct (sub-select in the output list of a SELECT
DISTINCT) until you update --- in some cases the consequences might be
worse than just an annoying NOTICE :-(.
        regards, tom lane