> 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=28912and 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=28932and 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=28944and 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=28959and 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
andphredsum=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@alumni.caltech.edu
South Pasadena, California