[postgis-users] GEOS union() error

Fri Nov 9 05:49:46 PST 2007

Lee,

Just noticed something.  Did you happen to increase your max_connections
setting as well.  As the comment suggests the shared_buffers setting is
dependent on max_connections.

As Kevin noted, I'm not sure increasing temp_buffers will help or not.
My thinking when suggesting that is that if a query plan results in a
materialized intermediate result, I'm not quite sure if that is
considered a temp table or not even though you didn't explicitly state a
bulk create.  

I've been able to up my temp buffers from the default, but I'm on a
32-bit Windows 2003 so not sure if that makes a difference or is limited
by some other memory setting.

Hope that helps,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Lee
Keel
Sent: Wednesday, November 07, 2007 6:09 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] GEOS union() error

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of Lee Keel
> Sent: Wednesday, November 07, 2007 4:56 PM
> To: PostGIS Users Discussion
> Subject: RE: [postgis-users] GEOS union() error
> 
> Sorry to keep on with this, but I have a couple more questions.
> 
> I have posted my current postgresql.conf settings below.  I am running
PG
> 8.2.3\postgis 1.2.1 on a Vista 64bit dual quad with 8GB Ram.
> 
> 
> shared_buffers = 1200MB			# min 128kB or
max_connections*16kB
> #temp_buffers = 16MB			# min 800kB
> work_mem = 150MB				# min 64kB
> 
> 
> 1) Why can't I increase any of these settings?  If I increment any one
of
> these settings or enable temp_buffers the service will not start.  In
> fact,
> the service won't even start if I just set shared_buffers = 1200000.
What
> have I got wrong?
> 
> 2) I have also tried to do 'set work_mem to 1200000;' as I am
executing my
> query and I still get 'Out of memory' errors.  Shouldn't some
postgres.exe
> be ramping up in memory before I get that error?  I have added some
> simplifies into my query to try to decrease the size of shapes being
> brought
> into the buffer and the collection.  This has worked to some degree,
but I
> am back to the problem of memory now.  My latest query is doing
> simplify(buffer(simplify(the_geom, 5), 400), 150) and dumping the
results
> to
> a table.  In which I get a bunch of polygons as expected.  But I can't
do
> a
> collect (nor geomunion) on this secondary table without getting 'out
of
> memory' error.  I have even tried adding limits as low as 2 and it
still
> throws a memory error.  Is this a windows config problem?  Do I need
to
> try
> and upgrade the postgis version to 1.3.1?
> 
> Thanks again for all the help.
> Lee

Some more information...

The table I have inserted the polygons into is 80K.  I have looked
through
the log file and really can't make heads-nor-tails out of it, but I have
pasted it below.  I can't see how an 80K table can take up more than
1200MB
of memory.  Anyway, just thought I would share and hopefully someone can
makes sense of it.

Thanks in advance,
Lee

TopMemoryContext: 57344 total in 6 blocks; 10912 free (25 chunks); 46432
used
TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 chunks); 336
used
Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks);
6392
used
SPI Plan: 1024 total in 1 blocks; 256 free (0 chunks); 768 used
SPI Plan: 7168 total in 3 blocks; 2008 free (0 chunks); 5160 used
SPI Plan: 7168 total in 3 blocks; 520 free (0 chunks); 6648 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 7168 total in 3 blocks; 1672 free (1 chunks); 5496 used
SPI Plan: 7168 total in 3 blocks; 1672 free (1 chunks); 5496 used
SPI Plan: 7168 total in 3 blocks; 536 free (0 chunks); 6632 used
SPI Plan: 7168 total in 3 blocks; 2904 free (0 chunks); 4264 used
SPI Plan: 7168 total in 3 blocks; 3704 free (0 chunks); 3464 used
SPI Plan: 1024 total in 1 blocks; 224 free (0 chunks); 800 used
SPI Plan: 3072 total in 2 blocks; 1888 free (0 chunks); 1184 used
SPI Plan: 3072 total in 2 blocks; 1376 free (0 chunks); 1696 used
SPI Plan: 3072 total in 2 blocks; 1488 free (1 chunks); 1584 used
SPI Plan: 23552 total in 5 blocks; 6240 free (2 chunks); 17312 used
PL/PgSQL function context: 57344 total in 3 blocks; 45520 free (19
chunks);
11824 used
SPI Plan: 3072 total in 2 blocks; 1480 free (1 chunks); 1592 used
PL/PgSQL function context: 24576 total in 2 blocks; 21728 free (12
chunks);
2848 used
Rendezvous variable hash: 8192 total in 1 blocks; 3848 free (0 chunks);
4344
used
PLpgSQL function cache: 24328 total in 2 blocks; 5904 free (0 chunks);
18424
used
PROJ4 Backend PJ MemoryContext Hash: 8192 total in 1 blocks; 5384 free
(0
chunks); 2808 used
CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
Operator class cache: 8192 total in 1 blocks; 4872 free (0 chunks); 3320
used
MessageContext: 8192 total in 1 blocks; 3736 free (2 chunks); 4456 used
smgr relation table: 8192 total in 1 blocks; 1776 free (0 chunks); 6416
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; 880 free (0 chunks); 144 used
ExecutorState: 8192 total in 1 blocks; 4000 free (0 chunks); 4192 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
AggContext: 577560 total in 4 blocks; 57296 free (21 chunks); 520264
used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 528528 total in 2 blocks; 8176 free (0 chunks); 520352 used
Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816 used
CacheMemoryContext: 659000 total in 19 blocks; 132720 free (8 chunks);
526280 used
pk_main_test: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_toast_289053_index: 1024 total in 1 blocks; 288 free (0 chunks); 736
used
main_test_the_geom_gist: 3072 total in 2 blocks; 1696 free (3 chunks);
1376
used
pg_constraint_contypid_index: 1024 total in 1 blocks; 352 free (0
chunks);
672 used
pg_constraint_oid_index: 1024 total in 1 blocks; 352 free (0 chunks);
672
used
pg_constraint_conname_nsp_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
geometry_columns_pk: 1024 total in 1 blocks; 152 free (0 chunks); 872
used
pg_shdepend_reference_index: 1024 total in 1 blocks; 288 free (0
chunks);
736 used
pg_depend_depender_index: 1024 total in 1 blocks; 216 free (0 chunks);
808
used
pg_depend_reference_index: 1024 total in 1 blocks; 216 free (0 chunks);
808
used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 288 free (0
chunks);
736 used
pg_toast_151751_index: 1024 total in 1 blocks; 288 free (0 chunks); 736
used
spatial_ref_sys_pkey: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
main_the_geom_gist: 3072 total in 2 blocks; 1696 free (3 chunks); 1376
used
pk_main: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_constraint_conrelid_index: 1024 total in 1 blocks; 352 free (0
chunks);
672 used
pg_database_datname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632
used
pg_index_indrelid_index: 1024 total in 1 blocks; 352 free (0 chunks);
672
used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 288 free (0 chunks);
736
used
pg_type_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 288 free (0
chunks);
736 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328 free (0
chunks);
696 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 216 free (0
chunks);
808 used
pg_proc_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
pg_operator_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_opclass_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 216 free (0
chunks);
808 used
pg_namespace_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_namespace_nspname_index: 1024 total in 1 blocks; 352 free (0 chunks);
672
used
pg_language_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_language_name_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 328 free (0
chunks);
696 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 352 free (0 chunks);
672
used
pg_authid_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_authid_rolname_index: 1024 total in 1 blocks; 352 free (0 chunks);
672
used
pg_database_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632
used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0
chunks);
696 used
pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0
chunks);
832 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 288 free (0 chunks);
736
used
pg_class_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_cast_source_target_index: 1024 total in 1 blocks; 288 free (0
chunks);
736 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 216 free (0 chunks);
808
used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 288 free (0 chunks); 736
used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 216 free (0 chunks);
808
used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 352 free (0 chunks);
672
used
MdSmgr: 8192 total in 1 blocks; 7288 free (0 chunks); 904 used
LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
Timezones: 49432 total in 2 blocks; 5968 free (0 chunks); 43464 used
ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
2007-11-07 17:04:52 ERROR:  out of memory
2007-11-07 17:04:52 DETAIL:  Failed on request of size 520308.
2007-11-07 17:04:52 STATEMENT:  select collect(the_geom) from main_test
TopMemoryContext: 49152 total in 5 blocks; 9208 free (12 chunks); 39944
used
TopTransactionContext: 24576 total in 2 blocks; 21464 free (15 chunks);
3112
used
Operator class cache: 8192 total in 1 blocks; 4872 free (0 chunks); 3320
used
Autovacuum context: 24576 total in 2 blocks; 22944 free (12 chunks);
1632
used
smgr relation table: 8192 total in 1 blocks; 3840 free (0 chunks); 4352
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: 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: 396856 total in 18 blocks; 99416 free (1 chunks);
297440
used
main_test_the_geom_gist: 3072 total in 2 blocks; 1696 free (3 chunks);
1376
used
pk_main_test: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_index_indrelid_index: 1024 total in 1 blocks; 352 free (0 chunks);
672
used
pg_constraint_conrelid_index: 1024 total in 1 blocks; 352 free (0
chunks);
672 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 288 free (0
chunks);
736 used
pg_autovacuum_vacrelid_index: 1024 total in 1 blocks; 392 free (0
chunks);
632 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks);
696
used
pg_type_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 328 free (0
chunks);
696 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328 free (0
chunks);
696 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 256 free (0
chunks);
768 used
pg_proc_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_operator_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_opclass_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 256 free (0
chunks);
768 used
pg_namespace_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_namespace_nspname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632
used
pg_language_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_language_name_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 328 free (0
chunks);
696 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 352 free (0 chunks);
672
used
pg_authid_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_authid_rolname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632
used
pg_database_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632
used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0
chunks);
696 used
pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0
chunks);
832 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks);
696
used
pg_class_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672
used
pg_cast_source_target_index: 1024 total in 1 blocks; 328 free (0
chunks);
696 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 216 free (0 chunks);
808
used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 328 free (0 chunks); 696
used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 216 free (0 chunks);
808
used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632
used
Per-database table: 57344 total in 3 blocks; 37592 free (13 chunks);
19752
used
Per-database table: 57344 total in 3 blocks; 37592 free (13 chunks);
19752
used
Per-database table: 24576 total in 2 blocks; 13040 free (5 chunks);
11536
used
Per-database table: 57344 total in 3 blocks; 37592 free (13 chunks);
19752
used
Per-database table: 24576 total in 2 blocks; 13040 free (5 chunks);
11536
used
Databases hash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
MdSmgr: 8192 total in 1 blocks; 8080 free (0 chunks); 112 used
LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
Timezones: 49432 total in 2 blocks; 5968 free (0 chunks); 43464 used
ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
2007-11-07 17:05:13 ERROR:  out of memory
2007-11-07 17:05:13 DETAIL:  Failed on request of size 262143996.

This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they are
addressed. If you have received this email in error please notify the
sender. This message contains confidential information and is intended
only for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail.
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.