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

Excerpts from tigran.mkrtchyan's message of jue ago 23 10:37:02 -0400 2012:
> The following bug has been logged on the website:
>
> Bug reference: 7502
> Logged by: Tigran Mkrtchyan
> Email address: tigran(dot)mkrtchyan(at)desy(dot)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.

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?

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