relation **** is still open issue

The error message you're encountering in Amazon Redshift, specifically "ERROR: relation [number] is still open," typically indicates that there's an open transaction or active process using the table you're attempting to drop.

Here are some potential reasons and steps to resolve this issue:

1. Active Sessions or Transactions: Check for any active sessions or transactions that might be using the table wrk_xxx that you're trying to drop. In some cases, there might be uncommitted transactions or open sessions that keep the table open.

2. Locks or Concurrent Operations: Verify if there are locks or concurrent operations happening on the table. These might prevent the table from being dropped. Use the SVV_LOCKS view to check for any active locks on the table.

3. Query Abort or Termination: It's possible that a previous query execution in the stored procedure wasn't properly terminated or completed. Ensure that all previous queries or transactions within the stored procedure have finished execution or are properly closed.

4. Analyze the Differences between Environments: Since you mentioned this issue occurs only in the dev environment, compare the configurations, workload, and any ongoing activities between the dev and prod environments. Look for any discrepancies in sessions, configurations, or running processes.

5. Isolation Level or Vacuum Process: Redshift uses a snapshot-based isolation level. Check if there's a long-running transaction or if the table is being used in an ongoing transaction that prevents the drop operation. Also, consider running a VACUUM operation on the table to clean up and release any unused space.

6. Review Stored Procedure Code:

   • Examine the stored procedure code (sp_merge_xxx) and ensure there are no anomalies that might cause an unexpected behavior or keep the table open inadvertently. Review the SQL statements executed before the DROP TABLE command.

7. Permissions and Ownership:

   • Ensure that the user or role executing the stored procedure has the necessary permissions to drop the table and that there are no ownership issues causing the operation to fail.

8. Contact AWS Support: If the issue persists and you can't identify the root cause, consider reaching out to AWS Support. They might have specific insights or tools to diagnose and resolve this issue.

Identifying the root cause might involve checking for active sessions, locks, or long-running transactions that prevent the table drop. Additionally, comparing the differences between the dev and prod environments might provide clues as to why this issue is specific to the dev environment.