query failed, not enough memory on 8.3.5 - Mailing list pgsql-bugs

hey folks
(please CC, I'm not subscribed).

I am trying to merge duplicates of two fields in a rather large table
(139M rows, about 7GB of data).
creating helper table, that would hold it.
my feeling is, postgresql shouldn't go beyond memory bounds I am
setting it in conf, never had any problems like it before.

Table:

staty=> \d+ stats_busy
                              Table "public.stats_busy"
      Column     |              Type              |     Modifiers
| Description
----------------+--------------------------------+--------------------
+-------------
  size_to        | bigint                         | not null default 0 |
  size_from      | bigint                         | not null default 0 |
  mac            | integer                        | not null           |
  data_timestamp | timestamp(0) without time zone | not null           |
  data           | integer                        | not null           |
Indexes:
     "d" btree (data)
     "dm" btree (data, mac)
Foreign-key constraints:
     "m" FOREIGN KEY (mac) REFERENCES macs(id) MATCH FULL
Has OIDs: no



conf file:
/var/lib/postgresql/8.3/main# grep -v -E "^#|^[[:space:]]+#|^$"
postgresql.conf
max_connections = 100            # (change requires restart)
shared_buffers = 32MB            # min 128kB or max_connections*16kB
temp_buffers = 32MB            # min 800kB
work_mem = 64MB                # min 64kB
maintenance_work_mem = 32MB        # min 1MB
max_fsm_pages = 4013760            # min max_fsm_relations*16, 6 bytes each
vacuum_cost_page_hit = 1        # 0-10000 credits
vacuum_cost_page_miss = 10        # 0-10000 credits
vacuum_cost_page_dirty = 20        # 0-10000 credits
vacuum_cost_limit = 200        # 1-10000 credits
checkpoint_segments = 64        # in logfile segments, min 1, 16MB each
geqo = on
geqo_threshold = 12
geqo_effort = 5            # range 1-10
geqo_pool_size = 0            # selects default based on effort
geqo_generations = 0            # selects default based on effort
geqo_selection_bias = 2.0        # range 1.5-2.0
default_statistics_target = 200        # range 1-1000
log_destination = 'stderr'        # Valid values are combinations of
logging_collector = on        # Enable capturing of stderr and csvlog
log_directory = 'pg_log'        # directory where log files are written,
log_line_prefix = '[%t %h %d %u %p]: '            # special values:
track_activities = on
track_counts = on
update_process_title = on
autovacuum = on            # Enable autovacuum subprocess?  'on'
autovacuum_naptime = 60min        # time between autovacuum runs
autovacuum_vacuum_threshold = 500000    # min number of row updates before
autovacuum_analyze_threshold = 250000    # min number of row updates before
autovacuum_freeze_max_age = 200000000    # maximum XID age before forced
vacuum
datestyle = 'iso, mdy'
lc_messages = 'en_US'            # locale for system error message
lc_monetary = 'en_US'            # locale for monetary formatting
lc_numeric = 'en_US'            # locale for number formatting
lc_time = 'en_US'                # locale for time formatting
default_text_search_config = 'pg_catalog.english'


~# ulimit -a; file /usr/lib/postgresql/8.3/bin/postgres; free; grep -i
-E 'work_mem|shared_buffers|max_connections' /etc/postgresql/8.3/main/
postgresql.conf
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 16375
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 16375
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
/usr/lib/postgresql/8.3/bin/postgres: ELF 32-bit LSB executable, Intel
80386, version 1 (SYSV), for GNU/Linux 2.6.8, dynamically linked (uses
shared libs), stripped
              total       used       free     shared    buffers
cached
Mem:       2071396     223888    1847508          0       6860
158284
-/+ buffers/cache:      58744    2012652
Swap:      1951888     104716    1847172
max_connections = 50
# note: increasing max_connections costs ~400 bytes of shared memory per
# might also need to raise shared_buffers to support more connections.
shared_buffers = 16000            # min 16 or max_connections*2, 8KB each
work_mem = 128140            # min 64, size in KB
maintenance_work_mem = 65535        # min 1024, size in KB
# max_locks_per_transaction * (max_connections +
max_prepared_transactions)






TopMemoryContext: 49416 total in 6 blocks; 6016 free (5 chunks); 43400
used
   Type information cache: 8192 total in 1 blocks; 1800 free (0
chunks); 6392 used
   Operator lookup cache: 24576 total in 2 blocks; 14072 free (6
chunks); 10504 used
   TopTransactionContext: 8192 total in 1 blocks; 5560 free (0
chunks); 2632 used
   MessageContext: 24576 total in 2 blocks; 12936 free (3 chunks);
11640 used
   Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks);
4344 used
   smgr relation table: 8192 total in 1 blocks; 2808 free (0 chunks);
5384 used
   TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0
chunks); 16 used
   Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
   PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
     PortalHeapMemory: 1024 total in 1 blocks; 896 free (0 chunks);
128 used
       ExecutorState: 24576 total in 2 blocks; 14520 free (15 chunks);
10056 used
         ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
         AggContext: 1981800448 total in 250 blocks; 3768 free (117
chunks); 1981796680 used
           TupleHashTable: 1158668312 total in 150 blocks; 465512 free
(580 chunks); 1158202800 used
         ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
         ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
   Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816
used
   CacheMemoryContext: 667472 total in 20 blocks; 217568 free (8
chunks); 449904 used
     pg_shdepend_depender_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
     pg_shdepend_reference_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_depend_depender_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
     pg_depend_reference_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
     dm: 1024 total in 1 blocks; 280 free (0 chunks); 744 used
     d: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
     pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
     pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
     pg_type_typname_nsp_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
     pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
     pg_statistic_relid_att_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_class_relname_nsp_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_proc_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
     pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 152 free
(0 chunks); 872 used
     pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free
(0 chunks); 832 used
     pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 88 free
(0 chunks); 936 used
     pg_namespace_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_language_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_language_name_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
     pg_database_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_constraint_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
     pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
     pg_conversion_default_index: 1024 total in 1 blocks; 128 free (0
chunks); 896 used
     pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
     pg_cast_source_target_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
     pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_auth_members_role_member_index: 1024 total in 1 blocks; 280
free (0 chunks); 744 used
     pg_auth_members_member_role_index: 1024 total in 1 blocks; 280
free (0 chunks); 744 used
     pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
     pg_amop_opr_fam_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
     pg_aggregate_fnoid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
     pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
     pg_operator_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_amproc_fam_proc_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
     pg_amop_fam_strat_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
     pg_opclass_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_index_indexrelid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
     pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
     pg_class_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
   MdSmgr: 8192 total in 1 blocks; 6472 free (1 chunks); 1720 used
   LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280
used
   Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used
   ErrorContext: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used
[2008-11-28 20:04:14 CET [local] staty citycos 4605]: ERROR:  out of
memory
[2008-11-28 20:04:14 CET [local] staty citycos 4605]: DETAIL:  Failed
on request of size 20.
[2008-11-28 20:04:14 CET [local] staty citycos 4605]: STATEMENT:
create table dups as select count(*) as c, data, mac from stats_busy
group by data,mac having count(*) >1;