How to Dump Function DDL using pg_dump

Products

• VMware Tanzu Greenplum

Issue/Introduction

When fixing certain inconsistency catalog errors (where one function has different values in certain catalog tables among different segment instances), we need to rebuild some user-defined functions. So recreating those functions is the best way to fix those errors.

 

Environment

Resolution

The process is to back up the function DDL, drop the function, and then create the function using the previous DDL. The goal is to make sure that this function has the same value in catalog tables among all segments. There are 3 steps to generate the function DDL:

• pg_dump -Fc -s -n <schemaname> -f temp.dump <database name>
• pg_restore -l temp.dump | grep FUNCTION > functionalist
• pg_restore -L functionalist temp.dump > yourfunctions.sql

 As pg_dump need to lock the pg_class table, it can't be used while the system is busy. So if the customer knows the function name and how did it work. They can use minirepro tool to extract the DDL for a single function.

 

minirepro -?
Usage: minirepro <database> [options]
Options:
--version show program's version number and exit
-?, --help Show this help message and exit
-h HOST, --host=HOST Specify a remote host
-p PORT, --port=PORT Specify a port other than 5432
-U USER, --user=USER Connect as someone other than current user
-q QUERY_FILE file name that contains the query
-f OUTPUT_FILE minirepro output file name

Additional Information

+ Environment:

• Pivotal Greenplum Database (GPDB) 4.3.x
• Operating System- Red Hat Enterprise Linux 6.x