search

embulk / embulk-output-jdbc

MySQL, PostgreSQL, Redshift and generic JDBC output plugins for Embulk
Other
88 stars 87 forks source link

PG raise error 'ERROR: NUMERIC precision 0 must be between 1 and 1000' #350

Open shin1103 opened 6 days ago

shin1103 commented 6 days ago

Starting from PostgreSQL 14, the default password encryption has changed from md5 to scram-sha-256. The default JDBC driver in embulk-output-jdbc does not support scram-sha-256 (see section 19.3.2 Authentication). https://www.postgresql.org/docs/13/runtime-config-connection.html
https://www.postgresql.org/docs/14/runtime-config-connection.html 

Error: java.lang.RuntimeException: org.postgresql.util.PSQLException: The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver.

Therefore, if you cannot change the user encryption method in PostgreSQL, you need to use another JDBC driver that supports scram-sha-256 encryption using the embulk driver_path option. However, using a supported JDBC driver might result in other errors.

Error: java.lang.RuntimeException: org.embulk.util.retryhelper.RetryGiveupException: org.postgresql.util.PSQLException: ERROR: NUMERIC precision 0 must be between 1 and 1000
  Position: 79

This error is similar to this issue. The difference in the error is likely due to the JDBC driver version.

To Reproduce

Postgresql

First, Prepare database.

services:
  db:
    image: postgres:14
    shm_size: 128mb
    environment:
      POSTGRES_DB: postgres
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
    ports:
      - 5432:5432
    volumes:
      - db-data:/var/lib/postgresql/data
  service_pgadmin:
    image: dpage/pgadmin4
    container_name: container_pgadmin
    ports:
      - 8080:80
    environment:
      PGADMIN_DEFAULT_EMAIL: example@example.com
      PGADMIN_DEFAULT_PASSWORD: password
    depends_on:
      - db
    volumes:
      - pgadmin-data:/var/lib/pgadmin
      - ./config/servers.json:/pgadmin4/servers.json
volumes:
    db-data:
    pgadmin-data:

Create tables.

CREATE TABLE dest.dest_tbl
(
    id integer,
    price numeric,
    PRIMARY KEY (id)
);

CREATE TABLE src.src_tbl
(
    id integer,
    price numeric,
    PRIMARY KEY (id)
);

Authentication Error

Use default JDBC driver.

in:
  type: postgresql
  host: localhost
  user: postgres
  password: password
  database: postgres
  schema: src
  table: src_tbl
out:
  type: postgresql
  host: localhost
  user: postgres 
  password: password
  database: postgres
  schema: dest
  table: dest_tbl
  mode: merge

Numeric Error

Use latest JDBC driver.

in:
  type: postgresql
  driver_path: ./postgresql-42.7.4.jar
  host: localhost
  user: postgres
  password: password
  database: postgres
  schema: src
  table: src_tbl
out:
  type: postgresql
  driver_path: ./postgresql-42.7.4.jar
  host: localhost
  user: postgres 
  password: password
  database: postgres
  schema: dest
  table: dest_tbl
  mode: merge

full logs

default driver

shin1103@DESKTOP-LBHCM86:~/work/embulk$ java -jar ~/.embulk/bin/embulk-0.11.5.jar run config.yml
2024-10-15 22:02:14.532 +0900 [INFO] (main): embulk_home is set by the location of embulk.properties found in: /home/shin1103/.embulk
2024-10-15 22:02:14.535 +0900 [INFO] (main): m2_repo is set as a sub directory of embulk_home: /home/shin1103/.embulk/lib/m2/repository
2024-10-15 22:02:14.536 +0900 [INFO] (main): gem_home is set as a sub directory of embulk_home: /home/shin1103/.embulk/lib/gems
2024-10-15 22:02:14.536 +0900 [INFO] (main): gem_path is set empty.
2024-10-15 22:02:14.536 +0900 [DEBUG] (main): Embulk system property "default_guess_plugin" is set to: "gzip,bzip2,json,csv"
2024-10-15 22:02:14.793 +0900 [INFO] (main): Started Embulk v0.11.5
2024-10-15 22:02:14.942 +0900 [INFO] (0001:transaction): Embulk system property "plugins.input.postgresql" is not set.
2024-10-15 22:02:14.942 +0900 [INFO] (0001:transaction): Embulk system property "plugins.default.input.postgresql" is not set.
2024-10-15 22:02:17.562 +0900 [INFO] (0001:transaction): Gem's home and path are set by system configs "gem_home": "/home/shin1103/.embulk/lib/gems", "gem_path": ""
2024-10-15 22:02:18.500 +0900 [INFO] (0001:transaction): Loaded JRuby runtime 9.4.8.0
2024-10-15 22:02:18.604 +0900 [INFO] (0001:transaction): Loaded plugin embulk-input-postgresql (0.13.2)
2024-10-15 22:02:18.763 +0900 [INFO] (0001:transaction): Embulk system property "plugins.output.postgresql" is not set.
2024-10-15 22:02:18.763 +0900 [INFO] (0001:transaction): Embulk system property "plugins.default.output.postgresql" is not set.
2024-10-15 22:02:18.803 +0900 [INFO] (0001:transaction): Loaded plugin embulk-output-postgresql (0.10.6)
/home/shin1103/.embulk/lib/gems/gems/embulk-0.11.5-java/lib/embulk/java_plugin.rb:92: warning: already initialized constant org.embulk.util.config::Task
2024-10-15 22:02:18.995 +0900 [WARN] (0001:transaction): "UTC" is recognized as "Z" to be compatible with the legacy style.
2024-10-15 22:02:19.026 +0900 [INFO] (0001:transaction): The Pg JDBC driver for the class "org.postgresql.Driver" is expected to be found in "default_jdbc_driver" at /home/shin1103/.embulk/lib/gems/gems/embulk-input-postgresql-0.13.2-java/default_jdbc_driver/postgresql-9.4-1205-jdbc41.jar.
2024-10-15 22:02:19.036 +0900 [INFO] (0001:transaction): Connecting to jdbc:postgresql://localhost:5432/postgres options {password=***, ApplicationName=embulk-input-postgresql, socketTimeout=1800, tcpKeepAlive=true, loginTimeout=300, user=postgres}
2024-10-15 22:02:19.110 +0900 [INFO] (0001:cleanup): Embulk system property "plugins.input.postgresql" is not set.
2024-10-15 22:02:19.110 +0900 [INFO] (0001:cleanup): Embulk system property "plugins.default.input.postgresql" is not set.
2024-10-15 22:02:21.043 +0900 [INFO] (0001:cleanup): Gem's home and path are set by system configs "gem_home": "/home/shin1103/.embulk/lib/gems", "gem_path": ""
2024-10-15 22:02:21.900 +0900 [INFO] (0001:cleanup): Loaded JRuby runtime 9.4.8.0
2024-10-15 22:02:21.977 +0900 [INFO] (0001:cleanup): Loaded plugin embulk-input-postgresql (0.13.2)
2024-10-15 22:02:22.144 +0900 [INFO] (0001:cleanup): Embulk system property "plugins.output.postgresql" is not set.
2024-10-15 22:02:22.144 +0900 [INFO] (0001:cleanup): Embulk system property "plugins.default.output.postgresql" is not set.
2024-10-15 22:02:22.167 +0900 [INFO] (0001:cleanup): Loaded plugin embulk-output-postgresql (0.10.6)
/home/shin1103/.embulk/lib/gems/gems/embulk-0.11.5-java/lib/embulk/java_plugin.rb:92: warning: already initialized constant org.embulk.util.config::Task
org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: org.postgresql.util.PSQLException: The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver.
        at org.embulk.exec.BulkLoader$LoaderState.buildPartialExecuteException(BulkLoader.java:340)
        at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:580)
        at org.embulk.exec.BulkLoader.access$000(BulkLoader.java:36)
        at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:353)
        at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:350)
        at org.embulk.spi.ExecInternal.doWith(ExecInternal.java:26)
        at org.embulk.exec.BulkLoader.run(BulkLoader.java:350)
        at org.embulk.EmbulkEmbed.run(EmbulkEmbed.java:278)
        at org.embulk.EmbulkRunner.runInternal(EmbulkRunner.java:288)
        at org.embulk.EmbulkRunner.run(EmbulkRunner.java:153)
        at org.embulk.cli.EmbulkRun.runInternal(EmbulkRun.java:115)
        at org.embulk.cli.EmbulkRun.run(EmbulkRun.java:24)
        at org.embulk.cli.Main.main(Main.java:53)
        Suppressed: java.lang.NullPointerException
                at org.embulk.exec.BulkLoader.doCleanup(BulkLoader.java:477)
                at org.embulk.exec.BulkLoader$3.run(BulkLoader.java:411)
                at org.embulk.exec.BulkLoader$3.run(BulkLoader.java:408)
                at org.embulk.spi.ExecInternal.doWith(ExecInternal.java:26)
                at org.embulk.exec.BulkLoader.cleanup(BulkLoader.java:408)
                at org.embulk.EmbulkEmbed.run(EmbulkEmbed.java:283)
                ... 5 more
Caused by: java.lang.RuntimeException: org.postgresql.util.PSQLException: The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver.
        at org.embulk.input.jdbc.AbstractJdbcInputPlugin.transaction(AbstractJdbcInputPlugin.java:227)
        at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:521)
        ... 11 more
Caused by: org.postgresql.util.PSQLException: The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver.
        at org.postgresql.Driver$ConnectThread.getResult(Driver.java:365)
        at org.postgresql.Driver.connect(Driver.java:288)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)
        at org.embulk.input.postgresql.PostgreSQLInputPlugin.newConnection(PostgreSQLInputPlugin.java:107)
        at org.embulk.input.postgresql.PostgreSQLInputPlugin.newConnection(PostgreSQLInputPlugin.java:24)
        at org.embulk.input.jdbc.AbstractJdbcInputPlugin.transaction(AbstractJdbcInputPlugin.java:213)
        ... 12 more

Error: java.lang.RuntimeException: org.postgresql.util.PSQLException: The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver.

latest driver

shin1103@DESKTOP-LBHCM86:~/work/embulk$ java -jar ~/.embulk/bin/embulk-0.11.5.jar run config.custom_driver.yml
2024-10-16 05:42:20.167 +0900 [INFO] (main): embulk_home is set by the location of embulk.properties found in: /home/shin1103/.embulk
2024-10-16 05:42:20.170 +0900 [INFO] (main): m2_repo is set as a sub directory of embulk_home: /home/shin1103/.embulk/lib/m2/repository
2024-10-16 05:42:20.170 +0900 [INFO] (main): gem_home is set as a sub directory of embulk_home: /home/shin1103/.embulk/lib/gems
2024-10-16 05:42:20.170 +0900 [INFO] (main): gem_path is set empty.
2024-10-16 05:42:20.171 +0900 [DEBUG] (main): Embulk system property "default_guess_plugin" is set to: "gzip,bzip2,json,csv"
2024-10-16 05:42:20.389 +0900 [INFO] (main): Started Embulk v0.11.5
2024-10-16 05:42:20.486 +0900 [INFO] (0001:transaction): Embulk system property "plugins.input.postgresql" is not set.
2024-10-16 05:42:20.486 +0900 [INFO] (0001:transaction): Embulk system property "plugins.default.input.postgresql" is not set.
2024-10-16 05:42:22.625 +0900 [INFO] (0001:transaction): Gem's home and path are set by system configs "gem_home": "/home/shin1103/.embulk/lib/gems", "gem_path": ""
2024-10-16 05:42:23.538 +0900 [INFO] (0001:transaction): Loaded JRuby runtime 9.4.8.0
2024-10-16 05:42:23.613 +0900 [INFO] (0001:transaction): Loaded plugin embulk-input-postgresql (0.13.2)
2024-10-16 05:42:23.719 +0900 [INFO] (0001:transaction): Embulk system property "plugins.output.postgresql" is not set.
2024-10-16 05:42:23.720 +0900 [INFO] (0001:transaction): Embulk system property "plugins.default.output.postgresql" is not set.
2024-10-16 05:42:23.757 +0900 [INFO] (0001:transaction): Loaded plugin embulk-output-postgresql (0.10.6)
/home/shin1103/.embulk/lib/gems/gems/embulk-0.11.5-java/lib/embulk/java_plugin.rb:92: warning: already initialized constant org.embulk.util.config::Task
2024-10-16 05:42:23.898 +0900 [WARN] (0001:transaction): "UTC" is recognized as "Z" to be compatible with the legacy style.
2024-10-16 05:42:23.929 +0900 [INFO] (0001:transaction): "driver_path" is set to load the Pg JDBC driver class "org.postgresql.Driver". Adding it to classpath.
2024-10-16 05:42:23.936 +0900 [INFO] (0001:transaction): Connecting to jdbc:postgresql://localhost:5432/postgres options {password=***, ApplicationName=embulk-input-postgresql, socketTimeout=1800, tcpKeepAlive=true, loginTimeout=300, user=postgres}
2024-10-16 05:42:24.115 +0900 [INFO] (0001:transaction): SQL: SET search_path TO "src"
2024-10-16 05:42:24.128 +0900 [INFO] (0001:transaction): Using JDBC Driver 42.7.4
2024-10-16 05:42:24.224 +0900 [INFO] (0001:transaction): Using local thread executor with max_threads=16 / output tasks 8 = input tasks 1 * 8
2024-10-16 05:42:24.281 +0900 [WARN] (0001:transaction): "UTC" is recognized as "Z" to be compatible with the legacy style.
2024-10-16 05:42:24.287 +0900 [INFO] (0001:transaction): Connecting to jdbc:postgresql://localhost:5432/postgres options {password=***, socketTimeout=1800, tcpKeepAlive=true, loginTimeout=300, user=postgres}
2024-10-16 05:42:24.381 +0900 [INFO] (0001:transaction): SQL: SET search_path TO "dest"
2024-10-16 05:42:24.385 +0900 [INFO] (0001:transaction): > 0.00 seconds
2024-10-16 05:42:24.392 +0900 [INFO] (0001:transaction): TransactionIsolation=read_committed
2024-10-16 05:42:24.392 +0900 [INFO] (0001:transaction): Using JDBC Driver 42.7.4
2024-10-16 05:42:24.392 +0900 [INFO] (0001:transaction): Using merge mode
2024-10-16 05:42:24.416 +0900 [INFO] (0001:transaction): SQL: CREATE TABLE "dest"."dest_tbl_0000019291eae920_embulk000" ("id" INT4, "price" NUMERIC(0,0))
2024-10-16 05:42:24.429 +0900 [ERROR] (0001:transaction): Operation failed (first exception)
java.lang.RuntimeException: org.embulk.util.retryhelper.RetryGiveupException: org.postgresql.util.PSQLException: ERROR: NUMERIC precision 0 must be between 1 and 1000
  Position: 79
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.createIntermediateTables(AbstractJdbcOutputPlugin.java:760)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.doBegin(AbstractJdbcOutputPlugin.java:606)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$2.run(AbstractJdbcOutputPlugin.java:491)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$RetryableSQLExecution.call(AbstractJdbcOutputPlugin.java:1343)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$RetryableSQLExecution.call(AbstractJdbcOutputPlugin.java:1331)
        at org.embulk.util.retryhelper.RetryExecutor.run(RetryExecutor.java:109)
        at org.embulk.util.retryhelper.RetryExecutor.runInterruptible(RetryExecutor.java:90)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.withRetry(AbstractJdbcOutputPlugin.java:1309)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.withRetry(AbstractJdbcOutputPlugin.java:1301)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.begin(AbstractJdbcOutputPlugin.java:485)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.transaction(AbstractJdbcOutputPlugin.java:461)
        at org.embulk.exec.BulkLoader$4$1$1.transaction(BulkLoader.java:535)
        at org.embulk.exec.LocalExecutorPlugin.transaction(LocalExecutorPlugin.java:54)
        at org.embulk.exec.BulkLoader$4$1.run(BulkLoader.java:530)
        at org.embulk.spi.util.FiltersInternal$RecursiveControl.transaction(FiltersInternal.java:85)
        at org.embulk.spi.util.FiltersInternal.transaction(FiltersInternal.java:43)
        at org.embulk.exec.BulkLoader$4.run(BulkLoader.java:525)
        at org.embulk.input.jdbc.AbstractJdbcInputPlugin.transaction(AbstractJdbcInputPlugin.java:230)
        at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:521)
        at org.embulk.exec.BulkLoader.access$000(BulkLoader.java:36)
        at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:353)
        at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:350)
        at org.embulk.spi.ExecInternal.doWith(ExecInternal.java:26)
        at org.embulk.exec.BulkLoader.run(BulkLoader.java:350)
        at org.embulk.EmbulkEmbed.run(EmbulkEmbed.java:278)
        at org.embulk.EmbulkRunner.runInternal(EmbulkRunner.java:288)
        at org.embulk.EmbulkRunner.run(EmbulkRunner.java:153)
        at org.embulk.cli.EmbulkRun.runInternal(EmbulkRun.java:115)
        at org.embulk.cli.EmbulkRun.run(EmbulkRun.java:24)
        at org.embulk.cli.Main.main(Main.java:53)
Caused by: org.embulk.util.retryhelper.RetryGiveupException: org.postgresql.util.PSQLException: ERROR: NUMERIC precision 0 must be between 1 and 1000
  Position: 79
        at org.embulk.util.retryhelper.RetryExecutor.run(RetryExecutor.java:116)
        at org.embulk.util.retryhelper.RetryExecutor.run(RetryExecutor.java:95)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.createIntermediateTables(AbstractJdbcOutputPlugin.java:688)
        ... 29 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: NUMERIC precision 0 must be between 1 and 1000
  Position: 79
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:356)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:341)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:317)
        at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:290)
        at org.embulk.output.jdbc.JdbcOutputConnection.executeUpdate(JdbcOutputConnection.java:641)
        at org.embulk.output.jdbc.JdbcOutputConnection.createTable(JdbcOutputConnection.java:204)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$6.call(AbstractJdbcOutputPlugin.java:703)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$6.call(AbstractJdbcOutputPlugin.java:688)
        at org.embulk.util.retryhelper.RetryExecutor.run(RetryExecutor.java:109)
        ... 31 common frames omitted
2024-10-16 05:42:24.430 +0900 [ERROR] (0001:transaction): Operation failed (last exception)
java.lang.RuntimeException: org.embulk.util.retryhelper.RetryGiveupException: org.postgresql.util.PSQLException: ERROR: NUMERIC precision 0 must be between 1 and 1000
  Position: 79
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.createIntermediateTables(AbstractJdbcOutputPlugin.java:760)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.doBegin(AbstractJdbcOutputPlugin.java:606)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$2.run(AbstractJdbcOutputPlugin.java:491)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$RetryableSQLExecution.call(AbstractJdbcOutputPlugin.java:1343)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$RetryableSQLExecution.call(AbstractJdbcOutputPlugin.java:1331)
        at org.embulk.util.retryhelper.RetryExecutor.run(RetryExecutor.java:109)
        at org.embulk.util.retryhelper.RetryExecutor.runInterruptible(RetryExecutor.java:90)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.withRetry(AbstractJdbcOutputPlugin.java:1309)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.withRetry(AbstractJdbcOutputPlugin.java:1301)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.begin(AbstractJdbcOutputPlugin.java:485)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.transaction(AbstractJdbcOutputPlugin.java:461)
        at org.embulk.exec.BulkLoader$4$1$1.transaction(BulkLoader.java:535)
        at org.embulk.exec.LocalExecutorPlugin.transaction(LocalExecutorPlugin.java:54)
        at org.embulk.exec.BulkLoader$4$1.run(BulkLoader.java:530)
        at org.embulk.spi.util.FiltersInternal$RecursiveControl.transaction(FiltersInternal.java:85)
        at org.embulk.spi.util.FiltersInternal.transaction(FiltersInternal.java:43)
        at org.embulk.exec.BulkLoader$4.run(BulkLoader.java:525)
        at org.embulk.input.jdbc.AbstractJdbcInputPlugin.transaction(AbstractJdbcInputPlugin.java:230)
        at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:521)
        at org.embulk.exec.BulkLoader.access$000(BulkLoader.java:36)
        at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:353)
        at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:350)
        at org.embulk.spi.ExecInternal.doWith(ExecInternal.java:26)
        at org.embulk.exec.BulkLoader.run(BulkLoader.java:350)
        at org.embulk.EmbulkEmbed.run(EmbulkEmbed.java:278)
        at org.embulk.EmbulkRunner.runInternal(EmbulkRunner.java:288)
        at org.embulk.EmbulkRunner.run(EmbulkRunner.java:153)
        at org.embulk.cli.EmbulkRun.runInternal(EmbulkRun.java:115)
        at org.embulk.cli.EmbulkRun.run(EmbulkRun.java:24)
        at org.embulk.cli.Main.main(Main.java:53)
Caused by: org.embulk.util.retryhelper.RetryGiveupException: org.postgresql.util.PSQLException: ERROR: NUMERIC precision 0 must be between 1 and 1000
  Position: 79
        at org.embulk.util.retryhelper.RetryExecutor.run(RetryExecutor.java:116)
        at org.embulk.util.retryhelper.RetryExecutor.run(RetryExecutor.java:95)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.createIntermediateTables(AbstractJdbcOutputPlugin.java:688)
        ... 29 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: NUMERIC precision 0 must be between 1 and 1000
  Position: 79
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:356)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:341)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:317)
        at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:290)
        at org.embulk.output.jdbc.JdbcOutputConnection.executeUpdate(JdbcOutputConnection.java:641)
        at org.embulk.output.jdbc.JdbcOutputConnection.createTable(JdbcOutputConnection.java:204)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$6.call(AbstractJdbcOutputPlugin.java:703)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$6.call(AbstractJdbcOutputPlugin.java:688)
        at org.embulk.util.retryhelper.RetryExecutor.run(RetryExecutor.java:109)
        ... 31 common frames omitted
2024-10-16 05:42:24.437 +0900 [INFO] (0001:cleanup): Embulk system property "plugins.input.postgresql" is not set.
2024-10-16 05:42:24.437 +0900 [INFO] (0001:cleanup): Embulk system property "plugins.default.input.postgresql" is not set.
2024-10-16 05:42:26.334 +0900 [INFO] (0001:cleanup): Gem's home and path are set by system configs "gem_home": "/home/shin1103/.embulk/lib/gems", "gem_path": ""
2024-10-16 05:42:27.036 +0900 [INFO] (0001:cleanup): Loaded JRuby runtime 9.4.8.0
2024-10-16 05:42:27.109 +0900 [INFO] (0001:cleanup): Loaded plugin embulk-input-postgresql (0.13.2)
2024-10-16 05:42:27.160 +0900 [INFO] (0001:cleanup): Embulk system property "plugins.output.postgresql" is not set.
2024-10-16 05:42:27.161 +0900 [INFO] (0001:cleanup): Embulk system property "plugins.default.output.postgresql" is not set.
2024-10-16 05:42:27.194 +0900 [INFO] (0001:cleanup): Loaded plugin embulk-output-postgresql (0.10.6)
/home/shin1103/.embulk/lib/gems/gems/embulk-0.11.5-java/lib/embulk/java_plugin.rb:92: warning: already initialized constant org.embulk.util.config::Task
org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: org.embulk.util.retryhelper.RetryGiveupException: org.postgresql.util.PSQLException: ERROR: NUMERIC precision 0 must be between 1 and 1000
  Position: 79
        at org.embulk.exec.BulkLoader$LoaderState.buildPartialExecuteException(BulkLoader.java:340)
        at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:580)
        at org.embulk.exec.BulkLoader.access$000(BulkLoader.java:36)
        at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:353)
        at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:350)
        at org.embulk.spi.ExecInternal.doWith(ExecInternal.java:26)
        at org.embulk.exec.BulkLoader.run(BulkLoader.java:350)
        at org.embulk.EmbulkEmbed.run(EmbulkEmbed.java:278)
        at org.embulk.EmbulkRunner.runInternal(EmbulkRunner.java:288)
        at org.embulk.EmbulkRunner.run(EmbulkRunner.java:153)
        at org.embulk.cli.EmbulkRun.runInternal(EmbulkRun.java:115)
        at org.embulk.cli.EmbulkRun.run(EmbulkRun.java:24)
        at org.embulk.cli.Main.main(Main.java:53)
        Suppressed: java.lang.NullPointerException
                at org.embulk.exec.BulkLoader.doCleanup(BulkLoader.java:477)
                at org.embulk.exec.BulkLoader$3.run(BulkLoader.java:411)
                at org.embulk.exec.BulkLoader$3.run(BulkLoader.java:408)
                at org.embulk.spi.ExecInternal.doWith(ExecInternal.java:26)
                at org.embulk.exec.BulkLoader.cleanup(BulkLoader.java:408)
                at org.embulk.EmbulkEmbed.run(EmbulkEmbed.java:283)
                ... 5 more
Caused by: java.lang.RuntimeException: org.embulk.util.retryhelper.RetryGiveupException: org.postgresql.util.PSQLException: ERROR: NUMERIC precision 0 must be between 1 and 1000
  Position: 79
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.createIntermediateTables(AbstractJdbcOutputPlugin.java:760)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.doBegin(AbstractJdbcOutputPlugin.java:606)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$2.run(AbstractJdbcOutputPlugin.java:491)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$RetryableSQLExecution.call(AbstractJdbcOutputPlugin.java:1343)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$RetryableSQLExecution.call(AbstractJdbcOutputPlugin.java:1331)
        at org.embulk.util.retryhelper.RetryExecutor.run(RetryExecutor.java:109)
        at org.embulk.util.retryhelper.RetryExecutor.runInterruptible(RetryExecutor.java:90)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.withRetry(AbstractJdbcOutputPlugin.java:1309)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.withRetry(AbstractJdbcOutputPlugin.java:1301)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.begin(AbstractJdbcOutputPlugin.java:485)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.transaction(AbstractJdbcOutputPlugin.java:461)
        at org.embulk.exec.BulkLoader$4$1$1.transaction(BulkLoader.java:535)
        at org.embulk.exec.LocalExecutorPlugin.transaction(LocalExecutorPlugin.java:54)
        at org.embulk.exec.BulkLoader$4$1.run(BulkLoader.java:530)
        at org.embulk.spi.util.FiltersInternal$RecursiveControl.transaction(FiltersInternal.java:85)
        at org.embulk.spi.util.FiltersInternal.transaction(FiltersInternal.java:43)
        at org.embulk.exec.BulkLoader$4.run(BulkLoader.java:525)
        at org.embulk.input.jdbc.AbstractJdbcInputPlugin.transaction(AbstractJdbcInputPlugin.java:230)
        at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:521)
        ... 11 more
Caused by: org.embulk.util.retryhelper.RetryGiveupException: org.postgresql.util.PSQLException: ERROR: NUMERIC precision 0 must be between 1 and 1000
  Position: 79
        at org.embulk.util.retryhelper.RetryExecutor.run(RetryExecutor.java:116)
        at org.embulk.util.retryhelper.RetryExecutor.run(RetryExecutor.java:95)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin.createIntermediateTables(AbstractJdbcOutputPlugin.java:688)
        ... 29 more
Caused by: org.postgresql.util.PSQLException: ERROR: NUMERIC precision 0 must be between 1 and 1000
  Position: 79
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:356)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:341)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:317)
        at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:290)
        at org.embulk.output.jdbc.JdbcOutputConnection.executeUpdate(JdbcOutputConnection.java:641)
        at org.embulk.output.jdbc.JdbcOutputConnection.createTable(JdbcOutputConnection.java:204)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$6.call(AbstractJdbcOutputPlugin.java:703)
        at org.embulk.output.jdbc.AbstractJdbcOutputPlugin$6.call(AbstractJdbcOutputPlugin.java:688)
        at org.embulk.util.retryhelper.RetryExecutor.run(RetryExecutor.java:109)
        ... 31 more

Error: java.lang.RuntimeException: org.embulk.util.retryhelper.RetryGiveupException: org.postgresql.util.PSQLException: ERROR: NUMERIC precision 0 must be between 1 and 1000
  Position: 79
shin1103 commented 5 days ago

The difference in the error is likely due to the JDBC driver version.

The difference is due to this issue. https://github.com/pgjdbc/pgjdbc/issues/2188

And fixed by this pull request. https://github.com/pgjdbc/pgjdbc/pull/2189

This change is applied from 42.2.23 https://github.com/pgjdbc/pgjdbc/blob/d98b56becf9171aa75ec848d4a5ec2bfa80381d2/docs/content/changelogs/2021-07-06-42.2.23-release.md

shin1103 commented 5 days ago

pgjdbc supports SCRAM-SHA-256 from 42.2.0. https://jdbc.postgresql.org/changelogs/2018-01-17-42.2.0-release/

pull request is here. https://github.com/pgjdbc/pgjdbc/pull/842