[Bug report] Open schema in some postgres catalog, encounter issue: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException

[danhuawang]

Version

main branch

Describe what's wrong

Error message and/or stacktrace

{
    "code": 1002,
    "type": "RuntimeException",
    "message": "Failed to operate object [hr] operation [LOAD] under [pg_catalog2], reason [Fail to import schema entity to the store.]",
    "stack": [
        "java.lang.RuntimeException: Fail to import schema entity to the store.",
        "\tat org.apache.gravitino.catalog.SchemaOperationDispatcher.importSchema(SchemaOperationDispatcher.java:355)",
        "\tat org.apache.gravitino.catalog.SchemaOperationDispatcher.lambda$loadSchema$7(SchemaOperationDispatcher.java:183)",
        "\tat org.apache.gravitino.lock.TreeLockUtils.doWithTreeLock(TreeLockUtils.java:49)",
        "\tat org.apache.gravitino.catalog.SchemaOperationDispatcher.loadSchema(SchemaOperationDispatcher.java:179)",
        "\tat org.apache.gravitino.hook.SchemaHookDispatcher.loadSchema(SchemaHookDispatcher.java:79)",
        "\tat org.apache.gravitino.catalog.SchemaNormalizeDispatcher.loadSchema(SchemaNormalizeDispatcher.java:71)",
        "\tat org.apache.gravitino.listener.SchemaEventDispatcher.loadSchema(SchemaEventDispatcher.java:107)",
        "\tat org.apache.gravitino.server.web.rest.SchemaOperations.lambda$loadSchema$4(SchemaOperations.java:150)",
        "\tat java.base/java.security.AccessController.doPrivileged(AccessController.java:712)",
        "\tat java.base/javax.security.auth.Subject.doAs(Subject.java:439)",
        "\tat org.apache.gravitino.utils.PrincipalUtils.doAs(PrincipalUtils.java:39)",
        "\tat org.apache.gravitino.server.web.Utils.doAs(Utils.java:188)",
        "\tat org.apache.gravitino.server.web.rest.SchemaOperations.loadSchema(SchemaOperations.java:146)",
        "\tat jdk.internal.reflect.GeneratedMethodAccessor156.invoke(Unknown Source)",
        "\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)",
        "\tat java.base/java.lang.reflect.Method.invoke(Method.java:568)",
        "\tat org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory.lambda$static$0(ResourceMethodInvocationHandlerFactory.java:52)",
        "\tat org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:146)",
        "\tat org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:189)",
        "\tat org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:176)",
        "\tat org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:93)",
        "\tat org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:478)",
        "\tat org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:400)",
        "\tat org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:81)",
        "\tat org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:256)",
        "\tat org.glassfish.jersey.internal.Errors$1.call(Errors.java:248)",
        "\tat org.glassfish.jersey.internal.Errors$1.call(Errors.java:244)",
        "\tat org.glassfish.jersey.internal.Errors.process(Errors.java:292)",
        "\tat org.glassfish.jersey.internal.Errors.process(Errors.java:274)",
        "\tat org.glassfish.jersey.internal.Errors.process(Errors.java:244)",
        "\tat org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:265)",
        "\tat org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:235)",
        "\tat org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:684)",
        "\tat org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:394)",
        "\tat org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:346)",
        "\tat org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:358)",
        "\tat org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:311)",
        "\tat org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:205)",
        "\tat org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:799)",
        "\tat org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1656)",
        "\tat org.apache.gravitino.server.authentication.AuthenticationFilter.doFilter(AuthenticationFilter.java:86)",
        "\tat org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)",
        "\tat org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)",
        "\tat org.apache.gravitino.server.web.VersioningFilter.doFilter(VersioningFilter.java:111)",
        "\tat org.eclipse.jetty.servlet.FilterHolder.doFilter(FilterHolder.java:193)",
        "\tat org.eclipse.jetty.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1626)",
        "\tat org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:552)",
        "\tat org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)",
        "\tat org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:600)",
        "\tat org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)",
        "\tat org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)",
        "\tat org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1624)",
        "\tat org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)",
        "\tat org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1440)",
        "\tat org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)",
        "\tat org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:505)",
        "\tat org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594)",
        "\tat org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)",
        "\tat org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1355)",
        "\tat org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)",
        "\tat org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)",
        "\tat org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)",
        "\tat org.eclipse.jetty.server.Server.handle(Server.java:516)",
        "\tat org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:487)",
        "\tat org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:732)",
        "\tat org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:479)",
        "\tat org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277)",
        "\tat org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)",
        "\tat org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)",
        "\tat org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)",
        "\tat org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:338)",
        "\tat org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:315)",
        "\tat org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:173)",
        "\tat org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)",
        "\tat org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:409)",
        "\tat org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883)",
        "\tat org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034)",
        "\tat java.base/java.lang.Thread.run(Thread.java:833)",
        "Caused by: org.apache.gravitino.EntityAlreadyExistsException: The SCHEMA entity: pg_test.pg_catalog2.hr already exists.",
        "\tat org.apache.gravitino.storage.relational.converters.H2ExceptionConverter.toGravitinoException(H2ExceptionConverter.java:41)",
        "\tat org.apache.gravitino.storage.relational.utils.ExceptionUtils.checkSQLException(ExceptionUtils.java:33)",
        "\tat org.apache.gravitino.storage.relational.service.SchemaMetaService.insertSchema(SchemaMetaService.java:137)",
        "\tat org.apache.gravitino.storage.relational.JDBCBackend.insert(JDBCBackend.java:138)",
        "\tat org.apache.gravitino.storage.relational.RelationalEntityStore.put(RelationalEntityStore.java:110)",
        "\tat org.apache.gravitino.catalog.SchemaOperationDispatcher.importSchema(SchemaOperationDispatcher.java:352)",
        "\t... 77 more",
        "Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: \"PUBLIC.UK_CID_SN_DEL_INDEX_4 ON PUBLIC.SCHEMA_META(CATALOG_ID, SCHEMA_NAME, DELETED_AT) VALUES 3331488371130917128\"; SQL statement:",
        "INSERT INTO schema_meta(schema_id, schema_name, metalake_id, catalog_id, schema_comment, properties, audit_info, current_version, last_version, deleted_at) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ON DUPLICATE KEY UPDATE schema_name = ?, metalake_id = ?, catalog_id = ?, schema_comment = ?, properties = ?, audit_info = ?, current_version = ?, last_version = ?, deleted_at = ? [23505-200]",
        "\tat org.h2.message.DbException.getJdbcSQLException(DbException.java:459)",
        "\tat org.h2.message.DbException.getJdbcSQLException(DbException.java:429)",
        "\tat org.h2.message.DbException.get(DbException.java:205)",
        "\tat org.h2.message.DbException.get(DbException.java:181)",
        "\tat org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:103)",
        "\tat org.h2.mvstore.db.MVSecondaryIndex.checkUnique(MVSecondaryIndex.java:221)",
        "\tat org.h2.mvstore.db.MVSecondaryIndex.add(MVSecondaryIndex.java:196)",
        "\tat org.h2.mvstore.db.MVTable.addRow(MVTable.java:531)",
        "\tat org.h2.table.Table.updateRows(Table.java:523)",
        "\tat org.h2.command.dml.Update.update(Update.java:225)",
        "\tat org.h2.command.dml.Insert.handleOnDuplicate(Insert.java:436)",
        "\tat org.h2.command.dml.Insert.insertRows(Insert.java:197)",
        "\tat org.h2.command.dml.Insert.update(Insert.java:151)",
        "\tat org.h2.command.CommandContainer.update(CommandContainer.java:198)",
        "\tat org.h2.command.Command.executeUpdate(Command.java:251)",
        "\tat org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:240)",
        "\tat org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:95)",
        "\tat org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:95)",
        "\tat org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)",
        "\tat org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)",
        "\tat org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)",
        "\tat org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)",
        "\tat org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)",
        "\tat org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)",
        "\tat org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)",
        "\tat org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)",
        "\tat org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)",
        "\tat org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)",
        "\tat jdk.proxy2/jdk.proxy2.$Proxy84.insertSchemaMetaOnDuplicateKeyUpdate(Unknown Source)",
        "\tat org.apache.gravitino.storage.relational.service.SchemaMetaService.lambda$insertSchema$4(SchemaMetaService.java:131)",
        "\tat org.apache.gravitino.storage.relational.utils.SessionUtils.doWithCommit(SessionUtils.java:47)",
        "\tat org.apache.gravitino.storage.relational.service.SchemaMetaService.insertSchema(SchemaMetaService.java:126)",
        "\t... 80 more"
    ]
}

How to reproduce

It can be reproduced in a CI environment

Additional context

No response

[danhuawang]

@yuqi1129 Can you help check this issue ?

[jerqi]

Now, we should write a uuid to schema properties. If a PG is mounted by multple catalogs. It will be conflict. schema id should be unique. It can only have a metalake id and catalog id in our entity store.

[jerryshao]

I will close this as won't fix. If later on we want to support such scenario, we can reopen this issue.