public inbox for [email protected]  

help / color / mirror / Atom feed

From: sud <[email protected]>
To: David G. Johnston <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Peter J. Holzer <[email protected]>
Subject: Re: Dropping column from big table
Date: Tue, 16 Jul 2024 10:34:36 +0530
Message-ID: <CAD=mzVW0A-TeX2Pce2N+7xQGy3hEi2xfUntH007MQ0TY=cVRkw@mail.gmail.com> (raw)
In-Reply-To: <CAKFQuwaHzz-BYGn7N4AMZ8QWy072eqMC-WQRwUFNNuOQeE19oQ@mail.gmail.com>
References: <CAD=mzVUGj2Zg0RY9YJwgDzAeghPjuT0FOnNs-aysTBGss87Yyw@mail.gmail.com>
	<[email protected]>
	<CAD=mzVUS8mFK42zmVUUC3JkiN=9QSUZ_Qxayd6=KJwbqdd0HzA@mail.gmail.com>
	<CANzqJaBAOzCjXaBFLa6cYweVNf5LbBvLYFkWx_hb3YezOP3quw@mail.gmail.com>
	<CAD=mzVXK+4tLMbENMecruPbk8cmeE4smD=ENUrwx5wh4N7hZhA@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAD=mzVW9d0cPfccGVHLvCkcq4Mc-Wg5La3+XAHHEY1AMeOQ_Bw@mail.gmail.com>
	<[email protected]>
	<CAD=mzVVTE1Jx0fpsQ=OVW0CL5LiGg_RHA4kq+cbPqTShA5EwVw@mail.gmail.com>
	<CAKFQuwZL8jWOX35fSRsMp2tjfGpqsnUgG=cCKuVBen5PC8Jddg@mail.gmail.com>
	<CAKFQuwaHzz-BYGn7N4AMZ8QWy072eqMC-WQRwUFNNuOQeE19oQ@mail.gmail.com>

On Tue, Jul 16, 2024 at 10:26 AM David G. Johnston <
[email protected]> wrote:

>
>
> On Monday, July 15, 2024, David G. Johnston <[email protected]>
> wrote:
>
>> On Monday, July 15, 2024, sud <[email protected]> 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 'dropped'
>>> column still existing under the hood along with the table storage,  with
>>> just carrying "null" values in it. […] Is this understanding correct?
>>>
>>
>> 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/d2b74882cab84b9f4fdce0f2f32e892ba9164f5c/src/backend/acces...
>>
>>
> My bad, stopped at the code comment.  Apparently the data is just nulled,
> not removed, the current row descriptor contains those columns with “is
> dropped” and since this behavior doesn’t change the catalogs 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?

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Dropping column from big table
  In-Reply-To: <CAD=mzVW0A-TeX2Pce2N+7xQGy3hEi2xfUntH007MQ0TY=cVRkw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox