Pivotal Knowledge Base

Follow

gpcrondump fails with "ERROR : query to obtain distribution policy of table returned no data"

Environment.

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

Symptom

gpcrondump command fails with the following error message : 

  Master (dbid 1) Host mdw Port 5432 Database wmgpprd BackupFile /data/master/gpseg-1/db_dumps/20160517/gp_dump_1_1_20160517104815.gz: Failed with error:
{ 20160517:10:57:27|gp_dump_agent-[ERROR]:-query to obtain distribution policy of table "drp_site_vertical_mapping" returned no data
20160517:10:57:27|gp_dump_agent-[ERROR]:-*** aborted because of error:
20160517:10:57:29|gp_dump_agent-[ERROR]:-*** aborted because of error:}

Cause

On checking the database, it was found that the table was somehow created without a distribution policy. Also, gpcheckcat currently does not check for the gp_distributed_policy and hence did not return any error message.

wmgpprd=# select gp_segment_id, * from gp_dist_random('gp_distribution_policy') where localoid=86509692; --localoid is oid from pg_class for the table.
gp_segment_id | localoid | attrnums
---------------+----------+----------
(0 rows)

Solution

Find all the tables that have this issue on the database:

wmgpprd=# select oid,* from pg_class where oid not in ( select localoid from gp_distribution_policy) and relstorage='h' and relkind='r' and relnamespace!=11 and relnamespace!=10673;
oid | relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relt
oastrelid | reltoastidxid | relaosegrelid | relaosegidxid | relhasindex | relisshared | relkind | relstorage | relnatts | relchecks | reltrigg
ers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relfrozenxid | relacl | reloptions
----------+---------------------------+--------------+----------+----------+-------+-------------+---------------+----------+-----------+-----
----------+---------------+---------------+---------------+-------------+-------------+---------+------------+----------+-----------+---------
----+----------+----------+---------+------------+------------+-------------+----------------+--------------+--------+------------
69076145 | drp_gopinath | 33291039 | 69076146 | 33263964 | 0 | 69076145 | 0 | 1 | 0 |
0 | 0 | 0 | 0 | f | f | r | h | 0 | 0 |
0 | 0 | 0 | 0 | f | f | f | f | 245792073 | |
86509692 | drp_site_vertical_mapping | 33291039 | 86509693 | 33263964 | 0 | 86509692 | 0 | 1 | 25 |
86509832 | 0 | 0 | 0 | f | f | r | h | 2 | 0 |
0 | 0 | 0 | 0 | f | f | f | f | 245797308 | |
(2 rows)

Redistributing the table and setting the distributed key does not work; it fails with DISTRIBUTED KEY ERROR:

wmgpprd=# ALTER TABLE smbi.drp_site_vertical_mapping SET with (reorganize = false) DISTRIBUTED RANDOMLY;
ERROR: SET DISTRIBUTED BY not supported on non-distributed tables

To resolve this error, create a new table with the source data, rename, and then drop the original table:

wmgpprd=# create table smbi.drp_gopinath_bkp as (select * from smbi.drp_gopinath);
wmgpprd=# ALTER table smbi.drp_gopinath RENAME to drop;
ALTER TABLE
wmgpprd=# ALTER table smbi.drp_gopinath_bkp rename to drp_gopinath;
ALTER TABLE
wmgpprd=# Drop table smbi.drop;
DROP TABLE

 

Comments

  • Avatar
    Faisal Ali

    Hi Archana ,

    The article seems like a duplicate of article "https://discuss.zendesk.com/hc/en-us/articles/205323987"

    Thanks
    Faisal

  • Avatar
    Olen Kline

    Ran into this for the first time in over a year of being DBA on this cluster. Suggested edit, where you exclude (!=) provide the relnamespace that this equates to, my information_schema is not 10673, but 10672 - so having the name, even as a comment, would make this more portable.

Powered by Zendesk