Pivotal Knowledge Base

Follow

ERROR: function pg_highest_oid() does not exist

Environment

  • Pivotal Greenplum (GPDB) 4.3.x
  • OS RHEL 6.x

Symptom

User is unable to create, analyze, truncate, alter and join tables in GreenPlum due to the error "pg_highest_oid (gathered 24 results from cmd 'select pg_highest_oid()') (cdboidsync.c:62)". When trying to execute the function you get an error message on some segments:

Error Message:

select pg_highest_oid();
ERROR: function pg_highest_oid() does not exist
LINE 1: select pg_highest_oid();
^
HINT: No function matches the given name and argument types. You may need to add explicit type casts.

Resolution

Follow the steps to resolve this issue:

  1. Find what segments are affected and what segments have good data by running:

    psql -d template1 -Atc "SELECT 'echo PROCESSING ' || hostname ||' ' || port || '\n echo \n' || \$\$PGOPTIONS='-c gp_session_role=utility' psql -e -d postgres -h \$\$ || hostname || \$\$ -p \$\$ || port || \$\$ -c 'select pg_highest_oid();'\$\$ FROM gp_segment_configuration WHERE role='p'" | /bin/bash

    example output:

    PROCESSING sdw1 50002


    select pg_highest_oid();
    pg_highest_oid
    ----------------
    2063033
    (1 row)

    PROCESSING sdw3 50002

    select pg_highest_oid();
    pg_highest_oid
    ----------------
    2063033
    (1 row)

    PROCESSING sdw1 40003

    select pg_highest_oid();
    ERROR: function pg_highest_oid() does not exist
    LINE 1: select pg_highest_oid();
    ^
    HINT: No function matches the given name and argument types. You may need to add explicit type casts.
    (...)

  2. take a backup of pg_proc form a good segment (based on above output it would be sdw1 on port 50002)

    ssh sdw1
    COPY pg_proc WITH OIDS TO '/tmp/gpseg19_pg_proc.csv'
  3. create a fix.sql file for each segment that needs to be fixed like the following:

    begin;
    set allow_system_table_mods = dml;
    COPY pg_proc WITH OIDS TO '/tmp/gpsegXX_pg_proc_backup.csv'
    DELETE FROM pg_proc;
    REINDEX TABLE pg_proc;
    COPY pg_proc WITH OIDS FROM '/tmp/gpseg19_pg_proc.csv' ;
    REINDEX TABLE pg_proc;
    COMMIT ;
  4. Run this fix command on all databases for the affected segment segment (you need -P for PGOPTIONS to skip indexes)

    ssh sdw1
    PGOPTIONS='-P -c gp_session_role=utility' psql MYDATABASE -p 40003 -f fix.sql;
    PGOPTIONS='-P -c gp_session_role=utility' psql postgres -p 40003 -f fix.sql;
    PGOPTIONS='-P -c gp_session_role=utility' psql gpperfmon -p 40003 -f fix.sql;
    PGOPTIONS='-P -c gp_session_role=utility' psql template1 -p 40003 -f fix.sql;
    (other databases)
  5. Verify if the issue is fixed by running step 1) again.
  6. Schedule full gpcheckcat to make sure there are no other corruptions except pg_proc.

Comments

Powered by Zendesk