Re: ALTER TABLE ADD COLUMN takes forever

[Alex Balashov <abalashov@xxxxxxxxxxxxxxx>]

I know your pain well. It is because you set a default value for the column, so it has to initialise that value for every role. If you don’t set a default, it should be quite expedient, so you have to weigh that.

—

Sent from mobile, with due apologies for brevity and errors.

On Mar 29, 2021, at 1:08 PM, Devendra Yadav <devendra.857@xxxxxxxxx> wrote:



Hello Experts,

I am running a query on a table - ALTER TABLE mytable ADD COLUMN deposited DOUBLE PRECISION DEFAULT 0.0;

this query just runs forever. Few more details about this table -

Total row count - 300000 (a small table of 200MB)

Columns - 42

Indexes - 15

Foreign key constraint - 14

Table reference - 11

When i check for the query in pg_stat_activity the wait even is LOCK and wait_event_type is RELATION. 

Also when i check in pg_locks the granted status is 'f'. However there are no pid's blocking this transaction.

This has just 500 dead tuples, so no problems there as well

I am a DBA and not good at development. 

Unable to understand what would be causing this? Could you guys help me what should be my next approach or solutions on this .

Regards,

Devendra Yadav