Thread: Out of Memory Error on Insert

I am getting an Out of Memory error in my server connection process
while running a large insert query.

Postgres version: "PostgreSQL 8.2.16 on i686-pc-mingw32, compiled by
GCC gcc.exe (GCC) 3.4.2 (mingw-special)"
OS: Windows 7 Professional (v.6.1, build 7601 service pack 1)
The OS is 64 bit but the postgres app is a 32-bit app and I run out of
memory and the server process crashes as soon as I hit 2 GB of memory.
 I assume that is because that is the limit for 32-bit apps.
My client connection is via JDBC in case that is important.

I am creating two temporary tables as follows:

create temporary table simple_group  (groupId int8 not null, elementId
int8 not null, primary key (groupId, elementId))

create temporary table temp_usergroup_acl_entry  (elementId int8 not
null, userGroupId int8 not null, grantFlags int8 not null, denyflags
int8 not null, primary key (elementId, userGroupId))

Table simple_group has about 584 rows.  It represents the membership
of devices (elementId) in a group (groupId).  The crash happens when I
run the query to populate temp_usergroup_acl_entry.  The query is
below followed by the memory map information.  As you can see there
are a lot of full joins.

My goal with the query is to combine the bit maps of access rights
(stored in 8 byte ints) for lists of devices in various groups.  The
groups might have overlapping memberships so that is why I am using
the outer joins and the bit-wise or operator to combine the
permissions of the bit masks.  I know what the values of the bit-masks
should be for each group from some queries that run before this query.
 However, the previous queries do not eat up much memory at all.

Is there something I can do to prevent the out of memory error?  Or
perhaps there is a way I can re-write the query to achieve the same
result?

Insert query:


insert into temp_usergroup_acl_entry(elementId,userGroupId,grantFlags,denyflags)
select coalesce(q0.elementId, q1.elementId) as elementId,
coalesce(q0.userGroupId, q1.userGroupId) as userGroupId,
(coalesce(q0.grantFlags, 0) | coalesce(q1.grantFlags, 0)) as grantFlags,
(coalesce(q0.denyflags, 0) | coalesce(q1.denyflags, 0)) as denyflags from
(select coalesce(q2.elementId, q3.elementId) as elementId,
coalesce(q2.userGroupId, q3.userGroupId) as userGroupId,
(coalesce(q2.grantFlags, 0) | coalesce(q3.grantFlags, 0)) as grantFlags,
(coalesce(q2.denyflags, 0) | coalesce(q3.denyflags, 0)) as denyflags from
(select coalesce(q4.elementId, q5.elementId) as elementId,
coalesce(q4.userGroupId, q5.userGroupId) as userGroupId,
(coalesce(q4.grantFlags, 0) | coalesce(q5.grantFlags, 0)) as grantFlags,
(coalesce(q4.denyflags, 0) | coalesce(q5.denyflags, 0)) as denyflags from
(select coalesce(q6.elementId, q7.elementId) as elementId,
coalesce(q6.userGroupId, q7.userGroupId) as userGroupId,
(coalesce(q6.grantFlags, 0) | coalesce(q7.grantFlags, 0)) as grantFlags,
(coalesce(q6.denyflags, 0) | coalesce(q7.denyflags, 0)) as denyflags from
(select coalesce(q8.elementId, q9.elementId) as elementId,
coalesce(q8.userGroupId, q9.userGroupId) as userGroupId,
(coalesce(q8.grantFlags, 0) | coalesce(q9.grantFlags, 0)) as grantFlags,
(coalesce(q8.denyflags, 0) | coalesce(q9.denyflags, 0)) as denyflags from
(select coalesce(q10.elementId, q11.elementId) as elementId,
coalesce(q10.userGroupId, q11.userGroupId) as userGroupId,
(coalesce(q10.grantFlags, 0) | coalesce(q11.grantFlags, 0)) as grantFlags,
(coalesce(q10.denyflags, 0) | coalesce(q11.denyflags, 0)) as denyflags from
(select coalesce(q12.elementId, q13.elementId) as elementId,
coalesce(q12.userGroupId, q13.userGroupId) as userGroupId,
(coalesce(q12.grantFlags, 0) | coalesce(q13.grantFlags, 0)) as grantFlags,
(coalesce(q12.denyflags, 0) | coalesce(q13.denyflags, 0)) as denyflags from
(select coalesce(q14.elementId, q15.elementId) as elementId,
coalesce(q14.userGroupId, q15.userGroupId) as userGroupId,
(coalesce(q14.grantFlags, 0) | coalesce(q15.grantFlags, 0)) as grantFlags,
(coalesce(q14.denyflags, 0) | coalesce(q15.denyflags, 0)) as denyflags from
(select coalesce(q16.elementId, q17.elementId) as elementId,
coalesce(q16.userGroupId, q17.userGroupId) as userGroupId,
(coalesce(q16.grantFlags, 0) | coalesce(q17.grantFlags, 0)) as grantFlags,
(coalesce(q16.denyflags, 0) | coalesce(q17.denyflags, 0)) as denyflags from
(select coalesce(q18.elementId, q19.elementId) as elementId,
coalesce(q18.userGroupId, q19.userGroupId) as userGroupId,
(coalesce(q18.grantFlags, 0) | coalesce(q19.grantFlags, 0)) as grantFlags,
(coalesce(q18.denyflags, 0) | coalesce(q19.denyflags, 0)) as denyflags from
(select coalesce(q20.elementId, q21.elementId) as elementId,
coalesce(q20.userGroupId, q21.userGroupId) as userGroupId,
(coalesce(q20.grantFlags, 0) | coalesce(q21.grantFlags, 0)) as grantFlags,
(coalesce(q20.denyflags, 0) | coalesce(q21.denyflags, 0)) as denyflags from
(select coalesce(q22.elementId, q23.elementId) as elementId,
coalesce(q22.userGroupId, q23.userGroupId) as userGroupId,
(coalesce(q22.grantFlags, 0) | coalesce(q23.grantFlags, 0)) as grantFlags,
(coalesce(q22.denyflags, 0) | coalesce(q23.denyflags, 0)) as denyflags from
(select coalesce(q24.elementId, q25.elementId) as elementId,
coalesce(q24.userGroupId, q25.userGroupId) as userGroupId,
(coalesce(q24.grantFlags, 0) | coalesce(q25.grantFlags, 0)) as grantFlags,
(coalesce(q24.denyflags, 0) | coalesce(q25.denyflags, 0)) as denyflags from
(select coalesce(q26.elementId, q27.elementId) as elementId,
coalesce(q26.userGroupId, q27.userGroupId) as userGroupId,
(coalesce(q26.grantFlags, 0) | coalesce(q27.grantFlags, 0)) as grantFlags,
(coalesce(q26.denyflags, 0) | coalesce(q27.denyflags, 0)) as denyflags from
(select coalesce(q28.elementId, q29.elementId) as elementId,
coalesce(q28.userGroupId, q29.userGroupId) as userGroupId,
(coalesce(q28.grantFlags, 0) | coalesce(q29.grantFlags, 0)) as grantFlags,
(coalesce(q28.denyflags, 0) | coalesce(q29.denyflags, 0)) as denyflags from
(select coalesce(q30.elementId, q31.elementId) as elementId,
coalesce(q30.userGroupId, q31.userGroupId) as userGroupId,
(coalesce(q30.grantFlags, 0) | coalesce(q31.grantFlags, 0)) as grantFlags,
(coalesce(q30.denyflags, 0) | coalesce(q31.denyflags, 0)) as denyflags from
(select coalesce(q32.elementId, q33.elementId) as elementId,
coalesce(q32.userGroupId, q33.userGroupId) as userGroupId,
(coalesce(q32.grantFlags, 0) | coalesce(q33.grantFlags, 0)) as grantFlags,
(coalesce(q32.denyflags, 0) | coalesce(q33.denyflags, 0)) as denyflags from
(select coalesce(q34.elementId, q35.elementId) as elementId,
coalesce(q34.userGroupId, q35.userGroupId) as userGroupId,
(coalesce(q34.grantFlags, 0) | coalesce(q35.grantFlags, 0)) as grantFlags,
(coalesce(q34.denyflags, 0) | coalesce(q35.denyflags, 0)) as denyflags from
(select coalesce(q36.elementId, q37.elementId) as elementId,
coalesce(q36.userGroupId, q37.userGroupId) as userGroupId,
(coalesce(q36.grantFlags, 0) | coalesce(q37.grantFlags, 0)) as grantFlags,
(coalesce(q36.denyflags, 0) | coalesce(q37.denyflags, 0)) as denyflags from
(select elementId, 10979837 as userGroupId, 3 as grantFlags, 0 as
denyflags from simple_group where groupId=10968327)  as q36
full outer join
(select elementId, 10979837 as userGroupId, 3 as grantFlags, 0 as
denyflags from simple_group where groupId=10957179) as q37
on (q36.elementId=q37.elementId))  as q34
full outer join (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=10957178)
as q35
on (q34.elementId=q35.elementId))  as q32
full outer join (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=10755693)
as q33
on (q32.elementId=q33.elementId))  as q30
full outer join (select elementId, 10979837 as userGroupId, 0 as
grantFlags, 3 as denyflags from simple_group where groupId=1811129) as
q31
on (q30.elementId=q31.elementId))  as q28
full outer join (select elementId, 10979837 as userGroupId, 0 as
grantFlags, 3 as denyflags from simple_group where groupId=1806563) as
q29
on (q28.elementId=q29.elementId))  as q26
full outer join (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=10957153)
as q27
on (q26.elementId=q27.elementId))  as q24
full outer join (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=10966144)
as q25
on (q24.elementId=q25.elementId))  as q22
full outer join (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=10755694)
as q23
on (q22.elementId=q23.elementId))  as q20
full outer join (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=10979064)
as q21
on (q20.elementId=q21.elementId))  as q18
full outer join (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=10755757)
as q19
on (q18.elementId=q19.elementId))  as q16
full outer join (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=5865082) as
q17
on (q16.elementId=q17.elementId))  as q14
full outer join (select elementId, 10979837 as userGroupId, 1 as
grantFlags, 0 as denyflags from simple_group where groupId=10758145)
as q15
on (q14.elementId=q15.elementId))  as q12
full outer join (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=1718259) as
q13
on (q12.elementId=q13.elementId))  as q10
full outer join (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=10755709)
as q11
on (q10.elementId=q11.elementId))  as q8
full outer join (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=1718266) as
q9
on (q8.elementId=q9.elementId))  as q6
full outer join (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=1718273) as
q7
on (q6.elementId=q7.elementId))  as q4
full outer join (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=10965166)
as q5
on (q4.elementId=q5.elementId))  as q2
full outer join (select elementId, 10979837 as userGroupId, 1 as
grantFlags, 0 as denyflags from simple_group where groupId=10979069)
as q3
on (q2.elementId=q3.elementId))  as q0
full outer join (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=759234) as
q1
on (q0.elementId=q1.elementId)


Memory map info:


2011-10-17 23:38:37 NOTICE:  CREATE TABLE / PRIMARY KEY will create
implicit index "simple_group_pkey" for table "simple_group"

2011-10-17 23:38:38 NOTICE:  CREATE TABLE / PRIMARY KEY will create
implicit index "temp_usergroup_acl_entry_pkey" for table
"temp_usergroup_acl_entry"

TopMemoryContext: 204824 total in 9 blocks; 17664 free (79 chunks); 187160 used

unnamed prepared statement: 1237311488 total in 160 blocks; 8936 free
(2 chunks); 1237302552 used

Local Buffer Lookup Table: 8192 total in 1 blocks; 1776 free (0
chunks); 6416 used

SPI Plan: 3072 total in 2 blocks; 592 free (0 chunks); 2480 used

SPI Plan: 3072 total in 2 blocks; 1024 free (0 chunks); 2048 used

Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used

SPI Plan: 3072 total in 2 blocks; 1024 free (0 chunks); 2048 used

SPI Plan: 3072 total in 2 blocks; 960 free (0 chunks); 2112 used

SPI Plan: 3072 total in 2 blocks; 872 free (0 chunks); 2200 used

SPI Plan: 3072 total in 2 blocks; 1136 free (0 chunks); 1936 used

SPI Plan: 3072 total in 2 blocks; 864 free (0 chunks); 2208 used

RI query cache: 24576 total in 2 blocks; 14136 free (5 chunks); 10440 used

TopTransactionContext: 24576 total in 2 blocks; 14360 free (1 chunks);
10216 used

AfterTriggerEvents: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used

S_2: 1024 total in 1 blocks; 752 free (0 chunks); 272 used

Operator class cache: 8192 total in 1 blocks; 4872 free (0 chunks); 3320 used

S_1: 1024 total in 1 blocks; 752 free (0 chunks); 272 used

Prepared Queries: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used

Record information cache: 8192 total in 1 blocks; 1800 free (0
chunks); 6392 used

MessageContext: 24600 total in 2 blocks; 8176 free (2 chunks); 16424 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; 8176 free (1 chunks); 16 used

Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used

CacheMemoryContext: 659000 total in 19 blocks; 91008 free (2 chunks);
567992 used

temp_usergroup_acl_entry_pkey: 1024 total in 1 blocks; 328 free (0
chunks); 696 used

simple_group_pkey: 1024 total in 1 blocks; 328 free (0 chunks); 696 used

pg_constraint_contypid_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_constraint_conname_nsp_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used

pg_constraint_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used

pg_shdepend_depender_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 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_shdepend_reference_index: 1024 total in 1 blocks; 288 free (0
chunks); 736 used

ar_rule_field_pkey: 1024 total in 1 blocks; 352 free (0 chunks); 672 used

ar_rule_pkey: 1024 total in 1 blocks; 352 free (0 chunks); 672 used

usergroup_arrule_pkey: 1024 total in 1 blocks; 288 free (0 chunks); 736 used

ar_ruletextfield_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used

allusersgroup_pkey: 1024 total in 1 blocks; 352 free (0 chunks); 672 used

vsusergroup_pkey: 1024 total in 1 blocks; 352 free (0 chunks); 672 used

vsusergroup_name_key: 1024 total in 1 blocks; 352 free (0 chunks); 672 used

ar_ruletemplate_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used

ar_ruleconditionfield_pkey: 1024 total in 1 blocks; 392 free (0
chunks); 632 used

ar_rule_field_edit_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used

ar_rule_edit_pkey: 1024 total in 1 blocks; 352 free (0 chunks); 672 used

usergroupedit_arruleedit_pkey: 1024 total in 1 blocks; 288 free (0
chunks); 736 used

vsusergroupedit_pkey: 1024 total in 1 blocks; 352 free (0 chunks); 672 used

access_rights_sortorder_key: 1024 total in 1 blocks; 392 free (0
chunks); 632 used

access_rights_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used

access_rights_name_key: 1024 total in 1 blocks; 392 free (0 chunks); 632 used

bounded_groups_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used

groups_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used

groups_name_key: 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; 256 free (0
chunks); 768 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; 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; 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; 392 free (0 chunks); 632 used

MdSmgr: 8192 total in 1 blocks; 6832 free (0 chunks); 1360 used

LOCALLOCK hash: 8192 total in 1 blocks; 1856 free (0 chunks); 6336 used

Timezones: 49432 total in 2 blocks; 5968 free (0 chunks); 43464 used

ErrorContext: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used

2011-10-17 23:38:54 ERROR:  out of memory

2011-10-17 23:38:54 DETAIL:  Failed on request of size 28.

2011-10-17 23:38:54 STATEMENT:  insert into
temp_usergroup_acl_entry(elementId,userGroupId,grantFlags,denyflags)
select coalesce(q0.elementId, q1.elementId) as elementId,
coalesce(q0.userGroupId, q1.userGroupId) as userGroupId,
(coalesce(q0.grantFlags, 0) | coalesce(q1.grantFlags, 0)) as
grantFlags, (coalesce(q0.denyflags, 0) | coalesce(q1.denyflags, 0)) as
denyflags from (select coalesce(q2.elementId, q3.elementId) as
elementId, coalesce(q2.userGroupId, q3.userGroupId) as userGroupId,
(coalesce(q2.grantFlags, 0) | coalesce(q3.grantFlags, 0)) as
grantFlags, (coalesce(q2.denyflags, 0) | coalesce(q3.denyflags, 0)) as
denyflags from (select coalesce(q4.elementId, q5.elementId) as
elementId, coalesce(q4.userGroupId, q5.userGroupId) as userGroupId,
(coalesce(q4.grantFlags, 0) | coalesce(q5.grantFlags, 0)) as
grantFlags, (coalesce(q4.denyflags, 0) | coalesce(q5.denyflags, 0)) as
denyflags from (select coalesce(q6.elementId, q7.elementId) as
elementId, coalesce(q6.userGroupId, q7.userGroupId) as userGroupId,
(coalesce(q6.grantFlags, 0) | coalesce(q7.grantFlags, 0)) as
grantFlags, (coalesce(q6.denyflags, 0) | coalesce(q7.denyflags, 0)) as
denyflags from (select coalesce(q8.elementId, q9.elementId) as
elementId, coalesce(q8.userGroupId, q9.userGroupId) as userGroupId,
(coalesce(q8.grantFlags, 0) | coalesce(q9.grantFlags, 0)) as
grantFlags, (coalesce(q8.denyflags, 0) | coalesce(q9.denyflags, 0)) as
denyflags from (select coalesce(q10.elementId, q11.elementId) as
elementId, coalesce(q10.userGroupId, q11.userGroupId) as userGroupId,
(coalesce(q10.grantFlags, 0) | coalesce(q11.grantFlags, 0)) as
grantFlags, (coalesce(q10.denyflags, 0) | coalesce(q11.denyflags, 0))
as denyflags from (select coalesce(q12.elementId, q13.elementId) as
elementId, coalesce(q12.userGroupId, q13.userGroupId) as userGroupId,
(coalesce(q12.grantFlags, 0) | coalesce(q13.grantFlags, 0)) as
grantFlags, (coalesce(q12.denyflags, 0) | coalesce(q13.denyflags, 0))
as denyflags from (select coalesce(q14.elementId, q15.elementId) as
elementId, coalesce(q14.userGroupId, q15.userGroupId) as userGroupId,
(coalesce(q14.grantFlags, 0) | coalesce(q15.grantFlags, 0)) as
grantFlags, (coalesce(q14.denyflags, 0) | coalesce(q15.denyflags, 0))
as denyflags from (select coalesce(q16.elementId, q17.elementId) as
elementId, coalesce(q16.userGroupId, q17.userGroupId) as userGroupId,
(coalesce(q16.grantFlags, 0) | coalesce(q17.grantFlags, 0)) as
grantFlags, (coalesce(q16.denyflags, 0) | coalesce(q17.denyflags, 0))
as denyflags from (select coalesce(q18.elementId, q19.elementId) as
elementId, coalesce(q18.userGroupId, q19.userGroupId) as userGroupId,
(coalesce(q18.grantFlags, 0) | coalesce(q19.grantFlags, 0)) as
grantFlags, (coalesce(q18.denyflags, 0) | coalesce(q19.denyflags, 0))
as denyflags from (select coalesce(q20.elementId, q21.elementId) as
elementId, coalesce(q20.userGroupId, q21.userGroupId) as userGroupId,
(coalesce(q20.grantFlags, 0) | coalesce(q21.grantFlags, 0)) as
grantFlags, (coalesce(q20.denyflags, 0) | coalesce(q21.denyflags, 0))
as denyflags from (select coalesce(q22.elementId, q23.elementId) as
elementId, coalesce(q22.userGroupId, q23.userGroupId) as userGroupId,
(coalesce(q22.grantFlags, 0) | coalesce(q23.grantFlags, 0)) as
grantFlags, (coalesce(q22.denyflags, 0) | coalesce(q23.denyflags, 0))
as denyflags from (select coalesce(q24.elementId, q25.elementId) as
elementId, coalesce(q24.userGroupId, q25.userGroupId) as userGroupId,
(coalesce(q24.grantFlags, 0) | coalesce(q25.grantFlags, 0)) as
grantFlags, (coalesce(q24.denyflags, 0) | coalesce(q25.denyflags, 0))
as denyflags from (select coalesce(q26.elementId, q27.elementId) as
elementId, coalesce(q26.userGroupId, q27.userGroupId) as userGroupId,
(coalesce(q26.grantFlags, 0) | coalesce(q27.grantFlags, 0)) as
grantFlags, (coalesce(q26.denyflags, 0) | coalesce(q27.denyflags, 0))
as denyflags from (select coalesce(q28.elementId, q29.elementId) as
elementId, coalesce(q28.userGroupId, q29.userGroupId) as userGroupId,
(coalesce(q28.grantFlags, 0) | coalesce(q29.grantFlags, 0)) as
grantFlags, (coalesce(q28.denyflags, 0) | coalesce(q29.denyflags, 0))
as denyflags from (select coalesce(q30.elementId, q31.elementId) as
elementId, coalesce(q30.userGroupId, q31.userGroupId) as userGroupId,
(coalesce(q30.grantFlags, 0) | coalesce(q31.grantFlags, 0)) as
grantFlags, (coalesce(q30.denyflags, 0) | coalesce(q31.denyflags, 0))
as denyflags from (select coalesce(q32.elementId, q33.elementId) as
elementId, coalesce(q32.userGroupId, q33.userGroupId) as userGroupId,
(coalesce(q32.grantFlags, 0) | coalesce(q33.grantFlags, 0)) as
grantFlags, (coalesce(q32.denyflags, 0) | coalesce(q33.denyflags, 0))
as denyflags from (select coalesce(q34.elementId, q35.elementId) as
elementId, coalesce(q34.userGroupId, q35.userGroupId) as userGroupId,
(coalesce(q34.grantFlags, 0) | coalesce(q35.grantFlags, 0)) as
grantFlags, (coalesce(q34.denyflags, 0) | coalesce(q35.denyflags, 0))
as denyflags from (select coalesce(q36.elementId, q37.elementId) as
elementId, coalesce(q36.userGroupId, q37.userGroupId) as userGroupId,
(coalesce(q36.grantFlags, 0) | coalesce(q37.grantFlags, 0)) as
grantFlags, (coalesce(q36.denyflags, 0) | coalesce(q37.denyflags, 0))
as denyflags from (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=10968327)
as q36 full outer join (select elementId, 10979837 as userGroupId, 3
as grantFlags, 0 as denyflags from simple_group where
groupId=10957179) as q37 on (q36.elementId=q37.elementId))  as q34
full outer join (select elementId, 10979837 as userGroupId, 3 as
grantFlags, 0 as denyflags from simple_group where groupId=10957178)
as q35 on (q34.elementId=q35.elementId))  as q32 full outer join
(select elementId, 10979837 as userGroupId, 3 as grantFlags, 0 as
denyflags from simple_group where groupId=10755693) as q33 on
(q32.elementId=q33.elementId))  as q30 full outer join (select
elementId, 10979837 as userGroupId, 0 as grantFlags, 3 as denyflags
from simple_group where groupId=1811129) as q31 on
(q30.elementId=q31.elementId))  as q28 full outer join (select
elementId, 10979837 as userGroupId, 0 as grantFlags, 3 as denyflags
from simple_group where groupId=1806563) as q29 on
(q28.elementId=q29.elementId))  as q26 full outer join (select
elementId, 10979837 as userGroupId, 3 as grantFlags, 0 as denyflags
from simple_group where groupId=10957153) as q27 on
(q26.elementId=q27.elementId))  as q24 full outer join (select
elementId, 10979837 as userGroupId, 3 as grantFlags, 0 as denyflags
from simple_group where groupId=10966144) as q25 on
(q24.elementId=q25.elementId))  as q22 full outer join (select
elementId, 10979837 as userGroupId, 3 as grantFlags, 0 as denyflags
from simple_group where groupId=10755694) as q23 on
(q22.elementId=q23.elementId))  as q20 full outer join (select
elementId, 10979837 as userGroupId, 3 as grantFlags, 0 as denyflags
from simple_group where groupId=10979064) as q21 on
(q20.elementId=q21.elementId))  as q18 full outer join (select
elementId, 10979837 as userGroupId, 3 as grantFlags, 0 as denyflags
from simple_group where groupId=10755757) as q19 on
(q18.elementId=q19.elementId))  as q16 full outer join (select
elementId, 10979837 as userGroupId, 3 as grantFlags, 0 as denyflags
from simple_group where groupId=5865082) as q17 on
(q16.elementId=q17.elementId))  as q14 full outer join (select
elementId, 10979837 as userGroupId, 1 as grantFlags, 0 as denyflags
from simple_group where groupId=10758145) as q15 on
(q14.elementId=q15.elementId))  as q12 full outer join (select
elementId, 10979837 as userGroupId, 3 as grantFlags, 0 as denyflags
from simple_group where groupId=1718259) as q13 on
(q12.elementId=q13.elementId))  as q10 full outer join (select
elementId, 10979837 as userGroupId, 3 as grantFlags, 0 as denyflags
from simple_group where groupId=10755709) as q11 on
(q10.elementId=q11.elementId))  as q8 full outer join (select
elementId, 10979837 as userGroupId, 3 as grantFlags, 0 as denyflags
from simple_group where groupId=1718266) as q9 on
(q8.elementId=q9.elementId))  as q6 full outer join (select elementId,
10979837 as userGroupId, 3 as grantFlags, 0 as denyflags from
simple_group where groupId=1718273) as q7 on
(q6.elementId=q7.elementId))  as q4 full outer join (select elementId,
10979837 as userGroupId, 3 as grantFlags, 0 as denyflags from
simple_group where groupId=10965166) as q5 on
(q4.elementId=q5.elementId))  as q2 full outer join (select elementId,
10979837 as userGroupId, 1 as grantFlags, 0 as denyflags from
simple_group where groupId=10979069) as q3 on
(q2.elementId=q3.elementId))  as q0 full outer join (select elementId,
10979837 as userGroupId, 3 as grantFlags, 0 as denyflags from
simple_group where groupId=759234) as q1 on
(q0.elementId=q1.elementId)

2011-10-17 23:39:03 ERROR:  current transaction is aborted, commands
ignored until end of transaction block

On 10/18/2011 02:52 PM, Mark Priest wrote:
> I am getting an Out of Memory error in my server connection process
> while running a large insert query.
>
> Postgres version: "PostgreSQL 8.2.16 on i686-pc-mingw32, compiled by
> GCC gcc.exe (GCC) 3.4.2 (mingw-special)"
> OS: Windows 7 Professional (v.6.1, build 7601 service pack 1)
> The OS is 64 bit but the postgres app is a 32-bit app and I run out of
> memory and the server process crashes as soon as I hit 2 GB of memory.
>   I assume that is because that is the limit for 32-bit apps.
> My client connection is via JDBC in case that is important.


You're on an old 8.2 subrelease, 8.2.16 from 2010-03-15 that lacks
important bug-fixes; the latest 8.2 is 8.2.22 from 2011-09-26. See:

   http://www.postgresql.org/docs/8.2/static/release.html

More to the point, you're on 8.2 on Windows! I strongly recommend moving
to a newer release if you can, as the newer releases are significantly
improved in performance and reliability on Windows.


For this specific issue, the only thing that comes to mind is whether
you have any AFTER INSERT triggers on this table, or whether you have
any DEFERRABLE constraints (irrespective of whether or not they're
INITIALLY DEFERRED or not). PostgreSQL must keep track of these to
execute them at the end of the transaction, and currently doesn't
support writing this list to disk when it gets too big so it can
eventually fill the backend's available RAM on huge inserts.

If your issue is with a constraint, a workaround is to drop the
constraint, do the insert, then re-establish the constraint and commit
the transaction.

If it's a trigger, that's trickier. Do the insert in smaller batches if
you can, or see if you can disable the trigger, do the inserts, then do
all its work in one go at the end.

--
Craig Ringer

Mark Priest <mark.priest@computer.org> writes:
> I am getting an Out of Memory error in my server connection process
> while running a large insert query.

> Postgres version: "PostgreSQL 8.2.16 on i686-pc-mingw32, compiled by
> GCC gcc.exe (GCC) 3.4.2 (mingw-special)"
> OS: Windows 7 Professional (v.6.1, build 7601 service pack 1)
> The OS is 64 bit but the postgres app is a 32-bit app and I run out of
> memory and the server process crashes as soon as I hit 2 GB of memory.

FWIW, I see excessive memory consumption for this query in 8.2.x but
not in 8.3.x and later.  Some desultory investigation suggests that
the change is associated with rangetable representation improvements
that were made in 8.3.  Since 8.2.x is staring hard at its EOL date,
I suggest now might be a good time to update to a more recent release
series.

            regards, tom lane

Thanks, Craig.

There are no triggers on the tables and the only constraints are the
primary keys.

I am thinking that the problem may be that I have too many full self
joins on the simple_group  table.  I am probably getting a
combinatorial explosion when postgres does cross joins on all the
derived tables.  I think I need to redesign the processing so that I
don't need to do so many joins.

However, I am still curious as to why I am getting an out of memory
error.  I can see how the performance might be terrible on such a
query but I am surprised that postgres doesn't start using the disk at
some point to reduce memory usage.   Could it be that postgres tries
to keep temp tables in memory?

On Tue, Oct 18, 2011 at 3:56 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 10/18/2011 02:52 PM, Mark Priest wrote:
>>
>> I am getting an Out of Memory error in my server connection process
>> while running a large insert query.
>>
>> Postgres version: "PostgreSQL 8.2.16 on i686-pc-mingw32, compiled by
>> GCC gcc.exe (GCC) 3.4.2 (mingw-special)"
>> OS: Windows 7 Professional (v.6.1, build 7601 service pack 1)
>> The OS is 64 bit but the postgres app is a 32-bit app and I run out of
>> memory and the server process crashes as soon as I hit 2 GB of memory.
>>  I assume that is because that is the limit for 32-bit apps.
>> My client connection is via JDBC in case that is important.
>
>
> You're on an old 8.2 subrelease, 8.2.16 from 2010-03-15 that lacks important
> bug-fixes; the latest 8.2 is 8.2.22 from 2011-09-26. See:
>
>  http://www.postgresql.org/docs/8.2/static/release.html
>
> More to the point, you're on 8.2 on Windows! I strongly recommend moving to
> a newer release if you can, as the newer releases are significantly improved
> in performance and reliability on Windows.
>
>
> For this specific issue, the only thing that comes to mind is whether you
> have any AFTER INSERT triggers on this table, or whether you have any
> DEFERRABLE constraints (irrespective of whether or not they're INITIALLY
> DEFERRED or not). PostgreSQL must keep track of these to execute them at the
> end of the transaction, and currently doesn't support writing this list to
> disk when it gets too big so it can eventually fill the backend's available
> RAM on huge inserts.
>
> If your issue is with a constraint, a workaround is to drop the constraint,
> do the insert, then re-establish the constraint and commit the transaction.
>
> If it's a trigger, that's trickier. Do the insert in smaller batches if you
> can, or see if you can disable the trigger, do the inserts, then do all its
> work in one go at the end.
>
> --
> Craig Ringer
>

Mark Priest <mark.priest@computer.org> writes:
> However, I am still curious as to why I am getting an out of memory
> error.  I can see how the performance might be terrible on such a
> query but I am surprised that postgres doesn't start using the disk at
> some point to reduce memory usage.   Could it be that postgres tries
> to keep temp tables in memory?

You're running out of memory in the planner, long before execution ever
happens.  (This is apparent from the memory map, but I also verified it
with a debugger yesterday.)  There really isn't any alternative but to
change the form of the query or upgrade to a newer PG.

            regards, tom lane