jeacott opened a new issue, #9022: URL: https://github.com/apache/seatunnel/issues/9022
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/seatunnel/issues?q=is%3Aissue+label%3A%22bug%22) and found no similar issues. ### What happened I am migrating partitioned tables from Oracle to Postgres. Oracle supports global unique indexes on partitioned tables where postgres does not. as a result, a partitioned table in oracle with a primary key (id) and a partition key (partkey) works fine, but I need to create a different setup in postgres - primary key (id, partkey). this is fine, I manually create the target sink schema. ``` ... ) PARTITION BY LIST ((partkey)) ; ALTER TABLE mytable ADD PRIMARY KEY (id, partkey); ``` unfortunately when it comes to seatunnel building the sql to do the insert/upsert. it seems to build its on conflict clause based on the source schema? ``` Batch entry 0 INSERT INTO "...) ON CONFLICT ("id") DO UPDATE SET "id"=EXCLUDED."id", "creation_date"=EXCLUDED...." was aborted: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification Call getNextException to see other errors in the batch. ``` it seems that the jdbc/postgres sink is using the schema spec from the source instead of the sink to create this clause? is there a workaround? Thanks. ### SeaTunnel Version 2.3.9 ### SeaTunnel Config ```conf env { parallelism = 8 job.mode = "BATCH" } source { Jdbc { plugin_output = "s1" driver = oracle.jdbc.driver.OracleDriver url = "jdbc:oracle:thin:@//..." user = sch password = xxx use_select_count = true table_list = [ { table_path = "SCH.MYTABLE" } ] partition_column = "CREATION_DATE" properties { database.oracle.jdbc.timezoneAsRegion = "false" } } } transform { TableRename { plugin_input = "s1" plugin_output = "s1_lc" convert_case = "LOWER" prefix = "" suffix = "" } } transform { DynamicCompile { plugin_input = "s1_lc" plugin_output = "s1_transformed" ... code to convert Raw(16)->UUID } } sink { Jdbc { plugin_input = "s1_transformed" driver = org.postgresql.Driver url = "jdbc:postgresql://xxx..." generate_sink_sql = true user = xxx password = xxx database = "mydb" schema = "public" field_ide = LOWERCASE schema_save_mode = ERROR_WHEN_SCHEMA_NOT_EXIST } } ``` ### Running Command ```shell $ docker run --name seatunnel_client --network compose_seatunnel_network -v /`pwd`/templates:/mnt/templates -e ST_DOCKER_MEMBER_LIST=172.16.0.2:5801 --rm apache/seatunnel ./bin/seatunnel.sh -c '/mnt/templates/jdbc_oracle_to_pg.conf' --async ``` ### Error Exception ```log Batch entry 0 INSERT INTO "...) ON CONFLICT ("id") DO UPDATE SET "id"=EXCLUDED."id", "creation_date"=EXCLUDED...." was aborted: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification Call getNextException to see other errors in the batch. end with state FAILED and Exception: java.lang.RuntimeException: java.lang.RuntimeException: table SCH.MYTABLE at org.apache.seatunnel.engine.server.task.flow.SinkFlowLifeCycle.received(SinkFlowLifeCycle.java:302) at org.apache.seatunnel.engine.server.task.flow.SinkFlowLifeCycle.received(SinkFlowLifeCycle.java:70) at org.apache.seatunnel.engine.server.task.SeaTunnelTransformCollector.collect(SeaTunnelTransformCollector.java:39) at org.apache.seatunnel.engine.server.task.SeaTunnelTransformCollector.collect(SeaTunnelTransformCollector.java:27) at org.apache.seatunnel.engine.server.task.group.queue.IntermediateBlockingQueue.handleRecord(IntermediateBlockingQueue.java:75) at org.apache.seatunnel.engine.server.task.group.queue.IntermediateBlockingQueue.collect(IntermediateBlockingQueue.java:50) at org.apache.seatunnel.engine.server.task.flow.IntermediateQueueFlowLifeCycle.collect(IntermediateQueueFlowLifeCycle.java:51) at org.apache.seatunnel.engine.server.task.TransformSeaTunnelTask.collect(TransformSeaTunnelTask.java:72) at org.apache.seatunnel.engine.server.task.SeaTunnelTask.stateProcess(SeaTunnelTask.java:169) at org.apache.seatunnel.engine.server.task.TransformSeaTunnelTask.call(TransformSeaTunnelTask.java:77) at org.apache.seatunnel.engine.server.TaskExecutionService$BlockingWorker.run(TaskExecutionService.java:694) at org.apache.seatunnel.engine.server.TaskExecutionService$NamedTaskWrapper.run(TaskExecutionService.java:1019) at org.apache.seatunnel.api.tracing.MDCRunnable.run(MDCRunnable.java:43) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Caused by: java.lang.RuntimeException: table SCH.MYTABLE sink throw error at org.apache.seatunnel.api.sink.multitablesink.MultiTableSinkWriter.subSinkErrorCheck(MultiTableSinkWriter.java:140) at org.apache.seatunnel.api.sink.multitablesink.MultiTableSinkWriter.write(MultiTableSinkWriter.java:184) at org.apache.seatunnel.api.sink.multitablesink.MultiTableSinkWriter.write(MultiTableSinkWriter.java:47) at org.apache.seatunnel.engine.server.task.flow.SinkFlowLifeCycle.received(SinkFlowLifeCycle.java:268) ... 17 more Caused by: org.apache.seatunnel.connectors.seatunnel.jdbc.exception.JdbcConnectorException: ErrorCode:[COMMON-08], ErrorDescription:[Sql oper to JDBC failed. at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.writeRecord(JdbcOutputFormat.java:109) at org.apache.seatunnel.connectors.seatunnel.jdbc.sink.JdbcSinkWriter.write(JdbcSinkWriter.java:134) at org.apache.seatunnel.connectors.seatunnel.jdbc.sink.JdbcSinkWriter.write(JdbcSinkWriter.java:43) at org.apache.seatunnel.api.sink.multitablesink.MultiTableWriterRunnable.run(MultiTableWriterRunnable.java:67) ... 6 more Caused by: org.apache.seatunnel.connectors.seatunnel.jdbc.exception.JdbcConnectorException: ErrorCode:[COMMON-10], ErrorDescription:[Flush da at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.flush(JdbcOutputFormat.java:142) at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.writeRecord(JdbcOutputFormat.java:106) ... 9 more Caused by: org.postgresql.util.PSQLException: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2099) at org.postgresql.core.v3.QueryExecutorImpl.flushIfDeadlockRisk(QueryExecutorImpl.java:1456) at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1481) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:546) ... 20 more ``` ### Zeta or Flink or Spark Version _No response_ ### Java or Scala Version _No response_ ### Screenshots _No response_ ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@seatunnel.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
