apache / apache/shardingsphere
Error exception when migration with PG 15
- Dominant language
- Java
- Stars
- 20.8k
- Forks
- 6.9k
- Avg merge
- 9h 55m
- Merged PRs (30d)
- 311
Description
## Bug Report
### Which version of ShardingSphere did you use?
master
35856435d1b10f9c1d388b3040c4c8d8005616e2
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
proxy
### Expected behavior
migration successfully
### Actual behavior
migration failed
```
[ERROR] 2023-06-02 16:02:04.136 [j0102p00002c60d901819e54f4b5361c5781d943ca_Worker-1] o.a.s.d.p.c.job.AbstractPipelineJob - job prepare failed, j0102p00002c60d901819e54f4b5361c5781d943ca-0
org.apache.shardingsphere.infra.util.exception.external.sql.type.wrapper.SQLWrapperException: ERROR: column "datlastsysoid" does not exist
Position: 21
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.AbstractPostgreSQLDDLAdapter.executeByTemplateForSingleRow(AbstractPostgreSQLDDLAdapter.java:83)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLTablePropertiesLoader.fetchDataBaseId(PostgreSQLTablePropertiesLoader.java:60)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLTablePropertiesLoader.load(PostgreSQLTablePropertiesLoader.java:50)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLCreateTableSQLGenerator.loadMaterials(PostgreSQLCreateTableSQLGenerator.java:52)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLCreateTableSQLGenerator.generate(PostgreSQLCreateTableSQLGenerator.java:43)
at org.apache.shardingsphere.data.pipeline.core.metadata.generator.PipelineDDLGenerator.generateLogicDDL(PipelineDDLGenerator.java:79)
at org.apache.shardingsphere.data.pipeline.core.prepare.datasource.AbstractDataSourcePreparer.getCreateTargetTableSQL(AbstractDataSourcePreparer.java:123)
at org.apache.shardingsphere.data.pipeline.postgresql.prepare.datasource.PostgreSQLDataSourcePreparer.prepareTargetTables(PostgreSQLDataSourcePreparer.java:40)
at org.apache.shardingsphere.data.pipeline.core.prepare.PipelineJobPreparerUtils.prepareTargetTables(PipelineJobPreparerUtils.java:123)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepareTarget(MigrationJobPreparer.java:153)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepareAndCheckTarget(MigrationJobPreparer.java:132)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepareAndCheckTargetWithLock(MigrationJobPreparer.java:118)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepare(MigrationJobPreparer.java:84)
at org.apache.shardingsphere.data.pipeline.scenario.migration.MigrationJob.doPrepare(MigrationJob.java:76)
at org.apache.shardingsphere.data.pipeline.core.job.AbstractPipelineJob.prepare(AbstractPipelineJob.java:90)
at org.apache.shardingsphere.data.pipeline.core.job.AbstractSimplePipelineJob.execute(AbstractSimplePipelineJob.java:61)
at org.apache.shardingsphere.elasticjob.simple.executor.SimpleJobExecutor.process(SimpleJobExecutor.java:33)
at org.apache.shardingsphere.elasticjob.simple.executor.SimpleJobExecutor.process(SimpleJobExecutor.java:29)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.process(ElasticJobExecutor.java:173)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.process(ElasticJobExecutor.java:142)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.execute(ElasticJobExecutor.java:124)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.execute(ElasticJobExecutor.java:100)
at org.apache.shardingsphere.elasticjob.lite.internal.schedule.LiteJob.execute(LiteJob.java:35)
at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: org.postgresql.util.PSQLException: ERROR: column "datlastsysoid" does not exist
Position: 21
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.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:243)
at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:110)
at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.AbstractPostgreSQLDDLAdapter.executeByTemplateForSingleRow(AbstractPostgreSQLDDLAdapter.java:80)
... 24 common frames omitted
[INFO ] 2023-06-02 16:02:04.170 [Pipeline-EventListener-0] o.a.s.d.p.c.m.n.e.h.i.ConfigMetaDataChangedEventHandler - UPDATED job configuration: /pipeline/jobs/j0102p00002c60d901819e54f4b5361c5781d943ca/config, disabled=true
[INFO ] 2023-06-02 16:02:04.170 [Pipeline-EventListener-0] o.a.s.d.p.c.job.AbstractPipelineJob - stop tasks runner, jobId=j0102p00002c60d901819e54f4b5361c5781d943ca
[INFO ] 2023-06-02 16:02:06.321 [Pipeline-EventListener-0] org.quartz.core.QuartzScheduler - Scheduler j0102p00002c60d901819e54f4b5361c5781d943ca_$_NON_CLUSTERED shutting down.
[INFO ] 2023-06-02 16:02:06.321 [Pipeline-EventListener-0] org.quartz.core.QuartzScheduler - Scheduler j0102p00002c60d901819e54f4b5361c5781d943ca_$_NON_CLUSTERED paused.
[INFO ] 2023-06-02 16:02:06.327 [Pipeline-EventListener-0] org.quartz.core.QuartzScheduler - Scheduler j0102p00002c60d901819e54f4b5361c5781d943ca_$_NON_CLUSTERED shutdown complete.
[ERROR] 2023-06-02 16:02:06.351 [j0102p00002c60d901819e54f4b5361c5781d943ca_Worker-1] o.a.s.e.e.h.g.LogJobErrorHandler - Job 'j0102p00002c60d901819e54f4b5361c5781d943ca' exception occur in job processing
org.apache.shardingsphere.infra.util.exception.external.sql.type.wrapper.SQLWrapperException: ERROR: column "datlastsysoid" does not exist
Position: 21
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.AbstractPostgreSQLDDLAdapter.executeByTemplateForSingleRow(AbstractPostgreSQLDDLAdapter.java:83)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLTablePropertiesLoader.fetchDataBaseId(PostgreSQLTablePropertiesLoader.java:60)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLTablePropertiesLoader.load(PostgreSQLTablePropertiesLoader.java:50)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLCreateTableSQLGenerator.loadMaterials(PostgreSQLCreateTableSQLGenerator.java:52)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLCreateTableSQLGenerator.generate(PostgreSQLCreateTableSQLGenerator.java:43)
at org.apache.shardingsphere.data.pipeline.core.metadata.generator.PipelineDDLGenerator.generateLogicDDL(PipelineDDLGenerator.java:79)
at org.apache.shardingsphere.data.pipeline.core.prepare.datasource.AbstractDataSourcePreparer.getCreateTargetTableSQL(AbstractDataSourcePreparer.java:123)
at org.apache.shardingsphere.data.pipeline.postgresql.prepare.datasource.PostgreSQLDataSourcePreparer.prepareTargetTables(PostgreSQLDataSourcePreparer.java:40)
at org.apache.shardingsphere.data.pipeline.core.prepare.PipelineJobPreparerUtils.prepareTargetTables(PipelineJobPreparerUtils.java:123)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepareTarget(MigrationJobPreparer.java:153)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepareAndCheckTarget(MigrationJobPreparer.java:132)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepareAndCheckTargetWithLock(MigrationJobPreparer.java:118)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepare(MigrationJobPreparer.java:84)
at org.apache.shardingsphere.data.pipeline.scenario.migration.MigrationJob.doPrepare(MigrationJob.java:76)
at org.apache.shardingsphere.data.pipeline.core.job.AbstractPipelineJob.prepare(AbstractPipelineJob.java:90)
at org.apache.shardingsphere.data.pipeline.core.job.AbstractSimplePipelineJob.execute(AbstractSimplePipelineJob.java:61)
at org.apache.shardingsphere.elasticjob.simple.executor.SimpleJobExecutor.process(SimpleJobExecutor.java:33)
at org.apache.shardingsphere.elasticjob.simple.executor.SimpleJobExecutor.process(SimpleJobExecutor.java:29)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.process(ElasticJobExecutor.java:173)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.process(ElasticJobExecutor.java:142)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.execute(ElasticJobExecutor.java:124)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.execute(ElasticJobExecutor.java:100)
at org.apache.shardingsphere.elasticjob.lite.internal.schedule.LiteJob.execute(LiteJob.java:35)
at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: org.postgresql.util.PSQLException: ERROR: column "datlastsysoid" does not exist
Position: 21
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.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:243)
at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:110)
at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.AbstractPostgreSQLDDLAdapter.executeByTemplateForSingleRow(AbstractPostgreSQLDDLAdapter.java:80)
... 24 common frames omitted
```
### Reason analyze (If you can)
Postgres 15 removed datlastsysoid field from pg_database table, so the error `column "datlastsysoid" does not exist` exception
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
migration with PG 15
Contributor guide
Research direction
Start with AbstractPostgreSQLDDLAdapter.java and PostgreSQLTablePropertiesLoader.java, especially the fetchDataBaseId path shown in the stack trace. Reproduce migration against PostgreSQL 15 and inspect the failing metadata lookup. Done means PostgreSQL 15 migration preparation succeeds without the missing datlastsysoid error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, postgresql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100