Converting timestamp to timestamptz without rewriting tables

Hi all

We have generally been using timestamps without timezones in our system. As
both our app servers and db server were set to UTC it so far hasn't been an
issue. However, that may not always be the case, so we want to tighten
things up a bit. We are also needing to do things like get the particular
date that a timestamp in the db refers to, and thus need to be explicit
about which timezone everything is in.

All of the timestamps in our system represent an instant in time, not a
clock date/time, so timestamp with time zone is more appropriate. All of
the data that is currently on disk in timestamp columns was inserted in a
db session in UTC, and represents that timestamp in UTC.

We'd really like to convert all of our timestamps to timestamp with time
zones. We'd prefer not to have to rewrite all of our tables as a result
though.

My understanding from reading the docs is that a timestamp in UTC will have
the same on-disk representation as a timestamptz. This suggested simply
tweaking the system catalogs in some way. It looks like the preferred way
to do it would be creating a cast with WITHOUT FUNCTION between the two and
then doing an ALTER TABLE for each table affected, but a cast between the
two already exists.

I ended up trying out just tweaking the system catalogs directly, changing
the type associated with the table columns and the opclasses associated
with the related indexes.

UPDATE pg_attribute
SET atttypid = 'timestamp with time zone'::regtype
FROM pg_class
WHERE attrelid = pg_class.oid
AND relnamespace = current_schema()::regnamespace
AND atttypid = 'timestamp'::regtype
AND relname NOT IN ('some', 'excluded_tables');

-- These (3127, 3128) are hardcoded in pg sourcecode
UPDATE pg_index
SET indclass = array_to_string(array_replace(indclass::oid[], 3128::oid,
3127::oid), ' ')::oidvector
FROM pg_class
WHERE indrelid = pg_class.oid
AND relnamespace = current_schema()::regnamespace
AND indclass::oid[] @> ARRAY[3128::oid];

If we do this for real we can shut out other clients while it happens so we
don't need to worry about other backends having cached plans with the wrong
type etc.

It.. seems to work. Data is returned as expected and queries using the
indexes seem to work. I dropped and recreated functions and views that had
any reference to the affected columns.

Questions:

1. Is there a safer way to change these types without this hackery?
2. If not, is there anything else that I need to adjust?
3. Is there anything that could go wrong?

Many thanks

Tom