pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
max( bool )?
4+ messages / 2 participants
[nested] [flat]

* max( bool )?
@ 2001-04-04 12:32 Cedar Cox <cedarc@visionforisrael.com>
  2001-04-04 15:16 ` Re: max( bool )? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Cedar Cox @ 2001-04-04 12:32 UTC (permalink / raw)
  To: pgsql-sql


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 tblstsc1options
  where '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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: max( bool )?
  2001-04-04 12:32 max( bool )? Cedar Cox <cedarc@visionforisrael.com>
@ 2001-04-04 15:16 ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-04-04 22:02   ` Re: max( bool )? Cedar Cox <cedarc@visionforisrael.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2001-04-04 15:16 UTC (permalink / raw)
  To: Cedar Cox <cedarc@visionforisrael.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: max( bool )?
  2001-04-04 12:32 max( bool )? Cedar Cox <cedarc@visionforisrael.com>
  2001-04-04 15:16 ` Re: max( bool )? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-04-04 22:02   ` Cedar Cox <cedarc@visionforisrael.com>
  2001-04-04 22:02     ` Re: max( bool )? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Cedar Cox @ 2001-04-04 22:02 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql


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

Attachments:

sc1_schema (text/plain, 1.3K) download CREATE TABLE "tblstsc1" ( "surid" character varying(50) NOT NULL, "sc1strictnessid" int4 NOT NULL, "sc1caption" text, "defaultoptionid" int4 NOT NULL, CONSTRAINT "tblstsc1_sc1caption" CHECK ((sc1caption <> ''::text)), PRIMARY KEY ("surid") ); CREATE TRIGGER "tblstsc1_before" BEFORE INSERT OR DELETE OR UPDATE ON "tblstsc1" FOR EACH ROW EXECUTE PROCEDURE "tblstsc1_before" (); CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "tblstsc1" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'tblstsc1', 'tblidstrictness', 'UNSPECIFIED', 'sc1strictnessid', 'strictnessid'); CREATE TABLE "tblstsc1options" ( "sc1optionid" int4 NOT NULL, "sc1optionname" character varying(50) NOT NULL, "sc1optionvalue" float4, "surid" character varying(50) NOT NULL, CONSTRAINT "tblstsc1options_sc1optionvalue" CHECK ((sc1optionvalue > 0)), CONSTRAINT "tblstsc1options_sc1optionname" CHECK ((sc1optionname <> ''::"varchar")), PRIMARY KEY ("sc1optionid") ); CREATE INDEX "ix_stsc1options_surid" on "tblstsc1options" using btree ( "surid" "varchar_ops" ); CREATE TRIGGER "tblstsc1options_before" BEFORE INSERT OR DELETE OR UPDATE ON "tblstsc1options" FOR EACH ROW EXECUTE PROCEDURE "tblstsc1options_before" ();
^ permalink raw reply [nested|flat] 4+ messages in thread

* Re: max( bool )?
  2001-04-04 12:32 max( bool )? Cedar Cox <cedarc@visionforisrael.com>
  2001-04-04 15:16 ` Re: max( bool )? Tom Lane <tgl@sss.pgh.pa.us>
  2001-04-04 22:02   ` Re: max( bool )? Cedar Cox <cedarc@visionforisrael.com>
@ 2001-04-04 22:02     ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2001-04-04 22:02 UTC (permalink / raw)
  To: Cedar Cox <cedarc@visionforisrael.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-04-04 22:02 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-04 12:32 max( bool )? Cedar Cox <cedarc@visionforisrael.com>
2001-04-04 15:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-04-04 22:02   ` Cedar Cox <cedarc@visionforisrael.com>
2001-04-04 22:02     ` Tom Lane <tgl@sss.pgh.pa.us>

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