Скачать или смотреть How to Overcome Locking Issues When Changing Table Schema in PostgreSQL

Discover effective solutions to handle `long-running queries` and achieve seamless schema changes in PostgreSQL.
---
This video is based on the question https://stackoverflow.com/q/70834840/ asked by the user 'Witos' ( https://stackoverflow.com/u/3891623/ ) and on the answer https://stackoverflow.com/a/70834954/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Problem with locks while moving table to different schema in postgres

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Handling Locking Issues While Moving Tables to a Different Schema in PostgreSQL

Changing the schema of tables in PostgreSQL can seem like a daunting task, especially when you have ongoing queries that prevent you from obtaining the necessary exclusive lock. In this guide, we will explore the problem of locking when trying to alter a table's schema, and provide effective solutions to ensure a smooth transition.

The Problem: Long-Running Queries

When you attempt to change a table's schema in PostgreSQL using the ALTER TABLE command, the database requires an exclusive lock on that table. However, if there are long-running queries or transactions accessing that table, you will likely face issues in acquiring this lock. This can lead to frustration, as you’re left waiting or forced to cancel queries, which may result in new queries being created in the meantime.

Key Considerations

Schema changes in PostgreSQL require exclusive locks.

Long-running queries prevent exclusive locks from being obtained.

Cancelling existing queries could lead to new ones starting up shortly after.

The Solution: Step-by-Step Approach

To successfully change the schema of your tables without running into lock issues, here’s a structured approach you can follow. It involves using PostgreSQL system functions to manage blocking transactions effectively.

Step 1: Identify Your Backend Process ID

Before you start the ALTER TABLE statement, you need to know your current backend process ID. You can do this by executing the following SQL command:

[[See Video to Reveal this Text or Code Snippet]]

This will return a PID (Process ID) associated with your session, which you will use for further queries.

Step 2: Start the ALTER TABLE Statement

Next, initiate the schema change you desire with the ALTER TABLE command. For example:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Identify Blocking Transactions

Open a new database session and run the following command to identify any blocking transactions associated with your backend PID:

[[See Video to Reveal this Text or Code Snippet]]

Replace 12345 with the PID you noted in Step 1. This command will return a list of PIDs of transactions that are currently blocking your attempt to acquire a lock.

Step 4: Cancel Blocking Transactions

If there are blocking transactions, you can cancel them one by one using the following command:

[[See Video to Reveal this Text or Code Snippet]]

Replace 23456 with each blocking transaction PID obtained from the previous query. Repeat this for all identified blocking transactions.

Step 5: Confirm Success

Once the blocking transactions are successfully canceled, return to your original session and check if the ALTER TABLE command can now proceed without errors. If all goes well, your schema change should be applied with minimal interruption!

Conclusion

Changing the schema of tables in PostgreSQL when faced with long-running queries can be challenging. By following this step-by-step approach, you can manage blocking transactions, clear the way for your schema changes, and execute them effectively. Remember, this method helps mitigate risks associated with ongoing queries while ensuring your database remains efficient.

Feel free to share your thoughts or questions regarding schema changes in PostgreSQL in the comments below! Happy querying!