PostGIS > SQL* - Issue with timestamptz

Hi @jbradfor​ I haven't been able to reproduce the issue in FME 2021.1 with PostgreSQL v11.8 (unfortunately I don't have access to an older version of PostgreSQL). Would you be able to share a full version of your log file with us? If you'd rather share you file privately, please consider uploading it to our Safe Support FTP. If you test the same issue in a newer version of FME, does the problem still occur?

Kind Regards,

Dan Minney

FME Desktop v2021.1.1.0

postgresql v11.13

I can run this query in DBeaver:

select exp_ts from test_table;

But not in FME SQLExec or SQLCreat.

As far as log files go, what I posted is all I have.

Possibly a client/driver issue?

I haven't been able to reproduce the issue still after some further testing. Are you able to provide steps on how you created the Trigger on your table? I may be doing something wrong here so I want to ensure my environment matches yours.

Thanks!

CREATE OR REPLACE FUNCTION test.test_table_exp_ts()

RETURNS TRIGGER AS '

BEGIN

  SELECT TIMESTAMP ''9999-12-31 23:59:59'' AT TIME ZONE ''America/Chicago'' INTO NEW.exp_ts;

  RETURN NEW;

END;

' LANGUAGE plpgsql;

COMMIT ;

DROP TRIGGER IF EXISTS set_exp_ts ON test.test_table;

CREATE TRIGGER set_exp_ts

BEFORE INSERT ON test.test_table

FOR EACH ROW

  EXECUTE PROCEDURE test.test_table_exp_ts();

COMMIT;

select distinct exp_ts from test.test_table;

9999-12-31 23:59:59.000 -0600

@jbradfor​ Thanks for sharing the SQL statements, that helped a lot. I've replicated your steps to create the Trigger and still haven't been able to reproduce the issue in the same build of FME.

Another user on the community noted that they somehow got an invalid date value in their datetime field (exp_ts in your case).

Would you be able to use a WHERE clause within your SQLExecutor to see if this affects whether or not FME can complete the query? Ex: WHERE exp_ts < '2022-07-20';

Here's the community post for reference: https://community.safe.com/s/question/0D54Q000080hb8SSAQ/postgresql-reader-badexpectedaccess