Question

PostGIS > SQL* - Issue with timestamptz

J
Badge +1

FME Desktop v2021.1.1.0

postgresql v11.13

 

We have an expiration date col that we default to a hard coded high value on INSERT using a trigger and function. The value of that timestamptz col is:

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

 

SQLCreator or SQLExecuter will not allow the selection or reference of that column. However, I can select or reference the col in DBeaver without issues.

 

This statement produces this error in FME:

select exp_ts from test_table;

POSTGIS reader: An error occurred. FME will attempt to obtain more information on the error, but this may cause the translation to be terminated

POSTGIS reader: An error occurred. Here is the information that FME was able to find on the error

POSTGIS reader: stk::ex::bad_expected_access

 

Workaround was to change the col to timestamp.

Any ideas?

 

 

 

 

 

 

 

5 replies

danminneyatsaf
Rank
Userlevel 2
Badge +10

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

J
Badge +1

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?

danminneyatsaf
Rank
Userlevel 2
Badge +10

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!

J
Badge +1

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

 

danminneyatsaf
Rank
Userlevel 2
Badge +10

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

Reply


Community Stats

28,741
Posts
109,578
Replies
36,583
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.