Re: memory leak????

> I am getting an error: FATAL 1: Memory exausted in allocSetAlloc().
(taken on-list)

> I get this when I do the attached command. The table definition is the
> second attachment. When I did the select statement, I only had 200 or so
> lines in the table. I was doing the select command in both psql and libpq.

This is known and expected behavior, though certainly undesirable for
your query. I don't know much about it, but I'll guess that you are
exhausting memory just trying to *plan* the query, or that the
bazillion intermediate results from the huge number of "or" clauses is
chewing things up.

I would try two things (again, I'm not recalling all I should here):
1) Do an "explain" on your query. If it fails, set Postgres to use the
genetic optimizer (SET GEQO ON;)
2) Ask on the hackers mailing list. Perhaps there is a way to clear
memory from intermediate results (or a better workaround). I'm
guessing that there is for multiple statements within a transaction,
but maybe not within a single query. The hackers mailing list archives
might have some details on this.

Good luck.

- Thomas

> ----------------------------------------------------------------------
> select seqid, barcode, run from sequence where (seqid=28904 and phredsum=170) or (seqid=28907 and phredsum=48) or (seqid=28912 and phredsum=212) or (seqid=28923 and phredsum=124) or (seqid=28924 and phredsum=224) or (seqid=28928 and phredsum=52) or (seqid=28929 and phredsum=176) or (seqid=28930 and phredsum=197) or (seqid=28931 and phredsum=184) or (seqid=28932 and phredsum=169) or (seqid=28936 and phredsum=274) or (seqid=28937 and phredsum=165) or (seqid=28938 and phredsum=297) or (seqid=28939 and phredsum=172) or (seqid=28942 and phredsum=162) or (seqid=28943 and phredsum=211) or (seqid=28944 and phredsum=246) or (seqid=28945 and phredsum=259) or (seqid=28946 and phredsum=357) or (seqid=28947 and phredsum=295) or (seqid=28955 and phredsum=239) or (seqid=28956 and phredsum=129) or (seqid=28958 and phredsum=13) or (seqid=28959 and phredsum=263) or (seqid=28960 and phredsum=171) or (seqid=28962 and phredsum=46) or (seqid=28963 and phredsum=297) or (seqid=28964 and phredsum=17!
7) or (seqid=28965 and phredsum=97) or (seqid=28967 and phredsum=143) or (seqid=28968 and phredsum=109) or (seqid=28969 and phredsum=233) or (seqid=28976 and phredsum=76);
>
> ----------------------------------------------------------------------
> +----------------------------------+----------------------------------+-------+
> | Field | Type | Length|
> +----------------------------------+----------------------------------+-------+
> | seqid | int4 not null | 4 |
> | barcode | int4 not null | 4 |
> | run | int2 not null | 2 |
> | sequence | text | var |
> | quality | text | var |
> | length | int2 | 2 |
> | seqtime | datetime | 8 |
> | geltype | text | var |
> | phredsum | int2 | 2 |
> | identifier | int4 not null default nextval ( | 4 |
> +----------------------------------+----------------------------------+-------+
> Indices: phredsum_ind
> seqid_ind
> sequence_pkey

--
Thomas Lockhart lockhart(at)alumni(dot)caltech(dot)edu
South Pasadena, California