hanicz opened a new pull request, #1477: URL: https://github.com/apache/knox/pull/1477
… key on pg_type_typname_nsp_index during concurrent table creation [KNOX-3536](https://issues.apache.org/jira/browse/KNOX-3536) - JDBCTokenStateService fails on PostgreSQL in HA: duplicate key on pg_type_typname_nsp_index during concurrent table creation ## What changes were proposed in this pull request? The Postgres token DDL scripts wrap CREATE TABLE IF NOT EXISTS in pg_advisory_lock / pg_advisory_unlock. JDBCUtils.createTableFromSQL sends the whole script in a single Statement.execute(), and PostgreSQL runs it as one implicit transaction. As a result, the unlock happens before the CREATE TABLE is committed. When several Knox instances start at the same time, another instance can then take the lock, fail to see the uncommitted table, and fail with duplicate key value violates unique constraint "pg_type_typname_nsp_index". This PR switches both scripts (createKnoxTokenDatabaseTablePostgres.sql, createKnoxTokenMetadataDatabaseTablePostgres.sql) to pg_advisory_xact_lock(12345) and removes the explicit unlock. That lock is released only after the transaction commits. ## How was this patch tested? Tested locally the script on a postgres 16 db running in docker -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
