Pivotal Knowledge Base

Follow

System table creation fails with "ERROR: could not open relation with OID 0 (heapam.c:883)"

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.5.x
OS RHEL 6.x

Symptom

The creation of system tables fails.

This issue can also affect updates to the gpperfmon database. 

Error Message:

In the error sample below, the table creation is successful, but the creation of the associated index table fails.

gpadmin=# create table t1(a int) distributed by (a);
CREATE TABLE
gpadmin=# create index t1_idx on t1 using bitmap(a);
ERROR:  could not open relation with OID 0 (heapam.c:883) 

The error is produced for updates to the gpperfmon database.

2015-11-17 11:31:16.316936 GMT,"gpmon","gpperfmon",p17642,th985237280,"[local]",,2015-11-17 11:31:16 GMT,358829,con64237,cmd1,seg-1,,dx45103,x358829,sx1,"ERROR","XX000","could not open relation with OID 0 (heapam.c:883)",,,,,,"insert into log_alert_history select * from log_alert_tail;",0,,"heapam.c",883,"Stack trace:
1    0xad6b4a postgres  (elog.c:506)
2    0xad8cd8 postgres elog_finish (elog.c:1454)
3    0x4d63f8 postgres relation_open (heapam.c:883)
4    0x5b212a postgres GetAllAOCSFileSegInfo (aocssegfiles.c:215)
5    0x89d293 postgres get_relation_info (plancat.c:534)
6    0x8a2233 postgres build_simple_rel (relnode.c:122)
7    0x85e43c postgres add_base_rels_to_query (initsplan.c:93)
8    0x8664e6 postgres query_planner (planmain.c:172)
9    0x86a5a0 postgres  (planner.c:1531)
10   0x86e113 postgres standard_planner (planner.c:817)
11   0x86ecde postgres planner (planner.c:305)
12   0x966679 postgres  (postgres.c:812)
13   0x969a19 postgres PostgresMain (postgres.c:4675)
14   0x8c701e postgres  (postmaster.c:6688)
15   0x8c9db0 postgres PostmasterMain (postmaster.c:7621)
16   0x7c854f postgres main (main.c:206)
17   0x7f0037b10d5d libc.so.6 __libc_start_main (??:0)
18   0x48a6f9 postgres  (??:0)
"

Cause

The gp_default_storage_options parameter sets the default values for the table storage options when a table is created with the CREATE TABLE command.

Setting this parameter overrides table storage options for system tables also.
For example, creating the system tables, such as indexes, may fail when they are created as APPENDONLY instead of the expected HEAP type.

Resolution

There are 2 ways of resolving the issue:

  1. Upgrade GPDB to version 4.3.6.0, or later.
  2. Otherwise, set the gp_default_storage_options parameter to null. This change can be made at either the session or database level.

Option 1: Session-level parameter change:

Run the following command: 

gpadmin=# set gp_default_storage_options='';
SET
gpadmin=# create index t1_idx on t1 using bitmap(a);
CREATE INDEX

Option 2: Database-level parameter change:

Run the following command:

gpadmin=# ALTER DATABASE mpp_test SET gp_default_storage_options='';
ALTER DATABASE
gpadmin=# \c mpp_test
You are now connected to database "mpp_test" as user "gpadmin".
gpadmin=# create index t1_idx on t1 using bitmap(a);
CREATE INDEX

Comments

Powered by Zendesk