Out of memory error when doing an update with IN clause - Mailing list pgsql-general

To all,

The facts:

PostgreSQL 7.4.0 running on BSD 5.1 on Dell 2650 with 4GB RAM, 5 SCSI
drives in hardware RAID 0 configuration.  Database size with indexes is
currently 122GB.  Schema for the table in question is at the end of this
email.  The DB has been vacuumed full and analyzed.  Between  8 and 12
million records are added to the table in question each night.  An
analyze on the entire DB is done after the data has been loaded each night.

The command below was run from psql and failed.  When I removed the last
3 elements in the IN clause (98,105,106) it worked fine.(If I only
removed 1 or 2 it still failed) I then ran the same update statement
again with those remaining 3 elements and it completed without any
problems. Trying to figure out why this would happen?  The system was
not out of memory.  Note that I also have run other queries that use the
form:

SELECT x FROM f_commerce_impressions WHERE id IN (SELECT some large
number of elements to match with id), up to 120k tuples in the sub
select, without problems.

Note that I have also posted another out of memory failure on this list
with subject line:

An out of memory error when doing a vacuum full


Thanks.

--sean



update f_commerce_impressions set servlet_key = 60 where servlet_key in
(68,69,70,71,87,90,94,91,98,105,106);
ERROR:  out of memory
DETAIL:  Failed on request of size 1024.




 \d f_commerce_impressions
                 Table "public.f_commerce_impressions"
       Column       |  Type   |               Modifiers
--------------------+---------+----------------------------------------
 id                 | integer | not null
 page_view_key      | integer | not null
 content_key        | integer | not null
 provider_key       | integer | not null
 is_match           | boolean | not null
 date_key           | integer | not null
 time_key           | integer | not null
 area               | text    | not null
 slot               | integer | not null
 cpc                | integer | not null
 servlet_key        | integer | not null
 web_server_name    | text    | not null default 'Not Available'::text
 job_control_number | integer | not null
Indexes:
    "f_commerce_impressions_pkey" primary key, btree (id)
    "idx_commerce_impressions_date_dec_2003" btree (date_key) WHERE
((date_key >= 335) AND (date_key <= 365))
    "idx_commerce_impressions_date_nov_2003" btree (date_key) WHERE
((date_key >= 304) AND (date_key <= 334))
    "idx_commerce_impressions_page_view" btree (page_view_key)
    "idx_commerce_impressions_servlet" btree (servlet_key)