danhuawang opened a new issue, #12238: URL: https://github.com/apache/gravitino/issues/12238
### Describe what's wrong When dropping a table using PostgreSQL as the backend database, a duplicate key constraint violation occurs if the table version has already been soft-deleted. This happens when: 1. A table is dropped (soft-delete succeeds) 2. The same table is dropped again (e.g., during cleanup, retry, or test teardown) 3. Both operations occur within the same millisecond ### Error message and/or stacktrace ``` java.lang.RuntimeException: Failed to operate object [table_name] operation [DROP] under [GRAVITINO], reason [ERROR: duplicate key value violates unique constraint "table_version_info_table_id_version_deleted_at_key" Detail: Key (table_id, version, deleted_at)=(3906110126923049197, 1, 1785232569753) already exists.] Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "table_version_info_table_id_version_deleted_at_key" Detail: Key (table_id, version, deleted_at)=(3906110126923049197, 1, 1785232569753) already exists. ### SQL: UPDATE table_version_info SET deleted_at = round(extract(epoch from(current_timestamp - timestamp '1970-01-01 00:00:00')) * 1000) WHERE table_id = ? AND version = ? ``` ### How to reproduce 1. Use PostgreSQL as the backend database 2. Create a table in Gravitino 3. Drop the table twice in quick succession (within 1 millisecond) 4. The second DROP will fail with the constraint violation This commonly occurs in test environments where `DROP TABLE IF EXISTS` is called both at the beginning (cleanup) and in the finally block (teardown). ### Versions **Gravitino version**: 0.9.0 / main branch **Database**: PostgreSQL (MySQL and H2 are NOT affected) ### Additional context #### Root Cause The PostgreSQL-specific implementation of `softDeleteTableVersionByTableIdAndVersion` in `TableVersionPostgreSQLProvider` is missing the `AND deleted_at = 0` condition that exists in the base implementation for MySQL/H2. **Current PostgreSQL SQL:** ```sql UPDATE table_version_info SET deleted_at = round(extract(epoch from(current_timestamp - timestamp '1970-01-01 00:00:00')) * 1000) WHERE table_id = ? AND version = ? ``` **Expected SQL (like MySQL/H2):** ```sql UPDATE table_version_info SET deleted_at = round(extract(epoch from(current_timestamp - timestamp '1970-01-01 00:00:00')) * 1000) WHERE table_id = ? AND version = ? AND deleted_at = 0 ``` Without the `deleted_at = 0` condition: - The UPDATE statement attempts to modify already soft-deleted records - If executed within the same millisecond, it tries to set the same `deleted_at` timestamp - This violates the unique constraint `(table_id, version, deleted_at)` #### Affected Code **File:** `core/src/main/java/org/apache/gravitino/storage/relational/mapper/provider/postgresql/TableVersionPostgreSQLProvider.java` **Method:** `softDeleteTableVersionByTableIdAndVersion()` #### Suggested Fix Add `AND deleted_at = 0` to the WHERE clause in the PostgreSQL implementation to match the base provider behavior and ensure idempotency. This will: 1. Prevent duplicate key violations on retry/redundant deletes 2. Make the operation idempotent (safe to call multiple times) 3. Align PostgreSQL behavior with MySQL/H2 -- 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]
