You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "Qianyi951015 (via GitHub)" <gi...@apache.org> on 2023/06/08 12:21:38 UTC
[GitHub] [shardingsphere] Qianyi951015 opened a new issue, #26203: Error exception when migration with PG 15
Qianyi951015 opened a new issue, #26203:
URL: https://github.com/apache/shardingsphere/issues/26203
## 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
--
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Error exception when migration with PG 15 [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #26203:
URL: https://github.com/apache/shardingsphere/issues/26203#issuecomment-3627909364
## Issue Understanding
When migrating from PostgreSQL 15, the pipeline fails during DDL preparation with column "datlastsysoid" does not exist, which stops the migration.
## Root Cause
The PostgreSQL metadata lookup in the migration module still queries the legacy pg_database.datlastsysoid column to obtain a database identifier; this column was removed in PostgreSQL 15, so the query fails.
## Analysis
- The stack trace shows PostgreSQLTablePropertiesLoader.fetchDataBaseId querying pg_database and relying on datlastsysoid.
- PostgreSQL 15 dropped this column; we should switch to a compatible field (e.g., oid) or another officially supported way to fetch the database identifier.
- No compatibility branch exists for PG 15 in the current code path, so migrations on PG 15/15+ will always hit this failure.
## Conclusion
This is a PostgreSQL 15 metadata compatibility gap; the pipeline must stop depending on datlastsysoid.
## Fix Suggestion
- In PostgreSQLTablePropertiesLoader.fetchDataBaseId (and related templates), remove the datlastsysoid dependency and use pg_database.oid or another supported column to obtain the database identifier, ensuring compatibility across PG 12–15+. Example (adjust to actual
classes/templates):
// Example: fetch current database oid without relying on datlastsysoid
SELECT oid FROM pg_database WHERE datname = current_database();
- Add/update PG 15 pipeline migration integration tests to confirm the preparation phase no longer fails due to the missing column.
Community contributions are warmly welcomed—please feel free to submit a PR with the code change and tests to improve PG 15 migration compatibility!
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org