Thread
-
cnf-ify problem
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-09T17:49:58Z
Here is a bug report that outlines out cnf-ify problem. Looks like we need a general solution, not just for subselects. --------------------------------------------------------------------------- Forwarded message: > From daveh@insightdist.com Wed Jan 14 10:20:16 1998 > X-Authentication-Warning: u1.abs.net: nobody set sender to insightdist.com!daveh using -f > Message-Id: <34BA82BE.D10BFEEA@insightdist.com> > Date: Mon, 12 Jan 1998 15:53:18 -0500 > From: David Hartwig <daveh@insightdist.com> > Organization: Insight Distribution Systems > X-Mailer: Mozilla 4.04 [en] (Win95; I) > Mime-Version: 1.0 > To: Bruce Momjian <maillist@candle.pha.pa.us> > Subject: Re: [BUGS] General Bug Report: palloc fails with lots of ANDs and ORs > References: <199801121635.LAA05002@candle.pha.pa.us> > Content-Type: multipart/mixed; boundary="------------20C7AC27E8BCA117B23354BE" > > This is a multi-part message in MIME format. > --------------20C7AC27E8BCA117B23354BE > Content-Type: text/plain; charset=us-ascii > Content-Transfer-Encoding: 7bit > > Bruce, > > I did some homework. Here is what I have. The default max data segment size on our (AIX 4.1.4) box is around 130000 kbytes. > > I put together a query which put me just past the threshold of the palloc "out of memory error". It is as follows: > > create table outlet ( > number int, > name varchar(30), > ... > } > > create unique index outlet_key on outlet using btree (number); > > select count(*) from outlet > where > (number = 1 and number = 1 and number = 1) or > (number = 1 and number = 1 and number = 1) or > (number = 1 and number = 1 and number = 1) or > (number = 1 and number = 1 and number = 1) or > (number = 1 and number = 1 and number = 1) or > (number = 1 and number = 1 and number = 1) or > (number = 1 and number = 1 and number = 1) or > (number = 1 and number = 1 and number = 1) or > (number = 1 and number = 1 and number = 1); > > Not pretty but it makes the point. Take out two OR clauses and the query works fine (but a bit slow). > > The above query is all it takes to use up all 130000 Kbytes of memory. And, since the query takes a long time to finally fail, I was able to > observe the memory consumption. > > I extended the max data segment to 300000. And tried again. I could observer the memory consumption up to about 280000 when the system > suddenly got sick. I was getting all kinds of messages like "cant fork"; bad stuff. The system did finally recover on its own. I am not > sure happened there. I know that ulimit puts us right around the physical memory limits of out system. > > Using 300 meg for the above query seems like a bit of a problem. It is difficult to imagine where all that memory is being used. I will > research the problem further if you need more information. > > Bruce Momjian wrote: > > > Try changing your OS default memory size. Unsure how to do this under > > AIX. > > > > > > > > > > > ============================================================================ > > > POSTGRESQL BUG REPORT TEMPLATE > > > ============================================================================ > > > > > > > > > Your name : David Hartwig > > > Your email address : daveh@insightdist.com > > > > > > Category : runtime: back-end: SQL > > > Severity : serious > > > > > > Summary: palloc fails with lots of ANDs and ORs > > > > > > System Configuration > > > -------------------- > > > Operating System : AIX 4.1 > > > > > > PostgreSQL version : 6.2 > > > > > > Compiler used : native CC > > > > > > Hardware: > > > --------- > > > RS 6000 > > > > > > Versions of other tools: > > > ------------------------ > > > NA > > > > > > -------------------------------------------------------------------------- > > > > > > Problem Description: > > > -------------------- > > > The follow is a mail message describing the problem on the PostODBC mailing list: > > > > > > > > > I have run across this also. We traced it down to a failure in the PostgreSQL server. This occurs under the following conditions. > > > > > > 1. MS Access > > > 2. Specify a multi-part key in the link time setup with postgresql > > > 3. Click on table view. > > > > > > What happens is MS Access takes the following steps. First it selects all possible key values for the table being viewed. I > > > suspect it maps the key values to the relative row position in the display. Then it uses the mapping to generate future queries based > > > on the mapping and the rows showing on the screen. The queries take the following form: > > > > > > SELECT keypart1, keypart2, keypart3, col4, col5, col6 ... FROM example_table > > > WHERE > > > (keypart1 = row1keypartval1 AND keypart2 = row1keypartval2 AND keypart3 = row1keypartval3) OR > > > (keypart1 = row2keypartval1 AND keypart2 = row2keypartval2 AND keypart3 = row2keypartval3) OR > > > . > > > . -- 28 lines of this stuff. Why 28... Why not 28 > > > . > > > (keypart1 = row27keypartval1 AND keypart2 = row27keypartval2 AND keypart3 = row27keypartval3) OR > > > (keypart1 = row28keypartval1 AND keypart2 = row28keypartval2 AND keypart3 = row28keypartval3); > > > > > > > > > The PostgreSQL sever chokes on this statement claiming it is out of memory. (palloc) In this example I used a three part key. I > > > do not recall if a three part key is enough to trash the backend. It has been a while. I have tried sending these kinds of statements > > > directly through the psql monitor and get the same result. > > > > > > > > > -------------------------------------------------------------------------- > > > > > > Test Case: > > > ---------- > > > select c1, c1 c3, c4, c5 ... from example_table > > > where > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something) or > > > (c1 = something and c2 = something and c3 = something and c4 = something); > > > > > > > > > -------------------------------------------------------------------------- > > > > > > Solution: > > > --------- > > > > > > > > > -------------------------------------------------------------------------- > > > > > > > > > > > > > -- > > Bruce Momjian > > maillist@candle.pha.pa.us > > > > --------------20C7AC27E8BCA117B23354BE > Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf" > Content-Transfer-Encoding: 7bit > Content-Description: Card for David Hartwig > Content-Disposition: attachment; filename="vcard.vcf" > > begin: vcard > fn: David Hartwig > n: Hartwig;David > org: Insight Distribution Systems > adr: 222 Shilling Circle;;;Hunt Valley ;MD;21030;USA > email;internet: daveh@insightdist.com > title: Manager Research & Development > tel;work: (410)403-2308 > x-mozilla-cpt: ;0 > x-mozilla-html: TRUE > version: 2.1 > end: vcard > > > --------------20C7AC27E8BCA117B23354BE-- > > -- Bruce Momjian maillist@candle.pha.pa.us