[
https://issues.apache.org/jira/browse/KNOX-3536?focusedWorklogId=1051294&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-1051294
]
ASF GitHub Bot logged work on KNOX-3536:
----------------------------------------
Author: ASF GitHub Bot
Created on: 29/Oct/26 20:09
Start Date: 29/Oct/26 20:09
Worklog Time Spent: 10m
Work Description: 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
Issue Time Tracking
-------------------
Worklog Id: (was: 1051294)
Remaining Estimate: 0h
Time Spent: 10m
> JDBCTokenStateService fails on PostgreSQL in HA: duplicate key on
> pg_type_typname_nsp_index during concurrent table creation
> ----------------------------------------------------------------------------------------------------------------------------
>
> Key: KNOX-3536
> URL: https://issues.apache.org/jira/browse/KNOX-3536
> Project: Apache Knox
> Issue Type: Bug
> Components: Server
> Affects Versions: 3.0.0
> Reporter: Tamás Hanicz
> Assignee: Tamás Hanicz
> Priority: Major
> Time Spent: 10m
> Remaining Estimate: 0h
>
> When several Knox instances start at the same time against an empty
> PostgreSQL database, \{{JDBCTokenStateService}} can fail to initialize:
> {noformat}
> PSQLException: ERROR: duplicate key value violates unique constraint
> "pg_type_typname_nsp_index"
> Detail: Key (typname, typnamespace)=(knox_tokens, 2200) already exists.
> {noformat}
> Cause: The Postgres DDL scripts wrap \{{CREATE TABLE IF NOT EXISTS}} in
> \{{pg_advisory_lock}} / \{{pg_advisory_unlock}}. The whole script is sent in
> one \{{Statement.execute()}}, which PostgreSQL runs as a single implicit
> transaction. As a result, the unlock happens before the \{{CREATE TABLE}}
> commits, and the next instance creates the table again.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
