WSO2 Identity Server 6.1.0中BPS是否需独立PostgreSQL数据库？

我安装了PostgreSQL 14，并执行了WSO2 Identity Server 6.1.0的4个脚本：

• <IS-HOME>/dbscripts/identity/postgresql.sql
• <IS-HOME>/dbscripts/consent/postgresql.sql
• <IS-HOME>/dbscripts/postgresql.sql
• <IS-HOME>/dbscripts/bps/bpel/create/postgresql.sql

我创建了一个单独的数据库和用户，所有脚本都在这个数据库中执行。已按照官方文档完成WSO2应用的安装和配置，应用可以启动，但出现以下警告，同时数据库日志有报错：

数据库错误日志 #

2023-05-25 10:04:50.565 EDT [361255] ERROR: current transaction is aborted, commands ignored until end of transaction block
2023-05-25 10:04:50,565 EDT [361255] STATEMENT: SELECT pg_get_serial_sequence($1, $2)

WSO2应用警告日志 #

[2023-05-24 13:27:06,479] []  WARN {openjpa.jdbc.JDBC} - Unable to determine which sequences are owned by the database. OpenJPA will consider all sequences suffixed with "_seq" as database managed.  This may result in improper creation or removal of sequences with this suffix. The original PostgreSQL driver exception is being logged for your reference. org.apache.openjpa.lib.jdbc.ReportingSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block {prepstmnt 282433064 SELECT pg_get_serial_sequence(?, ?) [params=?, ?]} [code=0, state=25P02]
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:247)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:223)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$1000(LoggingConnectionDecorator.java:72)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.execute(LoggingConnectionDecorator.java:1258)
at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:367)
at org.apache.openjpa.jdbc.sql.PostgresDictionary.queryOwnership(PostgresDictionary.java:465)
at org.apache.openjpa.jdbc.sql.PostgresDictionary.isOwnedSequence(PostgresDictionary.java:430)
at org.apache.openjpa.jdbc.sql.PostgresDictionary.isSystemSequence(PostgresDictionary.java:396)
at org.apache.openjpa.jdbc.schema.SchemaGenerator.generateSequences(SchemaGenerator.java:960)
at org.apache.openjpa.jdbc.schema.SchemaGenerator.generateSchema(SchemaGenerator.java:367)
at org.apache.openjpa.jdbc.schema.SchemaGenerator.generateSchemas(SchemaGenerator.java:300)
at org.apache.openjpa.jdbc.schema.SchemaTool.getDBSchemaGroup(SchemaTool.java:1166)
at org.apache.openjpa.jdbc.schema.SchemaTool.add(SchemaTool.java:366)
at org.apache.openjpa.jdbc.schema.SchemaTool.run(SchemaTool.java:343)
at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:505)
at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:455)
at org.apache.openjpa.jdbc.kernel.JDBCBrokerFactory.synchronizeMappings(JDBCBrokerFactory.java:160)
at org.apache.openjpa.jdbc.kernel.JDBCBrokerFactory.synchronizeMappings(JDBCBrokerFactory.java:164)
at org.apache.openjpa.jdbc.kernel.JDBCBrokerFactory.newBrokerImpl(JDBCBrokerFactory.java:122)

另外数据库日志还出现过：

2023-05-29 07:07:23.706 EDT [428497] ERROR: relation "um" does not exist
2023-05-29 07:07:23.706 EDT [428497] STATEMENT: SELECT pg_get_serial_sequence($1, $2)
2023-05-29 07:07:23.707 EDT [428497] ERROR: relation "um_group" does not exist
2023-05-29 07:07:23.707 EDT [428497] STATEMENT: SELECT pg_get_serial_sequence($1, $2)
2023-05-29 07:07:23.707 EDT [428497] ERROR: relation "um_group_uuid" does not exist

我尝试在repository/conf/deployment.toml中设置defaultAutoCommit="true"，但问题仍然存在。不过如果将BPS脚本单独部署到另一个数据库，就不会出现这些错误。

提问：是否必须将BPS安装在单独的PostgreSQL数据库中？

不是必须单独部署，但需要解决模式隔离与OpenJPA配置冲突问题：

1. 问题根源
   报错中relation "um" does not exist本质是OpenJPA查询序列所属关系时，未正确指定表的数据库模式（schema）。当IS和BPS脚本共用同一数据库时，两者的表可能分属不同模式，或OpenJPA配置未正确识别模式，导致查询时找不到对应表，触发事务中断。

2. 修复步骤

   • 统一数据库模式：确保所有脚本执行时使用同一模式，或在deployment.toml中明确指定各组件的数据库模式：

     [database.identity_db]
     schema = "public" # 替换为你使用的统一模式名
     
     [database.bps_db]
     schema = "public"
     

   • 设置用户默认模式：执行PostgreSQL命令，确保数据库用户连接时默认模式包含所有IS和BPS表：

     ALTER USER your_db_user SET search_path TO public;
     

   • 禁用OpenJPA自动同步：关闭OpenJPA的自动schema检测功能，避免它尝试自动识别序列所属关系：

     [openjpa]
     autoDetectSchema = "false"
     

3. 单独数据库生效的原因
   单独部署时，每个数据库的默认模式和表空间完全隔离，OpenJPA不会跨组件查询不存在的表/序列，自然不会触发事务错误，但这只是规避方案，而非强制要求。

内容的提问来源于stack exchange，提问作者Roberta Martins Costa