1

tl;dr

The following query blocks the postgres server when executed

ALTER TABLE "image_data" ADD COLUMN "user_status" varchar(30) NULL

engine version 10.6

# of rows 1.5 mils

Hi,

I have a somewhat odd problem with my production database server (I am unable to reproduce on my local server). As part of the deployment process I wanted to add a new column with a default value of null. When I ran the above query (which was produced by dajngo's migration process), It failed and in the process locked the database and blocked other queries (non-locking selects) from executing. Since it was a production environment, I rolled back after a few minutes (approximately 10). What I find most unusual is that this same query executes within less than a second on my local db replicate (which has the exact same data as the production).

Edit: When I run this transaction, it blocks the access to the database for all other select queries - which I think (please correct me if I'm wrong) implies that the database is in fact blocked by this transaction.

Improve this question

1 Answer 1

Reset to default
4

The problem is not your ALTER TABLE, but other transactions in the database.

You must have some long running transactions that hold any lock on the table (the ACCESS SHARE lock from a SELECT is sufficient to bloch the ACCESS EXCLUSIVE lock of an ALTER TABLE).

Your ALTER TABLE has to wait until this long running transactions are gone, and any later transactions that are trying to access the table have to queue behind the ALTER TABLE.

Close the long running transactions, and the statement will complete in a jiffy.

Improve this answer
5
  • Thanks! What if I cannot kill the running transactions? These are clients transactions and killing those could potentially cause them to experience data loss.
    – Superzarzar
    Aug 28, 2019 at 19:58
  • 1
    Then you cannot do it. Transactions that run forever are a serious threat to your database's health. They are usually an application bug. Investigate what that transaction is, and if you cannot figure it out, off with it's head. It will be rolled back, so no damage to the database can happen.
    – Laurenz Albe
    Aug 28, 2019 at 20:09
  • It is not that I have transactions that run forever (at least not that I think so), but rather that I have some incoming transactions that I want to be queued when I run the alter table transaction. Everything seems to be find until I executed this command after which the server stops responding to any query related to that table. Could we be dealing with a deadlock situation?
    – Superzarzar
    Aug 28, 2019 at 20:23
  • To identify what is blocking your ALTER TABLE command, execute that command and then from a seperate connection as the postgres user or a member of the pg_monitor role, run this query. It'll show the queries being that are blocking your ALTER command, from there you can determine what to do: select blockers.pid, blockers.usename, blockers.query_start, blockers.query from pg_stat_Activity blockers inner join (select pg_blocking_pids(pid) blocking_pids from pg_stat_tivity where pid != pg_backend_pid() and query like 'ALTER TABLE%') my_query on blockers.pid = ANY(my_query.blocking_pids);
    – Dan
    Aug 28, 2019 at 21:17
  • @Superzarzar "but rather that I have some incoming transactions that I want to be queued when I run the alter table transaction." And that is exactly what happened--they queued. Apparently you also have some different queries that you don't want to queue, but rather to jump the queue. There is no clean way to make that happen in PostgreSQL.
    – jjanes
    Aug 28, 2019 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.