Thread: ALTER TABLE ADD COLUMN takes forever
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
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@gmail.com> 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 - 42Indexes - 15Foreign key constraint - 14Table reference - 11When 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 wellI 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
On Mon, 2021-03-29 at 22:38 +0530, Devendra Yadav wrote: > I am running a query on a table - ALTER TABLE mytable ADD COLUMN deposited DOUBLE PRECISION DEFAULT 0.0; > > this query just runs forever. Then either you are using a PostgreSQL version older than v11, or there is an open transaction that used the table and blocks you. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Hello Alex
Thanks for your response.
I have tried that way as well by removing the DEFAULT value and running just
ALTER TABLE mytable ADD COLUMN deposited DOUBLE PRECISION;
I get the same things. No luck
Regards,
Devendra Yadav
On Mon, 29 Mar 2021, 22:41 Alex Balashov, <abalashov@evaristesys.com> wrote:
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@gmail.com> 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 - 42Indexes - 15Foreign key constraint - 14Table reference - 11When 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 wellI 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
Hello Laurenz,
Thanks for your response
We are using PostgreSQL v12.1
There are no open transactions as the table is not blocked by any pid. However when i check pg_locks it say granted=false
but nothing blocking it. Bit strange!!
but nothing blocking it. Bit strange!!
Regards,
Devendra Yadav
On Mon, 29 Mar 2021, 22:44 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
On Mon, 2021-03-29 at 22:38 +0530, Devendra Yadav wrote:
> I am running a query on a table - ALTER TABLE mytable ADD COLUMN deposited DOUBLE PRECISION DEFAULT 0.0;
>
> this query just runs forever.
Then either you are using a PostgreSQL version older than v11, or there
is an open transaction that used the table and blocks you.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Mon, 2021-03-29 at 22:48 +0530, Devendra Yadav wrote: > > I am running a query on a table - ALTER TABLE mytable ADD COLUMN deposited DOUBLE PRECISION DEFAULT 0.0; > > > > > > this query just runs forever. > > > > Then either you are using a PostgreSQL version older than v11, or there > > is an open transaction that used the table and blocks you. > > We are using PostgreSQL v12.1 > There are no open transactions as the table is not blocked by any pid. However when i check pg_locks it say granted=false > but nothing blocking it. Bit strange!! As I replied in the other thread you started, you are probably blocked by a prepared transaction. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Laurenz,
You're a saviour.
Thanks a bunch.
This thread can be closed
Regards,
Devendra Yadav
On Tue, 30 Mar 2021, 16:42 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
On Mon, 2021-03-29 at 22:48 +0530, Devendra Yadav wrote:
> > I am running a query on a table - ALTER TABLE mytable ADD COLUMN deposited DOUBLE PRECISION DEFAULT 0.0;
> > >
> > > this query just runs forever.
> >
> > Then either you are using a PostgreSQL version older than v11, or there
> > is an open transaction that used the table and blocks you.
>
> We are using PostgreSQL v12.1
> There are no open transactions as the table is not blocked by any pid. However when i check pg_locks it say granted=false
> but nothing blocking it. Bit strange!!
As I replied in the other thread you started, you are probably blocked
by a prepared transaction.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com