Pivotal Knowledge Base

Follow

GPLOAD error: "53200 out of shared memory adding portal increments"

Environment 

Pivotal Greenplum: 4.3.x

OS: RHEL 6.x

Symptom

While running gpload, the job fails, returning the following error:

ERROR","53200","out of shared memory adding portal increments",,"You may need to increase max_resource_portals_per_transaction."

Complete Error Message

2017-06-09 02:27:43 EDT,98317350,con9254060,cmd1,seg-1,,dx22330340,x98317350,sx1,"LOG","00000","statement: select nt.nspname as table_schema,
2017-06-09 02:27:43.297426 EDT,"ami_stage_etl_user","fpl",p633215,th65394464,"10.110.11.241","55283",2017-06-09 02:27:43 EDT,98317350,con9254060,cmd2,seg-1,,dx22330340,x98317350,sx1,"WARNING","01000","duplicate portal id 0 for proc 633215",,,,,,"select nt.nspname as table_schema, <---- this here is your issue and its explained below
2017-06-09 02:27:43.297676 EDT,"ami_stage_etl_user","fpl",p633215,th65394464,"10.110.11.241","55283",2017-06-09 02:27:43 EDT,98317350,con9254060,cmd2,seg-1,,dx22330340,x98317350,sx1,"ERROR","53200","out of shared memory adding portal increments",,"You may need to increase max_resource_portals_per_transaction.",,,,"select nt.nspname as table_schema,

Cause 

In some cases, the Greenplum Database generates an error when Pivotal Query Optimizer (PQO) is enabled and generates multiple query plans for a query. The error occurs during PQO query plan generation due to an incorrect metadata setting that causes a resource to be locked more than once. 

Resolution

Currently, we have 2 workarounds available:

OPTION 1

Run the query with Optimizer=off

OPTION 2

Increase the value of "max_resource_portals_per_transaction param" from the default of 64 to 128 

1. Current setting can be checked using the following command

gpconfig -s max_resource_portals_per_transaction
Values on all segments are consistent
GUC : max_resource_portals_per_transaction
Master value: 64
Segment value: 64

2. Settings can be changed using the command below

[gpadmin@mdw ~]$ gpconfig -c max_resource_portals_per_transaction -v 128
20160628:08:34:39:544836 gpconfig:mdw:gpadmin-[INFO]:-completed successfully
[gpadmin@mdw ~]$

3. Verify GUC has been correctly changed

[gpadmin@mdw ~]$ gpconfig -s max_resource_portals_per_transaction
Values on all segments are consistent
GUC : max_resource_portals_per_transaction
Master value: 128
Segment value: 128

This issue has been fixed in Greenplum 4.3.9 and above. For a full resolution, upgrade to Greenplum 4.3.9 or higher. 

 

 

Comments

Powered by Zendesk