[destination-postgres] airbyte_safe_cast function already exists

[lluiserena]

Connector Name

destination-postgres

Connector Version

2.0.10

What step the error happened?

During the sync

Relevant information

We believe we found a bug with PostgreSQL as a destination. We can consistently reproduce and think we have a fix.
https://github.com/airbytehq/airbyte/blob/3b3392317f166fecb8723531054c0c745865142b[…]byte/integrations/destination/postgres/PostgresDestination.java
Is not working as the comment states, for some reason, the connection is now kept consistent, and since that code runs multiple times in the same session, it always tries to create the existing function and fails

The could should check if the function exists before trying to create it

Relevant log output

`Stack Trace: org.postgresql.util.PSQLException: ERROR: function "airbyte_safe_cast" already exists with same argument types
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:292)
        at com.zaxxer.hikari.pool.PoolBase.executeSql(PoolBase.java:579)
        at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:426)
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:365)
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:202)
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:461)
        at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:724)
        at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:703)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
        at java.base/java.lang.Thread.run(Thread.java:1583)`

Contribute

• Yes, I want to contribute

[marcosmarxm]

@lluiserena you're reporting a problem for source but referencing Destination Postgres, please update your issue to inform correclty which connector this is affecting.

[d-shames]

fwiw my team is experiencing the same issue

[marcosmarxm]

Solved by #39372