pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedALTER TABLE ADD COLUMN takes forever
7+ messages / 3 participants
[nested] [flat]
* ALTER TABLE ADD COLUMN takes forever
@ 2021-03-29 17:08 Devendra Yadav <devendra.857@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Devendra Yadav @ 2021-03-29 17:08 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ALTER TABLE ADD COLUMN takes forever
@ 2021-03-29 17:11 Alex Balashov <abalashov@evaristesys.com>
parent: Devendra Yadav <devendra.857@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Alex Balashov @ 2021-03-29 17:11 UTC (permalink / raw)
To: Devendra Yadav <devendra.857@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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 - 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ALTER TABLE ADD COLUMN takes forever
@ 2021-03-29 17:14 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Devendra Yadav <devendra.857@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2021-03-29 17:14 UTC (permalink / raw)
To: Devendra Yadav <devendra.857@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ALTER TABLE ADD COLUMN takes forever
@ 2021-03-29 17:15 Devendra Yadav <devendra.857@gmail.com>
parent: Alex Balashov <abalashov@evaristesys.com>
0 siblings, 0 replies; 7+ messages in thread
From: Devendra Yadav @ 2021-03-29 17:15 UTC (permalink / raw)
To: Alex Balashov <abalashov@evaristesys.com>; +Cc: pgsql-admin@lists.postgresql.org
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 - 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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ALTER TABLE ADD COLUMN takes forever
@ 2021-03-29 17:18 Devendra Yadav <devendra.857@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 7+ messages in thread
From: Devendra Yadav @ 2021-03-29 17:18 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
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!!
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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ALTER TABLE ADD COLUMN takes forever
@ 2021-03-30 11:12 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Devendra Yadav <devendra.857@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2021-03-30 11:12 UTC (permalink / raw)
To: Devendra Yadav <devendra.857@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ALTER TABLE ADD COLUMN takes forever
@ 2021-03-30 11:47 Devendra Yadav <devendra.857@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 7+ messages in thread
From: Devendra Yadav @ 2021-03-30 11:47 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-03-30 11:47 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-29 17:08 ALTER TABLE ADD COLUMN takes forever Devendra Yadav <devendra.857@gmail.com>
2021-03-29 17:11 ` Alex Balashov <abalashov@evaristesys.com>
2021-03-29 17:15 ` Devendra Yadav <devendra.857@gmail.com>
2021-03-29 17:14 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-29 17:18 ` Devendra Yadav <devendra.857@gmail.com>
2021-03-30 11:12 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-30 11:47 ` Devendra Yadav <devendra.857@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox