Search Results

You have a problem with transaction management. A session that waits for ClientRead is done processing the last query and waits for the client to send the next request. The only way that such a sessi …

Somebody must hold a lock on the table, which means that you have an open transaction. That is a bug; no transaction should ever stay open. To find out which sessions block your statement: Before you …

You can enable log_lock_waits to get information about sessions that have to wait more than deadlock_timeout for a lock. You can reduce deadlock_timeout to see shorter waits. Other than that, monitor …

Row locks are not permanently stored in the shared lock table, but on the row itself, so you cannot simply query for them. To figure out which rows in a table are locked by concurrent transactions, yo …

I looked at the source code (function ATRewriteTable in src/backend/commands/tablecmds.c), and PostgreSQL always uses a sequential scan of the table to verify NOT NULL constraints. So creating indexe …

Nobody is blocked by a lock. The insert has finished long ago, but the application forgot to commit the transaction. That is an application error, and you should start investigating there. Processing …

That is indeed not clear from the documentation, but a simple experiment shows that advisory locks are local to the database. That is, you can take the same advisory lock in two different databases wi …

One undocumented way of avoiding VACUUM truncation and the exclusive lock it requires is to set old_snapshot_threshold to a value other than -1. This in turn will get rid of the locks and the resultin …

The difference between a live lock (handled by lock_timeout) and a deadlock is that the latter is guaranteed never to end, because two (or more) sessions are waiting for each other. A live lock can a …

Explicitly locking tables is almost always the wrong thing to do. If you describe the use case in more detail, I am confident that a better solution can be found. … Locking tables frequently will keep autovacuum from doing its important work on the table, and eventually an anti-wraparound autovacuum job will come along and block you, bringing processing to a standstill …

The magic is called "row lock". The updates on the counter column are serialized by row locks, so there cannot be any race condition. A lost update can never happen if you read and write the data in a …

This is essentially for free, and you should enable it in all databases unless the workload is designed to have long lock waits regularly (which would be a design bug). These error messages are writt …

If you want to prevent concurrent statements from modifying the rows that the CTE selects before they get updated, you need to use SELECT ... FOR NO KEY UPDATE in the CTE.

Setting up a streaming replication standby server in PostgreSQL is much easier than that. Follow the documentation. MySQL is quite different from PostgreSQL when it comes to replication. A simple se …

A normal autovacuum process will succumb silently if it blocks another transaction, but if that happens all the time and autovacuum can never finish, you will eventually have old table rows that have …