An out of memory error when doing a vacuum full - 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.  DB size before we completed the vacuum full was 150GB.

We have recently done a major update to a table, f_pageviews, in our
data warehouse. The f_pageviews table contains about 118 million rows.
Schema is at the end of this message.  We probably touched 80% of those
rows with the update.  We then commenced to drop all indexes on said
table, except the primary key, and attempted to do a vacuum full on the
entire DB.  You can see the output below it failed.  We then tried to do
the vacuum full on the f_pageviews table alone and the same error
occurred.  I did vacuum full on other tables in the schema, one of them
about 8 times larger but with very few dead tuples, and all complete
successfully. We ended up dumping the table and reloading it to
eliminate the dead tuples.  After the reload we did the vacuum full with
no problems.

Does anyone have an explanation as to why this might occur?

Thanks.

--sean


nohup /usr/local/pgsql/bin/vacuumdb -d tripmaster -U tripmaster -f -z -v
-t f_pageviews  > & /tmp/vacuum2.log &

tail -f /tmp/vacuum2.log
INFO:  vacuuming "public.f_pageviews"
INFO:  "f_pageviews": found 17736235 removable, 111796026 nonremovable
row versions in 1552349 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 156 to 244 bytes long.
There were 134565418 unused item pointers.
Total free space (including removable row versions) is 27898448080 bytes.
583420 pages are or will become empty, including 0 at the end of the table.
1088195 pages containing 27860101432 free bytes are potential move
destinations.
CPU 238.91s/27.44u sec elapsed 1261.80 sec.
INFO:  index "f_pageviews_pkey" now contains 111796026 row versions in
210003 pages
DETAIL:  15618120 index row versions were removed.
734 index pages have been deleted, 734 are currently reusable.
CPU 96.09s/139.03u sec elapsed 1569.93 sec.
vacuumdb: vacuuming of table "f_pageviews" in database "tripmaster"
failed: ERROR:  out of memory
DETAIL:  Failed on request of size 350.


 \d f_pageviews
                                   Table "public.f_pageviews"
         Column         |  Type   |                          Modifiers
------------------------+---------+-------------------------------------------------------------
 id                     | integer | not null default
nextval('public.f_pageviews_id_seq'::text)
 date_key               | integer | not null
 time_key               | integer | not null
 content_key            | integer | not null
 location_key           | integer | not null
 session_key            | integer | not null
 subscriber_key         | text    | not null
 persistent_cookie_key  | integer | not null
 ip_key                 | integer | not null
 referral_key           | integer | not null
 servlet_key            | integer | not null
 tracking_key           | integer | not null
 provider_key           | text    | not null
 marketing_campaign_key | integer | not null
 orig_airport           | text    | not null
 dest_airport           | text    | not null
 commerce_page          | boolean | not null default false
 job_control_number     | integer | not null
 sequenceid             | integer | not null default 0
 url_key                | integer | not null
 useragent_key          | integer | not null
 web_server_name        | text    | not null default 'Not Available'::text
 cpc                    | integer | not null default 0
 referring_servlet_key  | integer | default 1
 first_page_key         | integer | default 1
 newsletterid_key       | text    | not null default 'Not Available'::text
Indexes:
    "f_pageviews_pkey" primary key, btree (id)
    "idx_page_views_content" btree (content_key)
    "idx_pageviews_date_dec_2003" btree (date_key) WHERE ((date_key >=
335) AND (date_key <= 365))
    "idx_pageviews_date_nov_2003" btree (date_key) WHERE ((date_key >=
304) AND (date_key <= 334))
    "idx_pageviews_referring_servlet" btree (referring_servlet_key)
    "idx_pageviews_servlet" btree (servlet_key)
    "idx_pageviews_session" btree (session_key)