Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lQvQp-0002Lr-SL for pgsql-admin@arkaria.postgresql.org; Mon, 29 Mar 2021 17:12:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lQvQo-0005G7-SD for pgsql-admin@arkaria.postgresql.org; Mon, 29 Mar 2021 17:12:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lQvQo-0005G0-K9 for pgsql-admin@lists.postgresql.org; Mon, 29 Mar 2021 17:12:02 +0000 Received: from evariste01.clarocloud.co.za ([196.13.181.165] helo=isando.evaristesys.co.za) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lQvQm-0005Rw-NI for pgsql-admin@lists.postgresql.org; Mon, 29 Mar 2021 17:12:02 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=evaristesys.com; s=defaultdkim; h=Sender:To:In-Reply-To:Cc:References: Message-Id:Date:Subject:Mime-Version:From:Content-Transfer-Encoding: Content-Type:Reply-To:Content-ID:Content-Description:Resent-Date:Resent-From: Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID:List-Id:List-Help: List-Unsubscribe:List-Subscribe:List-Post:List-Owner:List-Archive; bh=oZMq/T1+0kg4QXwVufi6VfQ02izVjhNJuanNd6wN0l8=; b=QEJW6pFXp7RGseFExh6lTyOgVU MGBt5WEZCU9U3ixWYH6YqmSe8CJ+2GWJ+tUHAEl3eXJDyJ1yokYBgAjiKegHEXIHmqKYUNcpNv0Nr sDpjPj2/uRxfQW4bAxSRwASPxh4FBXku1cp3MCske3ne4k9s1v/vRMO4GPR7TLCf49+U=; Received: from [172.58.156.238] (helo=[100.236.97.240]) by isando.evaristesys.co.za with esmtpsa (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lQvQh-0006xH-SR; Mon, 29 Mar 2021 19:11:55 +0200 Content-Type: multipart/alternative; boundary=Apple-Mail-13DBB020-5291-47BE-954B-9CB0984D74DE Content-Transfer-Encoding: 7bit From: Alex Balashov Mime-Version: 1.0 (1.0) Subject: Re: ALTER TABLE ADD COLUMN takes forever Date: Mon, 29 Mar 2021 13:11:48 -0400 Message-Id: References: Cc: pgsql-admin@lists.postgresql.org In-Reply-To: To: Devendra Yadav X-Mailer: iPhone Mail (18D70) Sender: sasha@isando.evaristesys.co.za List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-13DBB020-5291-47BE-954B-9CB0984D74DE Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable 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=E2=80=99t set= a default, it should be quite expedient, so you have to weigh that. =E2=80=94 Sent from mobile, with due apologies for brevity and errors. > On Mar 29, 2021, at 1:08 PM, Devendra Yadav wrote= : >=20 > =EF=BB=BF > Hello Experts, >=20 > I am running a query on a table - ALTER TABLE mytable ADD COLUMN deposited= DOUBLE PRECISION DEFAULT 0.0; >=20 > 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 >=20 > When i check for the query in pg_stat_activity the wait even is LOCK and w= ait_event_type is RELATION.=20 > Also when i check in pg_locks the granted status is 'f'. However there are= no pid's blocking this transaction. >=20 > This has just 500 dead tuples, so no problems there as well >=20 > I am a DBA and not good at development.=20 > Unable to understand what would be causing this? Could you guys help me wh= at should be my next approach or solutions on this . >=20 > Regards, > Devendra Yadav --Apple-Mail-13DBB020-5291-47BE-954B-9CB0984D74DE Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
I know your pain well. It is because y= ou set a default value for the column, so it has to initialise that value fo= r every role. If you don=E2=80=99t set a default, it should be quite expedie= nt, so you have to weigh that.

=E2=80=94
Sent f= rom mobile, with due apologies for brevity and errors.

On Mar 29, 2021, at 1:08 PM, Devendra Ya= dav <devendra.857@gmail.com> wrote:

=EF=BB=BF
Hello Experts,=

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

this query just runs forev= er. Few more details about this table -
Total row cou= nt - 300000 (a small table of 200MB)
Columns - 42
Indexes - 15
Foreign key constrai= nt - 14
Table reference - 11
<= br>
When i check for the query in pg_stat_activity th= e 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 prob= lems 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 shoul= d be my next approach or solutions on this .

Regards,
Devendra Yadav
= --Apple-Mail-13DBB020-5291-47BE-954B-9CB0984D74DE--