Postgres to BigQuery logical replication errors out with invalid timestamp

Connector Questions & Issues
1
  • Is this your first time deploying Airbyte?: No
  • OS Version / Instance: GCP
  • Memory / Disk: 16Gb
  • Deployment: Docker
  • Airbyte Version: 0.39.19-alpha
  • Source name/version: Postgres 0.4.24
  • Destination name/version: Bigquery 1.1.9
  • Step: Sync job (normalization)
  • Description:
    Logical replication fails, with error message such as -
2022-06-20 16:24:17 normalization > 16:24:12.538432 [info ] [MainThread]: Completed with 1 error and 0 warnings:
2022-06-20 16:24:17 normalization > 16:24:12.539085 [info ] [MainThread]: 
2022-06-20 16:24:17 normalization > 16:24:12.539571 [error] [MainThread]: Database Error in model deposit_scd (models/generated/airbyte_incremental/scd/raw_checkmate/deposit_scd.sql)
2022-06-20 16:24:17 normalization > 16:24:12.540151 [error] [MainThread]:   Invalid timestamp string "0000-12-30T00:00:00Z"
2022-06-20 16:24:17 normalization > 16:24:12.540678 [error] [MainThread]:   compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_incremental/scd/raw_<db>/<table>_scd.sql

Checking the source table, there is no row with the timestamp 0000-12-30T00:00:00Z. Although there are a couple of entries with the timestamp 0001-01-01 00:00:00+00.

Probably some parsing that turns 0001-01-01 00:00:00+00 into 0000-12-30T00:00:00Z, as the errored out timestamp is exactly 2 days prior to the original timestamp.

Logs -
logs-81417.txt (998.6 KB)

source_catalog.json -
source_catalog.json.txt (4.1 KB)

The Standard replication works as expected for these dbs.

Related issue - Postgres to BigQuery logical replication errors out with invalid timestamp · Issue #13946 · airbytehq/airbyte · GitHub

2

@alafanechere Please lemme know if any other info is needed :slight_smile:

3

Hey @alafanechere! Did you get a chance to look at the catalog?

4

Observing the same issue with upgraded versions-

  • Airbyte Version: 0.39.32-alpha
  • Source name/version: Postgres 0.4.31

Looking for suggestions to resolve it.

5

I reopened the issue now that we have sufficient details. I just asked an addtional question on the issue. Thank you @ameyabhope!

6

Thanks for your response @alafanechere
We made some changes to source schema since. Here are all the updated docs -

Logs -
logs-89696.txt (1.6 MB)

source_catalog.json
source_catalog.json.txt (7.4 KB)

The error we’re getting causing replication to fail -

2022-07-11 15:48:08 normalization > 15:48:03.291819 [info ] [MainThread]: Completed with 1 error and 0 warnings:
2022-07-11 15:48:08 normalization > 15:48:03.292511 [info ] [MainThread]: 
2022-07-11 15:48:08 normalization > 15:48:03.293065 [error] [MainThread]: Database Error in model transactions_in_scd (models/generated/airbyte_incremental/scd/raw_achilles/transactions_in_scd.sql)
2022-07-11 15:48:08 normalization > 15:48:03.293622 [error] [MainThread]:   Invalid timestamp string "0000-12-30T00:00:00Z"
2022-07-11 15:48:08 normalization > 15:48:03.294238 [error] [MainThread]:   compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_incremental/scd/raw_achilles/transactions_in_scd.sql

The issue being with transactions_in.settlement_date

Postgres type of the column - timestamp with time zone

Like the OP, the timestamp in the data is actually 0001-01-01T00:00:00Z, which perhaps gets converted to 0000-12-30T00:00:00Z causing it to error out (as per error logs)

7

Hi there from the Community Assistance team.
We’re letting you know about an issue we discovered with the back-end process we use to handle topics and responses on the forum. If you experienced a situation where you posted the last message in a topic that did not receive any further replies, please open a new topic to continue the discussion. In addition, if you’re having a problem and find a closed topic on the subject, go ahead and open a new topic on it and we’ll follow up with you. We apologize for the inconvenience, and appreciate your willingness to work with us to provide a supportive community.