CODEWITHSUNDEEP
postgresqlalter-table
Kevin V
Kevin V

Reputation: 159

Postgres add column on existing table takes very long

I have a table with 500k elements. Now I want to add a new column (type boolean, nullable = false) without a default value.

The query to do so is running like for ever.

I'm using PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit on my Windows 2012 Server

In pgAdmin I can see the query is blocked by PID 0. But when I execute this query, I can't see the query with pid = 0

SELECT *
FROM pg_stat_activity

Can someone help me here? Why is the query blocked and how can I fix this to add a new column to my table.

UPDATE attempt:

SELECT * 
FROM pg_prepared_xacts

Update

It works after rollback all prepared transaction.

ROLLBACK PREPARED 'gid goes here';

Upvotes: 2

Views: 2260

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248235

You have got stale prepared transactions. I say that as in "you have got the measles", because it is a disease for a database.

Such prepared transactions keep holding locks and block autovacuum progress, so they will bring your database to its knees if you don't take action. In addition, such transactions are persisted, so even a restart of the database won't get rid of them.

Remove them with

ROLLBACK PREPARED 'gid goes here';  /* use the transaction names shown in the view */

If you use prepared transactions, you need a distributed transaction manager. That is a piece of software that keeps track of all prepared transactions and their state and persists that information, so that no distributed transaction can become stale. Even if there is a crash, the distributed transaction manager will resolve in-doubt transactions in all involved databases.

If you don't have that, don't use prepared transactions. You now know why. Best is to set max_prepared_transactions to 0 in that case.

Upvotes: 4

Related Questions