Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a set of spatial queries that are failing and I'm struggling with troubleshooting them. I suspect that we're running into some bug, but I'd like to nail things down a bit better so as to be sure and also so that the resulting bug report is both useful and directed to the correct party (postgresql, postgis, or other).

Does anyone have any recommendations for next steps?

Given: PostgreSQL v9.1 installed from http://yum.postgresql.org on fully a patched 32-bit install of CentOS 5

$ rpm -q postgresql91-server
postgresql91-server-9.1.6-1PGDG.rhel5

$ rpm -q geos
geos-3.3.5-1.rhel5

$ rpm -q proj
proj-4.7.0-1.rhel5

$ rpm -q postgis91
postgis91-1.5.5-1.rhel5

$ uname --kernel-name --kernel-release --kernel-version --machine --processor --hardware-platform --operating-system
Linux 2.6.18-308.16.1.el5PAE #1 SMP Tue Oct 2 22:49:17 EDT 2012 i686 i686 i386 GNU/Linux

$ cat /proc/meminfo | grep MemTotal
MemTotal:      5126968 kB

There are three tables involved (the names have been changed for readability), all of them small.

db=# select count(1), st_isclosed(the_geom) from table_1 group by st_isclosed(the_geom);
 count | st_isclosed
-------+-------------
 13268 | f
     1 | t
(2 rows)


db=# select count(1), st_isclosed(the_geom) from table_2 group by st_isclosed(the_geom);
 count | st_isclosed
-------+-------------
    18 | t
(1 row)


db=# select count(1), st_isclosed(the_geom) from table_3 group by st_isclosed(the_geom);
 count | st_isclosed 
-------+-------------
   103 | t
(1 row)

Yet the following queries fail:

db=# SELECT sc.auid,
db-#        h6.huc_6
db-#     FROM table_1 sc
db-#     INNER JOIN table_2 h6
db-#         ON st_intersects(sc.the_geom, h6.the_geom)
db-#     ;
ERROR:  out of memory
DETAIL:  Failed on request of size 452262.

db=# SELECT sc.auid,
db-#        h8.huc_8
db-#     FROM table_1 sc
db-#     INNER JOIN table_3 h8
db-#         ON st_intersects(sc.the_geom, h8.the_geom)
db-#     ;
NOTICE:  St9bad_alloc
ERROR:  GEOS intersects() threw an error!

FWIW, the third query works:

db=# SELECT h6.huc_6,
db-#        h8.huc_8
db-#     FROM table_2 h6
db-#     INNER JOIN table_3 h8
db-#         ON st_intersects(h6.the_geom, h8.the_geom)
db-#     ;

 huc_6  |  huc_8
--------+----------
 040102 | 07030003
 040102 | 09030005
 040102 | 07010103
 .
 .
 .
 070802 | 07100003
 070802 | 07020009
(256 rows)

These queries work on postgres 8.4/CentOS 5 (64 bit). Under 9.1 they also result in a spike in memory usage.

I've run vacuum full, analyze, and reindex on the database and have also adjusted the memory parameters in the postgresql.conf (using pgtune)-- makes no difference.

$ diff postgresql.conf postgresql.conf.20121025 | grep ...
> checkpoint_segments = 16
> default_statistics_target = 100
> effective_cache_size = 2816MB
> maintenance_work_mem = 224MB
> max_connections = 80
> shared_buffers = 896MB
> work_mem = 22MB
< 
< checkpoint_segments = 64
< default_statistics_target = 500
< effective_cache_size = 3584MB
< maintenance_work_mem = 576MB
< max_connections = 20
< shared_buffers = 1152MB
< wal_buffers = 32MB
< work_mem = 120MB

For the out-of-memory error, the pg_log looks like:

TopMemoryContext: 46106288 total in 5587 blocks; 5104 free (6 chunks); 46101184 used
  PostGIS Prepared Geometry Backend MemoryContext Hash: 253952 total in 5 blocks; 116408 free (16 chunks); 137544 used
  CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
  TableSpace cache: 8192 total in 1 blocks; 5640 free (0 chunks); 2552 used
  Operator lookup cache: 24576 total in 2 blocks; 14072 free (6 chunks); 10504 used
  TopTransactionContext: 8192 total in 1 blocks; 7696 free (0 chunks); 496 used
  MessageContext: 57344 total in 3 blocks; 23320 free (7 chunks); 34024 used
  Operator class cache: 8192 total in 1 blocks; 4872 free (0 chunks); 3320 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; 920 free (0 chunks); 104 used
      ExecutorState: 1872363424 total in 6099 blocks; 5819408 free (12 chunks); 1866544016 used
PostGIS Prepared Geometry Context: Prepared context
        GiST temporary context: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
        GiST queue context: 8192 total in 1 blocks; 7248 free (3 chunks); 944 used
        ExprContext: 460480 total in 2 blocks; 8176 free (5 chunks); 452304 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        ExprContext: 460480 total in 2 blocks; 8176 free (5 chunks); 452304 used
  Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used
  CacheMemoryContext: 676968 total in 21 blocks; 190472 free (1 chunks); 486496 used
    pg_toast_90116_index: 1024 total in 1 blocks; 296 free (0 chunks); 728 used
    pg_toast_83744_index: 1024 total in 1 blocks; 296 free (0 chunks); 728 used
    pg_toast_2619_index: 1024 total in 1 blocks; 296 free (0 chunks); 728 used
    hu06_wbd_the_geom_gist: 1024 total in 1 blocks; 120 free (0 chunks); 904 used
    hu06_wbd_pkey: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    stream_auids_current_the_geom_gist: 1024 total in 1 blocks; 120 free (0 chunks); 904 used
    stream_auids_current_pkey: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_index_indrelid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_constraint_conrelid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 296 free (0 chunks); 728 used
    pg_db_role_setting_databaseid_rol_index: 1024 total in 1 blocks; 296 free (0 chunks); 728 used
    pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
    pg_foreign_data_wrapper_name_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_enum_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_class_relname_nsp_index: 1024 total in 1 blocks; 296 free (0 chunks); 728 used
    pg_foreign_server_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_statistic_relid_att_inh_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
    pg_cast_source_target_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_language_name_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_collation_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_amop_fam_strat_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
    pg_index_indexrelid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_ts_template_tmplname_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_ts_config_map_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
    pg_opclass_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_foreign_data_wrapper_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_ts_dict_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_conversion_default_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
    pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
    pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_enum_typid_label_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_ts_config_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_user_mapping_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
    pg_foreign_table_relid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_type_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_aggregate_fnoid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_constraint_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_ts_parser_prsname_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_ts_config_cfgname_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_ts_parser_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_operator_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_namespace_nspname_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_ts_template_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_amop_opr_fam_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
    pg_default_acl_role_nsp_obj_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
    pg_collation_name_enc_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
    pg_ts_dict_dictname_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_type_typname_nsp_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_opfamily_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_class_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
    pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 296 free (0 chunks); 728 used
    pg_proc_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_language_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_namespace_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_amproc_fam_proc_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
    pg_foreign_server_name_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_conversion_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_user_mapping_user_server_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_conversion_name_nsp_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_authid_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_auth_members_member_role_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_tablespace_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_database_datname_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_auth_members_role_member_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_database_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_authid_rolname_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
  MdSmgr: 8192 total in 1 blocks; 7216 free (0 chunks); 976 used
  LOCALLOCK hash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 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
2012-10-25 15:37:18 CDT postgres db 3124 ERROR:  out of memory
2012-10-25 15:37:18 CDT postgres db 3124 DETAIL:  Failed on request of size 452262.

Update: Turns out to have been a bug in the GEOS package.

share|improve this question
1  
You should post your findings as an answer and accept it, I think. Probably a link to a bug report could help as well. –  dezso Nov 15 '12 at 22:48

1 Answer 1

up vote 1 down vote accepted

Turns out to have been a bug in the GEOS package-- there is an announcement on the postgis-users list. The link to the bug report is here.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.