executeUpdate cannot update the value of a postgres sequence

[naka-sho]

https://github.com/embulk/embulk-output-jdbc/blob/a11e87c5efc39bc913aace293e4428a021df45ae/embulk-output-jdbc/src/main/java/org/embulk/output/jdbc/JdbcOutputConnection.java#L623

executeUpdate cannot update the value of a postgres sequence.

Change it to execute method.

For example

SELECT setval('table_id_seq', (SELECT MAX(id) FROM table));

https://docs.oracle.com/javase/jp/6/api/java/sql/Statement.html#executeUpdate(java.lang.String) https://docs.oracle.com/javase/jp/6/api/java/sql/Statement.html#execute(java.lang.String)

[hiroyuki-sato]

Hello, @naka-sho

Cloud you tell us your problem and evnironment?

[naka-sho]

@hiroyuki-sato

excuse me. I ran the yaml below.

in:
  xxx:
out:
  type: postgresql
  host: localhost
  database: test
  user: test_user
  password: test
  table: test
  mode: insert
  after_load: SELECT setval('table_id_seq', (SELECT MAX(id) FROM test));
  default_timezone: 'Asia/Tokyo'

[hiroyuki-sato]

Hello, @naka-sho

Creating a procedure is one of the workarounds.

• Embulk 0.9.24
• embulk-output-postgresql: 0.9.0
• macOS: 13.2.1
• input data: embulk example sample (Remove decoder for changing id field.)

CREATE PROCEDURE update_sequence()
LANGUAGE SQL
AS $$
SELECT setval('sequence_test_id_seq', (SELECT MAX(id) FROM sequence_test));
$$;

in:
  type: file
  path_prefix: sample/csv/sample_
  parser:
    charset: UTF-8
    newline: LF
    type: csv
    delimiter: ','
    quote: '"'
    escape: '"'
    null_string: 'NULL'
    trim_if_not_quoted: false
    skip_header_lines: 1
    allow_extra_columns: false
    allow_optional_columns: false
    columns:
    - {name: id, type: long}
    - {name: account, type: long}
    - {name: time, type: timestamp, format: '%Y-%m-%d %H:%M:%S'}
    - {name: purchase, type: timestamp, format: '%Y%m%d'}
    - {name: comment, type: string}
#out: {type: stdout}
out:
  type: postgresql
  mode: insert
  table: sequence_test
  user: user
  host: host
  password: pass
  database: embulk_test
  after_load: "call update_sequence()"

It outputs the following error if the configuration in the after_load part use the select statement.

  after_load: "SELECT setval('sequence_test_id_seq', (SELECT MAX(id) FROM sequence_test))"

org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: org.postgresql.util.PSQLException: A result was returned when none was expected.
    at org.embulk.exec.BulkLoader$LoaderState.buildPartialExecuteException(BulkLoader.java:340)
    at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:566)
    at org.embulk.exec.BulkLoader.access$000(BulkLoader.java:35)
    at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:353)
    at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:350)
    at org.embulk.spi.Exec.doWith(Exec.java:23)
    at org.embulk.exec.BulkLoader.run(BulkLoader.java:350)
    at org.embulk.EmbulkEmbed.run(EmbulkEmbed.java:242)
    at org.embulk.EmbulkRunner.runInternal(EmbulkRunner.java:291)
    at org.embulk.EmbulkRunner.run(EmbulkRunner.java:155)
    at org.embulk.cli.EmbulkRun.runSubcommand(EmbulkRun.java:431)
    at org.embulk.cli.EmbulkRun.run(EmbulkRun.java:90)
    at org.embulk.cli.Main.main(Main.java:64)
Caused by: java.lang.RuntimeException: org.postgresql.util.PSQLException: A result was returned when none was expected.

[naka-sho]

@hiroyuki-sato

thank you.

It is also possible to circumvent the procedure.

Do you recommend using procedure for this tool?

The way I worked around it was to manually run the sequence update after running the embulk.

I thought it would be possible to execute select in after_load, so I raised an issue.

It may be good to add another option instead of after_load.

[hiroyuki-sato]

I'll discuss your request with team members. But It will take a little time. Please use a Stored Procedure or a manual update way until a new feature is added.

[naka-sho]

@hiroyuki-sato thank you. hope it will be introduced

[hiroyuki-sato]

@naka-sho this issue fixed in #313. And, v0.10.3 has been released. Please try it.