pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmax( bool )?
4+ messages / 2 participants
[nested] [flat]
* max( bool )?
@ 2001-04-04 12:32 Cedar Cox <cedarc@visionforisrael.com>
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 15:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: 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 22:02 Cedar Cox <cedarc@visionforisrael.com>
parent: 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 22:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: Cedar Cox <cedarc@visionforisrael.com>
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