MIME-Version: 1.0
In-Reply-To: <CAKFQuwZL8jWOX35fSRsMp2tjfGpqsnUgG=cCKuVBen5PC8Jddg@mail.gmail.com>
References: <CAD=mzVUGj2Zg0RY9YJwgDzAeghPjuT0FOnNs-aysTBGss87Yyw@mail.gmail.com>
 <f519a562-667c-4a96-9844-a2aaa42928b3@aklaver.com> <CAD=mzVUS8mFK42zmVUUC3JkiN=9QSUZ_Qxayd6=KJwbqdd0HzA@mail.gmail.com>
 <CANzqJaBAOzCjXaBFLa6cYweVNf5LbBvLYFkWx_hb3YezOP3quw@mail.gmail.com>
 <CAD=mzVXK+4tLMbENMecruPbk8cmeE4smD=ENUrwx5wh4N7hZhA@mail.gmail.com>
 <acc8b587497a65d38fafe4e0030e252eda78398f.camel@cybertec.at>
 <20240713220545.cgjghaggksov3xkt@hjp.at> <f70b1f5c575497a615c797588964d3c21823ef7e.camel@cybertec.at>
 <20240715142744.7e7a3wtdfxinochf@hjp.at> <CAD=mzVW9d0cPfccGVHLvCkcq4Mc-Wg5La3+XAHHEY1AMeOQ_Bw@mail.gmail.com>
 <20240716003731.m75sye7qq74wadhe@hjp.at> <CAD=mzVVTE1Jx0fpsQ=OVW0CL5LiGg_RHA4kq+cbPqTShA5EwVw@mail.gmail.com>
 <CAKFQuwZL8jWOX35fSRsMp2tjfGpqsnUgG=cCKuVBen5PC8Jddg@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Mon, 15 Jul 2024 21:56:23 -0700
Message-ID: <CAKFQuwaHzz-BYGn7N4AMZ8QWy072eqMC-WQRwUFNNuOQeE19oQ@mail.gmail.com>
Subject: Re: Dropping column from big table
To: sud <suds1434@gmail.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>, 
	"Peter J. Holzer" <hjp-pgsql@hjp.at>
Content-Type: multipart/alternative; boundary="000000000000e3bf5f061d562a1e"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwaHzz-BYGn7N4AMZ8QWy072eqMC-WQRwUFNNuOQeE19oQ%40mail.gmail.com>
Precedence: bulk

--000000000000e3bf5f061d562a1e
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Monday, July 15, 2024, David G. Johnston <david.g.johnston@gmail.com>
wrote:

> On Monday, July 15, 2024, sud <suds1434@gmail.com> wrote:
>
>>
>> However even with "vacuum full", the old rows will be removed completely
>> from the storage , but the new rows will always be there with the 'dropp=
ed'
>> column still existing under the hood along with the table storage,  with
>> just carrying "null" values in it. [=E2=80=A6] Is this understanding cor=
rect?
>>
>
> 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 catalog=
s in this way
the new ones must as well.  We just get the space back.

David J.

--000000000000e3bf5f061d562a1e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<br><br>On Monday, July 15, 2024, David G. Johnston &lt;<a href=3D"mailto:d=
avid.g.johnston@gmail.com">david.g.johnston@gmail.com</a>&gt; wrote:<br><bl=
ockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #=
ccc solid;padding-left:1ex">On Monday, July 15, 2024, sud &lt;<a href=3D"ma=
ilto:suds1434@gmail.com" target=3D"_blank">suds1434@gmail.com</a>&gt; wrote=
:<br><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-le=
ft:1px #ccc solid;padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><br><=
/div><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">Howev=
er even with &quot;vacuum full&quot;, the old rows will be removed complete=
ly from the storage , but the new rows will always be there with the &#39;d=
ropped&#39; column still existing under the hood along with the table stora=
ge,=C2=A0 with just carrying &quot;null&quot; values in it. [=E2=80=A6] Is =
this understanding correct?<br></div></div></div></blockquote><div><br></di=
v><div>No.=C2=A0 The table rewrite process involves creating new tuples tha=
t exactly conform to the current row specification.=C2=A0 The potentially n=
on-null data present in live tuples for columns that have been dropped are =
not copied into the newly constructed tuples.</div><div><br></div><div><a h=
ref=3D"https://github.com/postgres/postgres/blob/d2b74882cab84b9f4fdce0f2f3=
2e892ba9164f5c/src/backend/access/heap/heapam_handler.c#L2499" target=3D"_b=
lank">https://github.com/postgres/<wbr>postgres/blob/<wbr>d2b74882cab84b9f4=
fdce0f2f32e89<wbr>2ba9164f5c/src/backend/access/<wbr>heap/heapam_handler.c#=
L2499</a><br></div><div><br></div></blockquote><div><br></div><div>My bad, =
stopped at the code comment.=C2=A0 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 catalo=
gs in this way the new ones must as well.=C2=A0 We just get the space back.=
</div><div><br></div><div>David J.</div>

--000000000000e3bf5f061d562a1e--