Source Postgres: Debezium Upgrade introduces default values errors #13967

adam-bloom · 2022-06-21T13:51:23Z

Environment

Airbyte version: 0.39.13-alpha
OS Version / Instance: AWS Linux
Deployment: K8s
Source Connector and version: Postgres 0.4.26
Destination Connector and version: Redshift 0.3.40
Step where error happened: Sync Job

Current Behavior

Debezium fails with the following exception:

Caused by: java.lang.NoSuchMethodError: 'java.time.OffsetDateTime org.postgresql.jdbc.TimestampUtils.toOffsetDateTime(java.lang.String)'
	at io.debezium.connector.postgresql.connection.PostgresDefaultValueConverter.lambda$createDefaultValueMappers$22(PostgresDefaultValueConverter.java:168)
	at io.debezium.connector.postgresql.connection.PostgresDefaultValueConverter.parseDefaultValue(PostgresDefaultValueConverter.java:77)
	at io.debezium.relational.TableSchemaBuilder.lambda$addField$9(TableSchemaBuilder.java:391)
	at java.base/java.util.Optional.flatMap(Optional.java:289)
	at io.debezium.relational.TableSchemaBuilder.addField(TableSchemaBuilder.java:391)
	at io.debezium.relational.TableSchemaBuilder.lambda$create$2(TableSchemaBuilder.java:147)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183)
	at java.base/java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:179)
	at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1625)
	at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
	at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
	at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150)
	at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173)

Expected Behavior

No exception was encountered on 0.4.24.

This is resolved in newer versions of pgjdbc (pgjdbc/pgjdbc#2497). I believe upgrading will resolve this, but am not sure where that dependency is declared.

Logs

Steps to Reproduce

Are you willing to submit a PR?

If I found where the pgjdbc dependency was... ;)

The text was updated successfully, but these errors were encountered:

tuliren · 2022-06-21T20:59:15Z

@subodh1810, FYI.

subodh1810 · 2022-06-22T13:18:44Z

@adam-bloom can you share the schema of the table that you are trying to sync which is causing this error. I want to reproduce this locally

adam-bloom · 2022-06-22T14:19:51Z

@subodh1810 not sure how to identify exactly which table from that log - there are quite a few for this connection. However, only 5 of them are configured for incremental sync, so I assume it'd be one of those five that debezium is attempting to setup. Here are two schemas for tables in that 5 that have timestamp columns with default values, so my guess is that it's one of these:

                                      Table "public.auth_keys"
     Column     |            Type             | Collation | Nullable |           Default
----------------+-----------------------------+-----------+----------+------------------------------
 id             | character varying(24)       |           | not null | generate_id('kx'::bpchar)
 is_revoked     | boolean                     |           | not null | false
 token          | character varying(128)      |           | not null |
 principal_type | auth_resource_types         |           | not null |
 principal_id   | character varying(64)       |           | not null |
 time_created   | timestamp without time zone |           |          | timezone('utc'::text, now())
 time_expires   | timestamp without time zone |           |          |
 time_last_used | timestamp without time zone |           |          |
 version        | character varying(8)        |           | not null | '1.0'::character varying
 manager_id     | character varying(64)       |           |          | NULL::character varying
 manager_type   | enum_auth_keys_manager_type |           |          |
 type           | character varying(64)       |           | not null | 'default'::character varying
 ip_whitelist   | jsonb                       |           | not null | '[]'::jsonb

                                     Table "public.account_emails"
    Column     |            Type             | Collation | Nullable |              Default
---------------+-----------------------------+-----------+----------+-----------------------------------
 email         | character varying(256)      |           | not null |
 account_id    | character varying(24)       |           | not null |
 is_anonymous  | boolean                     |           | not null |
 is_confirmed  | boolean                     |           | not null | false
 is_primary    | boolean                     |           | not null | false
 is_archived   | boolean                     |           | not null | false
 time_archived | timestamp without time zone |           | not null | to_timestamp(0::double precision)

subodh1810 · 2022-06-22T19:49:45Z

@adam-bloom thanks for sharing, I will try to reproduce and update the issue with my findings

lucienfregosibodyguard · 2022-06-24T11:50:22Z

Same issue here

jrmbrgs · 2022-06-27T17:24:23Z

Hi,

Got the same issue on setting up CDC ingestion w/ postgres

Airbyte version: 0.39.26-alpha
OS Version / Instance: GCP
Deployment: Docker
Source Connector and version: Postgres 0.4.28
Destination Connector and version: BigQuery 1.1.11
Step where error happened: Sync Job

On a simple test table:

CREATE DATABASE foo;
CREATE TABLE bar (
  id INTEGER PRIMARY KEY,
  label TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() 
);

2022-06-27 17:22:29 source > 2022-06-27 17:22:29 INFO i.d.c.p.s.InitialSnapshotter(shouldSnapshot):34 - Taking initial snapshot for new datasource
2022-06-27 17:22:29 source > 2022-06-27 17:22:29 INFO i.d.c.p.PostgresSnapshotChangeEventSource(getSnapshottingTask):64 - According to the connector configuration data will be snapshotted
2022-06-27 17:22:29 source > 2022-06-27 17:22:29 INFO i.d.r.RelationalSnapshotChangeEventSource(doExecute):87 - Snapshot step 1 - Preparing
2022-06-27 17:22:29 source > 2022-06-27 17:22:29 INFO i.d.p.s.AbstractSnapshotChangeEventSource(execute):88 - Snapshot - Final stage
2022-06-27 17:22:29 source > 2022-06-27 17:22:29 ERROR i.d.p.ErrorHandler(setProducerThrowable):35 - Producer failure
2022-06-27 17:22:29 source > java.lang.NoSuchMethodError: 'java.time.OffsetDateTime org.postgresql.jdbc.TimestampUtils.toOffsetDateTime(java.lang.String)'
2022-06-27 17:22:29 source > 	at io.debezium.connector.postgresql.connection.PostgresDefaultValueConverter.lambda$createDefaultValueMappers$23(PostgresDefaultValueConverter.java:169) ~[debezium-connector-postgres-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at io.debezium.connector.postgresql.connection.PostgresDefaultValueConverter.parseDefaultValue(PostgresDefaultValueConverter.java:77) ~[debezium-connector-postgres-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at io.debezium.relational.TableSchemaBuilder.lambda$addField$9(TableSchemaBuilder.java:391) ~[debezium-core-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at java.util.Optional.flatMap(Optional.java:289) ~[?:?]
2022-06-27 17:22:29 source > 	at io.debezium.relational.TableSchemaBuilder.addField(TableSchemaBuilder.java:391) ~[debezium-core-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at io.debezium.relational.TableSchemaBuilder.lambda$create$2(TableSchemaBuilder.java:147) ~[debezium-core-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183) ~[?:?]
2022-06-27 17:22:29 source > 	at java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:179) ~[?:?]
2022-06-27 17:22:29 source > 	at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1625) ~[?:?]
2022-06-27 17:22:29 source > 	at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509) ~[?:?]
2022-06-27 17:22:29 source > 	at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499) ~[?:?]
2022-06-27 17:22:29 source > 	at java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150) ~[?:?]
2022-06-27 17:22:29 source > 	at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173) ~[?:?]
2022-06-27 17:22:29 source > 	at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[?:?]
2022-06-27 17:22:29 source > 	at java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596) ~[?:?]
2022-06-27 17:22:29 source > 	at io.debezium.relational.TableSchemaBuilder.create(TableSchemaBuilder.java:145) ~[debezium-core-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at io.debezium.relational.RelationalDatabaseSchema.buildAndRegisterSchema(RelationalDatabaseSchema.java:135) ~[debezium-core-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at io.debezium.relational.RelationalDatabaseSchema.refreshSchema(RelationalDatabaseSchema.java:209) ~[debezium-core-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at io.debezium.connector.postgresql.PostgresSchema.lambda$refreshSchemas$2(PostgresSchema.java:149) ~[debezium-connector-postgres-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at java.util.concurrent.ConcurrentHashMap$KeySetView.forEach(ConcurrentHashMap.java:4706) ~[?:?]
2022-06-27 17:22:29 source > 	at java.util.Collections$UnmodifiableCollection.forEach(Collections.java:1092) ~[?:?]
2022-06-27 17:22:29 source > 	at io.debezium.connector.postgresql.PostgresSchema.refreshSchemas(PostgresSchema.java:149) ~[debezium-connector-postgres-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at io.debezium.connector.postgresql.PostgresSchema.refresh(PostgresSchema.java:93) ~[debezium-connector-postgres-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at io.debezium.connector.postgresql.PostgresSnapshotChangeEventSource.connectionCreated(PostgresSnapshotChangeEventSource.java:92) ~[debezium-connector-postgres-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at io.debezium.relational.RelationalSnapshotChangeEventSource.doExecute(RelationalSnapshotChangeEventSource.java:94) ~[debezium-core-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:76) ~[debezium-core-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:155) ~[debezium-core-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at io.debezium.pipeline.ChangeEventSourceCoordinator.executeChangeEventSources(ChangeEventSourceCoordinator.java:137) ~[debezium-core-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:109) ~[debezium-core-1.9.2.Final.jar:1.9.2.Final]
2022-06-27 17:22:29 source > 	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) [?:?]
2022-06-27 17:22:29 source > 	at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
2022-06-27 17:22:29 source > 	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
2022-06-27 17:22:29 source > 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
2022-06-27 17:22:29 source > 	at java.lang.Thread.run(Thread.java:833) [?:?]

subodh1810 · 2022-06-28T07:41:26Z

Hey guys thanks for reporting the issue and helping me reproduce it. I have raised the PR to fix it. Please track the PR and look for a new release for postgres source connector after its merged. I will update the issue as well once I release.

felix-d · 2022-06-28T14:27:14Z

+1, we're also having the same issue and it breaks our sync.

marcosmarxm · 2022-06-28T20:05:20Z

Zendesk ticket #1362 has been linked to this issue.

marcosmarxm · 2022-06-28T20:05:43Z

Comment made from Zendesk by Marcos Marx on 2022-06-28 at 20:05:

Team is working in the solution for this issue Felix. Any updates I'll return to you.

adam-bloom · 2022-06-29T15:46:26Z

@marcosmarxm @subodh1810 I saw the PR was merged, but a new version of the postgres connector wasn't published as a part of that. Are you planning to release a new version with that fix?

subodh1810 · 2022-06-29T16:23:44Z

@adam-bloom yes am going to release a new version of the postgres connector soon

subodh1810 · 2022-06-29T19:48:01Z

Hey guys a new postgres source conenctor 0.4.29 should be available now with the fix. Am going to close the ticket now but feel free to open a new ticket if you come across any issues. Thanks again

marcosmarxm · 2022-06-30T17:45:32Z

Comment made from Zendesk by Marcos Marx on 2022-06-30 at 17:45:

I fix was released, please upgrade connector Postgres to version 0.4.29

adam-bloom added needs-triage type/bug Something isn't working labels Jun 21, 2022

octavia-squidington-iii added team/triage autoteam community team/tse Technical Support Engineers and removed team/triage labels Jun 21, 2022

tuliren added the area/databases label Jun 21, 2022

marcosmarxm removed needs-triage team/tse Technical Support Engineers autoteam labels Jun 22, 2022

octavia-squidington-iii added autoteam team/tse Technical Support Engineers labels Jun 22, 2022

marcosmarxm added cdc connectors/source/postgres labels Jun 22, 2022

subodh1810 self-assigned this Jun 22, 2022

marcosmarxm removed team/tse Technical Support Engineers autoteam labels Jun 23, 2022

octavia-squidington-iii added autoteam team/tse Technical Support Engineers labels Jun 23, 2022

marcosmarxm added the priority/high High priority label Jun 27, 2022

subodh1810 mentioned this issue Jun 28, 2022

upgrade potgresql version to fix default timestamp handling #14211

Merged

marcosmarxm added the zendesk label Jun 28, 2022

grishick added team/databases and removed team/tse Technical Support Engineers autoteam labels Jun 29, 2022

subodh1810 mentioned this issue Jun 29, 2022

release new postgres source connector version 0.4.29 #14265

Merged

subodh1810 closed this as completed Jun 29, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Source Postgres: Debezium Upgrade introduces default values errors #13967

Source Postgres: Debezium Upgrade introduces default values errors #13967

adam-bloom commented Jun 21, 2022

tuliren commented Jun 21, 2022

subodh1810 commented Jun 22, 2022

adam-bloom commented Jun 22, 2022

subodh1810 commented Jun 22, 2022

lucienfregosibodyguard commented Jun 24, 2022

jrmbrgs commented Jun 27, 2022

subodh1810 commented Jun 28, 2022

felix-d commented Jun 28, 2022

marcosmarxm commented Jun 28, 2022

marcosmarxm commented Jun 28, 2022

adam-bloom commented Jun 29, 2022

subodh1810 commented Jun 29, 2022

subodh1810 commented Jun 29, 2022

marcosmarxm commented Jun 30, 2022

Source Postgres: Debezium Upgrade introduces default values errors #13967

Source Postgres: Debezium Upgrade introduces default values errors #13967

Comments

adam-bloom commented Jun 21, 2022

Environment

Current Behavior

Expected Behavior

Logs

Steps to Reproduce

Are you willing to submit a PR?

tuliren commented Jun 21, 2022

subodh1810 commented Jun 22, 2022

adam-bloom commented Jun 22, 2022

subodh1810 commented Jun 22, 2022

lucienfregosibodyguard commented Jun 24, 2022

jrmbrgs commented Jun 27, 2022

subodh1810 commented Jun 28, 2022

felix-d commented Jun 28, 2022

marcosmarxm commented Jun 28, 2022

marcosmarxm commented Jun 28, 2022

adam-bloom commented Jun 29, 2022

subodh1810 commented Jun 29, 2022

subodh1810 commented Jun 29, 2022

marcosmarxm commented Jun 30, 2022