[postgis-devel] Programming error: Out of Memory

Tue Nov 16 13:57:59 PST 2010

Hello,

I'm getting an error message in my Python script:

pg.ProgrammingError: ERROR:  out of memory
DETAIL:  Failed on request of size 16.

The query that caused the error was:

UPDATE table1 SET string = (SELECT string FROM table2 WHERE
ST_Centroid(table1.wkb_geometry) && table2. the_geom AND
(ST_Distance(ST_Centroid(table1.wkb_geometry), table2.the_geom)<=0)
LIMIT 1 OFFSET 0) WHERE string is null;

table1: a table with one geometry column of type "polygon" (square
polygons). 1277500 rows.
table2: a table with one geometry column of type "polygon". A few rows (12).

Both geometry columns have gist indexes over them.

This query was executed inside a loop, with another 10 similar
queries, using table1 against different tables (table2, table3,
table4, etc). The error is not always produced in the same part of the
loop (sometimes with table2, sometimes with table3...). I attach the
relevant part of PostgreSQL log.

My enviroment: Ubuntu 10.10, PostgreSQL 8.4, PostGIS 1.5.2, 2GB RAM
Things I've tried: Increase the SHMMAX kernel variable from 32MB to
128MB. Increase SHMALL too. Change postgresql.conf parameters
shared_buffers to 120MB, work_mem to 32MB, effective_cache_size to
128MB. Didn't work.

I think it's a problem with data, because I've tested this other times
with other data at worked. What could be happening?

Many thanks in advance

-- 
Jorge Arévalo
Internet & Mobilty Division, DEIMOS
jorge.arevalo at deimos-space.com
http://mobility.grupodeimos.com/
http://gis4free.wordpress.com
-------------- next part --------------
TopMemoryContext: 49416 total in 6 blocks; 5096 free (6 chunks); 44320 used
  TopTransactionContext: 8192 total in 1 blocks; 6600 free (0 chunks); 1592 used
  Operator lookup cache: 24576 total in 2 blocks; 14072 free (6 chunks); 10504 used
  CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
  Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used
  MessageContext: 32768 total in 3 blocks; 8040 free (0 chunks); 24728 used
  Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks); 4344 used
  smgr relation table: 8192 total in 1 blocks; 760 free (0 chunks); 7432 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; 936 free (0 chunks); 88 used
      ExecutorState: 57344 total in 3 blocks; 33416 free (12 chunks); 23928 used
        GiST temporary context: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        ExprContext: 8192 total in 1 blocks; 8176 free (1 chunks); 16 used
        ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
        ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
  Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used
  CacheMemoryContext: 667696 total in 20 blocks; 99040 free (1 chunks); 568656 used
    pg_toast_18592_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
    umts_can_geom_idx: 1024 total in 1 blocks; 136 free (0 chunks); 888 used
    umts_can_pk: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
    pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
    pg_toast_18599_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
    pg_constraint_conrelid_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_user_mapping_user_server_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 used
    pg_user_mapping_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 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; 280 free (0 chunks); 744 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; 344 free (0 chunks); 680 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_foreign_server_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
    pg_foreign_server_name_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
    pg_foreign_data_wrapper_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
    pg_foreign_data_wrapper_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; 280 free (0 chunks); 744 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; 344 free (0 chunks); 680 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; 6784 free (0 chunks); 1408 used
  LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
  Timezones: 78520 total in 2 blocks; 5968 free (0 chunks); 72552 used
  ErrorContext: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used
2010-11-16 19:12:34 CET ERROR:  out of memory
2010-11-16 19:12:34 CET DETAIL:  Failed on request of size 16.
2010-11-16 19:12:34 CET STATEMENT: UPDATE table1 SET string = (SELECT string FROM table2 WHERE ST_Centroid(table1.wkb_geometry) && table2. the_geom AND (ST_Distance(ST_Centroid(table1.wkb_geometry), table2.the_geom)<=0) LIMIT 1 OFFSET 0) WHERE string is null;
2010-11-16 21:20:14 CET LOG:  could not receive data from client: Connection timed out
2010-11-16 21:20:14 CET LOG:  unexpected EOF on client connection
2010-11-16 21:23:04 CET LOG:  could not receive data from client: Connection timed out
2010-11-16 21:23:04 CET LOG:  unexpected EOF on client connection