cnf-ify problem - Mailing list pgsql-hackers

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
130000kbytes. 
>
> 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
finallyfail, 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
280000when the system 
> suddenly got sick.   I was getting all kinds of messages like "cant fork"; bad stuff.  The system did finally recover
onits 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
isbeing 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
followingconditions. 
> > >
> > >     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
beingviewed.   I 
> > > suspect it maps the key values to the relative row position in the display.   Then it uses the mapping to
generatefuture 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
threepart 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
thesekinds 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