Timestamp inconsistencies when using PXF to write to SQL Database with JDBC driver

Products

• VMware Tanzu Greenplum

Issue/Introduction

Timestamp inconsistencies can be observed when using PXF to create external writable tables to a SQL database through JDBC.

When inserting the current timestamp into the external table and reading it, the value will show a timestamp in the future.

The issue can be reproduced by creating the following readable and writable external tables:

gpadmin=# create writable external table time_write(datum timestamp)
gpadmin-# location('pxf://ambari.time?PROFILE=Jdbc&JDBC_DRIVER=org.postgresql.Driver&DB_URL=jdbc:postgresql://hdp-26-ambari:5432/ambari&USER=ambari&PASS=bigdata')
gpadmin-# format 'custom'  (FORMATTER='pxfwritable_export');
CREATE EXTERNAL TABLE

gpadmin=# create external table time_read(datum timestamp)
gpadmin-# location('pxf://ambari.time?PROFILE=Jdbc&JDBC_DRIVER=org.postgresql.Driver&DB_URL=jdbc:postgresql://hdp-26-ambari:5432/ambari&USER=ambari&PASS=bigdata')
gpadmin-# FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
CREATE EXTERNAL TABLE

Then inserting current timestamp into the table:

gpadmin=# insert into time_write select current_timestamp;
INSERT 0 1

Compare current timestamp with the inserted data:

gpadmin=# select (current_timestamp);
              now
-------------------------------
 2019-09-12 13:40:21.045566+01
(1 row)

gpadmin=# select * from time_read;
          datum
-------------------------
 2019-09-12 13:51:26.762
(1 row)

Environment

Product Version: 5.19

Resolution

This has been identified as an issue with the function used for parsing timestamps in PXF v 5.4.0 which is shipped with GPDB 5.19. 

The issue has been fixed and is included in PXF 5.5.1 shipped with GPDB 5.20.
The recommended action is to upgrade to GPDB 5.20 or later.