Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sTaMy-000DA0-8g for pgsql-general@arkaria.postgresql.org; Tue, 16 Jul 2024 05:04:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sTaMv-00FRnk-8q for pgsql-general@arkaria.postgresql.org; Tue, 16 Jul 2024 05:04:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sTaMu-00FRnc-QQ for pgsql-general@lists.postgresql.org; Tue, 16 Jul 2024 05:04:52 +0000 Received: from mail-vs1-xe29.google.com ([2607:f8b0:4864:20::e29]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sTaMs-002MA6-8C for pgsql-general@lists.postgresql.org; Tue, 16 Jul 2024 05:04:52 +0000 Received: by mail-vs1-xe29.google.com with SMTP id ada2fe7eead31-48ff19d7074so2373306137.1 for ; Mon, 15 Jul 2024 22:04:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1721106288; x=1721711088; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Esm7D3UIVSOwQ1zbK97Xu4awa9opZ5FurS5BfGW6dNk=; b=O5wuwftX2gRNCNLlLiYBep4eh3G/YDmfSLy+358Xf4RCssvFB05N3QNHbZk/ACvOdK c01+HlRta4BZjDb1roxFJguphaIup7KP5JJoh2iq1KjiHozfjOK4EKBEf5Bm5zaLA5Ui hTPnPaaVkDdnT7bv8SjLsnOi/68Q58P5QiTuyCtccZTfPp3NIQLtKGaFk6Lsgf0velBq 9XlbH3+QillUwJRGYzoJ8XpQXa5XJSbWUd3yHdo319YUt7cCtl2pqPI5PqGXT8y4Fxrt rqEDOUVEi8iTJAhLa0okscMZYVFkOOA/yRqDlaOpd9DUUdttRhILRq35UtE+lvZQqtBr 5ckA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1721106288; x=1721711088; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Esm7D3UIVSOwQ1zbK97Xu4awa9opZ5FurS5BfGW6dNk=; b=TZavzYeVcKU8evO+6+O1Cyk85L5DDG0PuP7ETtrRitYReeO0fmqEMQTxknENEIwAT1 wNoaP1+1HtUdiz2Y6OnVx0AVHnYHeAY7fisT13mXIFIwHZa5R/Welxhuskwybt2KFCzz pIT74tlvWbl079xBg1gjFw6K4iWiJRX9ADJpTTaawVFUgBMFapPv3GsyQC8iBr5Gkw6t KGgETXepkWm/wBL8pT+sqqUcVjBP820bQVQrm+8pCLl4zQWJtE53/USAsYAtP9emjdyC 1zbKMgyFF3YnQG156yYjT78M4zlxrjbVz6ZDtHftWxBMblH/o3gqI2IrPRJwv1F2N7pK 0MPQ== X-Gm-Message-State: AOJu0YxcC5u7s65ZkdwAvS72rid67gscev/hmSSFB3NQcMAazD36Xwj5 ZzZPVy7qxP6Fh5aV44RNprhF/0uP5lVh5qER9HzaLPKazk1cHN0nuoBdI3bo+P6TU7GEa5jHVJI pPla62XJ79/UMHhyM5oa+XE+PW4k= X-Google-Smtp-Source: AGHT+IEXyiXRbBaSwXuz4zYxCGFpBibboe314n4V1ePgNp+7LP0ZkZMhRADgy3V57ECR9oNJAsiALX0bczQjkVnXbwo= X-Received: by 2002:a05:6102:801a:b0:48f:968b:1714 with SMTP id ada2fe7eead31-4914d6c00aemr290275137.11.1721106288217; Mon, 15 Jul 2024 22:04:48 -0700 (PDT) MIME-Version: 1.0 References: <20240713220545.cgjghaggksov3xkt@hjp.at> <20240715142744.7e7a3wtdfxinochf@hjp.at> <20240716003731.m75sye7qq74wadhe@hjp.at> In-Reply-To: From: sud Date: Tue, 16 Jul 2024 10:34:36 +0530 Message-ID: Subject: Re: Dropping column from big table To: "David G. Johnston" Cc: "pgsql-general@lists.postgresql.org" , "Peter J. Holzer" Content-Type: multipart/alternative; boundary="000000000000ee1a1e061d564815" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ee1a1e061d564815 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Jul 16, 2024 at 10:26=E2=80=AFAM David G. Johnston < david.g.johnston@gmail.com> wrote: > > > On Monday, July 15, 2024, David G. Johnston > wrote: > >> On Monday, July 15, 2024, sud wrote: >> >>> >>> However even with "vacuum full", the old rows will be removed completel= y >>> from the storage , but the new rows will always be there with the 'drop= ped' >>> column still existing under the hood along with the table storage, wit= h >>> just carrying "null" values in it. [=E2=80=A6] Is this understanding co= rrect? >>> >> >> No. The table rewrite process involves creating new tuples that exactly >> conform to the current row specification. The potentially non-null data >> present in live tuples for columns that have been dropped are not copied >> into the newly constructed tuples. >> >> >> https://github.com/postgres/postgres/blob/d2b74882cab84b9f4fdce0f2f32e89= 2ba9164f5c/src/backend/access/heap/heapam_handler.c#L2499 >> >> > My bad, stopped at the code comment. Apparently the data is just nulled, > not removed, the current row descriptor contains those columns with =E2= =80=9Cis > dropped=E2=80=9D and since this behavior doesn=E2=80=99t change the catal= ogs in this way > the new ones must as well. We just get the space back. > > > Thank you for the confirmation. And if someone wants to fully remove that column from the table , then the only option is to create a new table with an exact set of active columns and insert the data into that from the existing/old table and then rename it back to old. Is this correct understanding? --000000000000ee1a1e061d564815 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Tue, Jul 16, 2024 at 10:26=E2=80=AFAM = David G. Johnston <david.g= .johnston@gmail.com> wrote:


On Monday, July 15, 2024, David G. Johnston <= david.g.joh= nston@gmail.com> wrote:
On Monday, July 15, 2024, sud <suds1434@gmail.com> wrote:
How= ever even with "vacuum full", the old rows will be removed comple= tely from the storage , but the new rows will always be there with the '= ;dropped' column still existing under the hood along with the table sto= rage,=C2=A0 with just carrying "null" values in it. [=E2=80=A6] I= s this understanding correct?

No.=C2=A0 The table rewrite process involves creating new tuples t= hat exactly conform to the current row specification.=C2=A0 The potentially= non-null data present in live tuples for columns that have been dropped ar= e not copied into the newly constructed tuples.

https://github.com/postgres/postgres/blob/d2b74882cab84b9f4fdce0f2f= 32e892ba9164f5c/src/backend/access/heap/heapam_handler.c#L2499


My bad, stopped at the cod= e comment.=C2=A0 Apparently the data is just nulled, not removed, the curre= nt row descriptor contains those columns with =E2=80=9Cis dropped=E2=80=9D = and since this behavior doesn=E2=80=99t change the catalogs in this way the= new ones must as well.=C2=A0 We just get the space back.



Thank you for the confir= mation.=C2=A0
And if someone wants to fully remove that column fr= om the table , then the only option is to create a new table with an exact = set of active columns and insert the data into that from the existing/old t= able and then rename it back to old. Is this correct understanding?
--000000000000ee1a1e061d564815--