PXF fails with ERROR: could not access file "/usr/local/pxf-gp6-6.6.0-2/gpextable/pxf": No such file or directory

Products

• VMware Tanzu Greenplum

Issue/Introduction

After upgrading Platform Extension Framework(PXF) It is not possible to access the external tables using PXF.

An error is reported when reading/writing to external tables:

gpadmin=# select * from ext_table ;
ERROR:  could not access file "/usr/local/pxf-gp6-6.8.0/gpextable/pxf": No such file or directory  (seg0 slice1 XX.XX.XX.XX:30066 pid=1326646)

The installation directory for PXF was changed between the different versions.

Cause

The PXF functions in the database use a file in the PXF installation:

gpadmin=# select proname,probin from pg_proc where probin like '%pxf%';
       proname       |              probin              
---------------------+----------------------------------
 pxf_write           | /usr/local/pxf-gp6-6.8.0/gpextable/pxf
 pxf_read            | /usr/local/pxf-gp6-6.8.0/gpextable/pxf
 pxf_validate        | /usr/local/pxf-gp6-6.8.0/gpextable/pxf
 pxfwritable_import  | /usr/local/pxf-gp6-6.8.0/gpextable/pxf
 pxfwritable_export  | /usr/local/pxf-gp6-6.8.0/gpextable/pxf
 pxfdelimited_import | /usr/local/pxf-gp6-6.8.0/gpextable/pxf
(6 rows)

When the "ALTER PROTOCOL pxf UPDATE" was run, it did not update the function definitions as the extension version did not change.

The installation of PXF rpm package by default installs into "/usr/local/pxf-gp6" or "/usr/local/pxf-gp7".

The installation assumes the installation directory does not change between versions.

Resolution

Log into the database to find the extension version:

gpadmin=# SELECT * FROM pg_extension where extname = 'pxf';
 extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
---------+----------+--------------+----------------+------------+-----------+--------------
 pxf     |       10 |         2200 | f              | 2.1        |           | 
(1 row)

The version above is "2.1"

Copy the file "gpextable/pxf--<VERSION>.sql" from the PXF installation directory to ~/pxf.sql , replace <VERSION> with the appropriate value:

# cp /usr/local/pxf-gp6-6.10.1/gpextable/pxf--2.1.sql ~/pxf.sql

Edit the file ~/pxf.sql:

Change all occurrences of "MODULE_PATHNAME" with "<PXF_INSTALL_DIR>/gpextable/pxf", replace <PXF_INSTALL_DIR> with the appropriate value.
Note: The ".so" extension of the file is removed from the string replacing "MODULE_PATHNAME" 

Remove the "CREATE TRUSTED PROTOCOL" statement at the end of the file.

The file should look similar to:

------------------------------------------------------------------
-- PXF Protocol/Formatters
------------------------------------------------------------------

CREATE OR REPLACE FUNCTION pg_catalog.pxf_write() RETURNS integer
AS '/usr/local/pxf-gp6-6.10.1/gpextable/pxf', 'pxfprotocol_export'
LANGUAGE C STABLE;

CREATE OR REPLACE FUNCTION pg_catalog.pxf_read() RETURNS integer
AS '/usr/local/pxf-gp6-6.10.1/gpextable/pxf', 'pxfprotocol_import'
LANGUAGE C STABLE;

CREATE OR REPLACE FUNCTION pg_catalog.pxf_validate() RETURNS void
AS '/usr/local/pxf-gp6-6.10.1/gpextable/pxf', 'pxfprotocol_validate_urls'
LANGUAGE C STABLE;

CREATE OR REPLACE FUNCTION pg_catalog.pxfwritable_import() RETURNS record
AS '/usr/local/pxf-gp6-6.10.1/gpextable/pxf', 'gpdbwritableformatter_import'
LANGUAGE C STABLE;

CREATE OR REPLACE FUNCTION pg_catalog.pxfwritable_export(record) RETURNS bytea
AS '/usr/local/pxf-gp6-6.10.1/gpextable/pxf', 'gpdbwritableformatter_export'
LANGUAGE C STABLE;

CREATE OR REPLACE FUNCTION pg_catalog.pxfdelimited_import() RETURNS record
AS '/usr/local/pxf-gp6-6.10.1/gpextable/pxf', 'pxfdelimited_import'
LANGUAGE C STABLE;

Run the file using "psql" in each of the databases using PXF, replace <DBNAME> with the appropriate value:

psql -f ~/pxf.sql -c <DBNAME>