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
