Thread: BUG #7502: ALTER COLUMN TYPE processed even if column type matches

The following bug has been logged on the website:

Bug reference:      7502
Logged by:          Tigran Mkrtchyan
Email address:      tigran.mkrtchyan@desy.de
PostgreSQL version: 9.1.1
Operating system:   Linux
Description:        =


Hi,

we have notice that =


ALTER TABLE a_table ALTER COLUMN a_column TYPE varchar(36)

will be processed by postgres even if column types matches to the new one.
Technically, this is not an issue, except on big tables ( ~ 30M entries )
takes some time.

Tigran.

Excerpts from tigran.mkrtchyan's message of jue ago 23 10:37:02 -0400 201=
2:
> The following bug has been logged on the website:
>=20
> Bug reference:      7502
> Logged by:          Tigran Mkrtchyan
> Email address:      tigran.mkrtchyan@desy.de
> PostgreSQL version: 9.1.1
> Operating system:   Linux
> Description:       =20
>=20
> Hi,
>=20
> we have notice that=20
>=20
> ALTER TABLE a_table ALTER COLUMN a_column TYPE varchar(36)
>=20
> will be processed by postgres even if column types matches to the new o=
ne.
> Technically, this is not an issue, except on big tables ( ~ 30M entries=
 )
> takes some time.

This is a feature, at least for versions predating the rewrite of VACUUM
FULL.  The reason is that this is the only cheap way to cause the table
to be rewritten without causing it to be reordered (what CLUSTER does).
See http://wiki.postgresql.org/wiki/VACUUM_FULL

So the ALTER TABLE trick is not needed in 9.0, but is useful in previous
releases.  There are optimizations to avoid the table rewrite in certain
cases in 9.2:
http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2#Reduce_ALTER=
_TABLE_rewrites

According to that page, though, a change of varchar(36) to varchar(36)
would still require a table rewrite, which doesn't make sense to me.  Is
the wiki page inaccurate maybe?

--=20
=C3=81lvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> So the ALTER TABLE trick is not needed in 9.0, but is useful in previous
> releases.  There are optimizations to avoid the table rewrite in certain
> cases in 9.2:
> http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2#Reduce_ALTER_TABLE_rewrites

> According to that page, though, a change of varchar(36) to varchar(36)
> would still require a table rewrite, which doesn't make sense to me.  Is
> the wiki page inaccurate maybe?

Yeah - you can easily convince yourself by timing it that no rewrite
happens in 9.2.

            regards, tom lane