Pivotal Knowledge Base

Follow

Backup fails with "query to obtain distribution policy of table 'xx' returned more than one policy"

Problem

Backup ( gpcrondump ) fails and when you look at the status report generated by the backup tool , you witness the reason for the failure is due to the below error message.

20140409:09:15:07|gp_dump_agent-[INFO]:-Dumping database "p_biafhist"...
20140409:09:15:09|gp_dump_agent-[INFO]:-TASK_SET_SERIALIZABLE
20140409:09:15:25|gp_dump_agent-[INFO]:-TASK_GOTLOCKS
20140409:09:15:28|gp_dump_agent-[ERROR]:-query to obtain distribution policy of table "gp_conf_hc" returned more than one policy
20140409:09:15:28|gp_dump_agent-[ERROR]:-*** aborted because of error:
20140409:09:15:29|gp_dump_agent-[ERROR]:-*** aborted because of error:
gp_dump_agent: Opening file /tmp/exclude_dump_tables_fileTRdErY for exclude tables list
gp_dump_agent: Line #1, value: bulkload.*

Cause

The cause of the issue is due to inconsistent indexes on the catalog table "gp_distribution_policy" , the index for that table shows or returns two values for the table when ideally it should be returning one.

Solution

Reindex the table gp_distribution_policy and it should help to overcome the issue.

psql <database-name> -c "reindex table gp_distribution_policy"

Comments

  • Avatar
    Thol Chidambaram

    Thank you for the post. Command in the solution should include database name as below, to resolve the issue.

    For example
    psql warehousedb -c "reindex table gp_distribution_policy"

  • Avatar
    Faisal Ali

    Thanks Thol , The article has been updated.

  • Avatar
    Yadukula Chengappa

    Hi Faisal,
    We had a similar issue(ERROR: Query to obtain distribution policy of table “test_f” returned more than one policy) while performing the dump.The reason why the dump failed was because the catalog table gp_distribution_policy had more than one row for the table .It was resolved by "alter table table_name set distributed randomly.;" command.

    Regards,
    Yadu

  • Avatar
    Faisal Ali

    Hi Yadu,

    Yes that also works , but the reindex is much better as you don't know how many tables would be complaining about it and since there isn't a easy way to find out, only way would be run the backup each time and then find the problem table and redistribute it.

    Thanks
    Faisal

Powered by Zendesk