When the locially dropped column is also physically dropped

"rupesh bajaj" <rupesh.bajaj@gmail.com> writes:

Hi,
I have dropped a column (say column name is 'A') from the relation R. By
setting the attisdropped as true in the pg_catalog.pg_attribute table. But
the column is dropped locially not the physically. Can you please tell me
when this column will be physically also dropped. Is this column is
automatically physically dropped? or I have to run some command to dropped
it physically.

These kinds of questions are best sent to pgsql-general instead of -hackers.

The column won't be dropped physically until you execute one of the commands
that rewrites the table entirely. "CLUSTER" and "ALTER TABLE ALTER column TYPE
type" are the two popular ones.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

Hi,
I have dropped a column (say column name is 'A') from the relation R. By
setting the attisdropped as true in the pg_catalog.pg_attribute table. But
the column is dropped locially not the physically. Can you please tell me
when this column will be physically also dropped. Is this column is
automatically physically dropped? or I have to run some command to dropped
it physically.

Thanks,
Rupesh Bajaj

On Apr 27, 2007, at 12:35 PM, Gregory Stark wrote:

I have dropped a column (say column name is 'A') from the relation
R. By
setting the attisdropped as true in the pg_catalog.pg_attribute
table. But
the column is dropped locially not the physically. Can you please
tell me
when this column will be physically also dropped. Is this column is
automatically physically dropped? or I have to run some command to
dropped
it physically.

These kinds of questions are best sent to pgsql-general instead of -
hackers.

The column won't be dropped physically until you execute one of the
commands
that rewrites the table entirely. "CLUSTER" and "ALTER TABLE ALTER
column TYPE
type" are the two popular ones.

UPDATE SET field1 = field1; would also work, right?
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Jim Nasby wrote:

On Apr 27, 2007, at 12:35 PM, Gregory Stark wrote:

The column won't be dropped physically until you execute one of the
commands
that rewrites the table entirely. "CLUSTER" and "ALTER TABLE ALTER
column TYPE
type" are the two popular ones.

UPDATE SET field1 = field1; would also work, right?

Well, yes, except that it will bloat the table. If you wanted to
physically drop the column to save space, you're not going to be happy
with that.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/27/07 07:01, rupesh bajaj wrote:

Hi,
I have dropped a column (say column name is 'A') from the relation R. By
setting the attisdropped as true in the pg_catalog.pg_attribute table. But
the column is dropped locially not the physically. Can you please tell me
when this column will be physically also dropped. Is this column is
automatically physically dropped? or I have to run some command to dropped
it physically.

Why didn't you drop the column the proper way?

- --
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGNiLVS9HxQb37XmcRAs/rAJ9KkXKlagXCe+RWnNd2824gZ0MTdQCgwHRy
uA2IsmiFw7WjrexvEHeY7w8=
=pqnT
-----END PGP SIGNATURE-----

rupesh.bajaj@gmail.com ("rupesh bajaj") writes:

Hi, I have dropped a column (say column name is 'A') from the
relation R. By setting the attisdropped as true in the
pg_catalog.pg_attribute table. But the column is dropped locially
not the physically. Can you please tell me when this column will be
physically also dropped. Is this column is automatically physically
dropped? or I have to run some command to dropped it physically.

Ron's comment is well-suggested[1]Ron Johnson, Jr suggested "Why didn't you drop the column the proper way?", but a bit of a red herring, as the
column will *NEVER* be "physically dropped." [2]When a column is dropped via ALTER TABLE DROP COLUMN, the effect in fact is much the same as what you did; the attribute is marked as dropped. -- let name="cbbrowne" and tld="linuxdatabases.info" in name ^ "@" ^ tld;; http://linuxdatabases.info/info/wp.html "MICROS~1: The People who Brought the Y2K Bug into Software Titling" -- cbbrowne@hex.net

What will happen is that new tuples will not have the column, but old
tuples will continue to have the (invisible) column for as long as
they "live" in the database.

Footnotes:

[1]: Ron Johnson, Jr suggested "Why didn't you drop the column the proper way?"
proper way?"

[2]: When a column is dropped via ALTER TABLE DROP COLUMN, the effect in fact is much the same as what you did; the attribute is marked as dropped. -- let name="cbbrowne" and tld="linuxdatabases.info" in name ^ "@" ^ tld;; http://linuxdatabases.info/info/wp.html "MICROS~1: The People who Brought the Y2K Bug into Software Titling" -- cbbrowne@hex.net
in fact is much the same as what you did; the attribute is marked as
dropped.
--
let name="cbbrowne" and tld="linuxdatabases.info" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/wp.html
"MICROS~1: The People who Brought the Y2K Bug into Software Titling"
-- cbbrowne@hex.net

Chris Browne <cbbrowne@acm.org> writes:

Ron's comment is well-suggested[1], but a bit of a red herring, as the
column will *NEVER* be "physically dropped." [2]

Check.

What will happen is that new tuples will not have the column, but old
tuples will continue to have the (invisible) column for as long as
they "live" in the database.

Actually, new tuples still have the column, it's just always NULL
(and hence takes no space except for a bit in the nulls-bitmap).

regards, tom lane