ERROR: duplicate key violates unique constraint "pg_class_relname_nsp_index"

[idoelad]

When running Embulk on multiple machines that writes to a single RedShift cluster, I often get the following error (usually only on the first few runs):

Error: java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: duplicate key violates unique constraint "pg_class_relname_nsp_index" (possibly caused by concurrent transaction conflict)

I found these two issues that seems related but both seems to be fixed in versions older than the one I'm using:
#205 #116

Embulk and plugin versions:

Embulk v0.9.11
embulk-input-mysql (0.9.3)
embulk-output-redshift (0.8.1)

[hiroyuki-sato]

Hello, @idoelad

Could you paste the whole stack trace log?. It may help for debugging the error.

[idoelad]

Hi @hiroyuki-sato, sorry for the delayed response.
Here is the whole stack trace log:

org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: duplicate key violates unique constraint "pg_class_relname_nsp_index" (possibly caused by concurrent transaction conflict)
     at org.embulk.exec.BulkLoader$LoaderState.buildPartialExecuteException(BulkLoader.java:339)
     at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:565)
     at org.embulk.exec.BulkLoader.access$000(BulkLoader.java:34)
     at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:352)
     at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:349)
     at org.embulk.spi.Exec.doWith(Exec.java:22)
     at org.embulk.exec.BulkLoader.run(BulkLoader.java:349)
     at org.embulk.EmbulkEmbed.run(EmbulkEmbed.java:161)
     at org.embulk.EmbulkRunner.runInternal(EmbulkRunner.java:292)
     at org.embulk.EmbulkRunner.run(EmbulkRunner.java:156)
     at org.embulk.cli.EmbulkRun.runSubcommand(EmbulkRun.java:436)
     at org.embulk.cli.EmbulkRun.run(EmbulkRun.java:91)
     at org.embulk.cli.Main.main(Main.java:26)
Caused by: java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: duplicate key violates unique constraint "pg_class_relname_nsp_index" (possibly caused by concurrent transaction conflict)
     at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.commit(AbstractJdbcOutputPlugin.java:474)
     at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.transaction(AbstractJdbcOutputPlugin.java:417)
     at org.embulk.exec.BulkLoader$4$1$1.transaction(BulkLoader.java:520)
     at org.embulk.exec.LocalExecutorPlugin.transaction(LocalExecutorPlugin.java:49)
     at org.embulk.exec.BulkLoader$4$1.run(BulkLoader.java:515)
     at org.embulk.spi.util.Filters$RecursiveControl.transaction(Filters.java:84)
     at org.embulk.spi.util.Filters.transaction(Filters.java:42)
     at org.embulk.exec.BulkLoader$4.run(BulkLoader.java:510)
     at org.embulk.input.jdbc.AbstractJdbcInputPlugin.transaction(AbstractJdbcInputPlugin.java:206)
     at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:506)
     ... 11 more
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key violates unique constraint "pg_class_relname_nsp_index" (possibly caused by concurrent transaction conflict)
     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
     at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:645)
     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:481)
     at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:409)
     at org.embulk.output.jdbc.JdbcOutputConnection.executeUpdate(JdbcOutputConnection.java:598)
     at org.embulk.output.jdbc.JdbcOutputConnection.createTableIfNotExists(JdbcOutputConnection.java:144)
     at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.doCommit(AbstractJdbcOutputPlugin.java:822)
     at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$3.run(AbstractJdbcOutputPlugin.java:467)
     at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$RetryableSQLExecution.call(AbstractJdbcOutputPlugin.java:1239)
     at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$RetryableSQLExecution.call(AbstractJdbcOutputPlugin.java:1227)
     at org.embulk.spi.util.RetryExecutor.run(RetryExecutor.java:81)
     at org.embulk.spi.util.RetryExecutor.runInterruptible(RetryExecutor.java:62)
     at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.withRetry(AbstractJdbcOutputPlugin.java:1212)
     at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.withRetry(AbstractJdbcOutputPlugin.java:1204)
     at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.commit(AbstractJdbcOutputPlugin.java:462)
     ... 20 more

[hito4t]

@idoelad
Thank you for the information!
@hiroyuki-sato
Thank you for the support!

It seems that multiple embulk processes tried to create the same table.
Did you try to load into the same table that had not exist from multiple machines?

[idoelad]

Hi @hito4t ,
Thank you for the response.

Yes, I have a few machines that writes to the same table that doesn't exists before the first Embulk run (I'm counting on Embulk to create the table, since the MySQL table structure may change).
I assumed that the first machine will create the table and the rest of the machines will know that the table already exists.

[hito4t]

@idoelad
I've created the PR #244 to fix this problem.

[idoelad]

Thank you so much @hito4t !