Refactoring a PostgreSQL database
22 September 2008 10:49When investigating legacy systems, all sorts of interesting things can appear. Whilst it's easy to change code around a bit, historical warts in database structure can be a bit harder to change.
In this instance, a datetime was stored in the database as a string representation of the number of seconds since the epoch. Based on the approach given in "Refactoring Databases", I set up a new TIMESTAMP column and set up triggers to keep the two columns syncronised. Since I couldn't find any other examples of using triggers for column synchronisation in PostgreSQL, I thought I'd publish the working code, as it's a bit different to the Oracle version.
I started by creating functions to map between timestamps and strings. These definitions are in PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language), a procedural language supported by the PostgreSQL RDBMS. It closely resembles Oracle's PL/SQL language. Thanks, Wikipedia.
So, the function definitions:
CREATE OR REPLACE FUNCTION string_to_timestamp (dtstring text) RETURNS TIMESTAMP AS $$
BEGIN
IF dtstring = '' THEN
RETURN NULL;
END IF;
RETURN TIMESTAMP WITH TIME ZONE 'EPOCH' + to_number(dtstring, 9999999999) * INTERVAL '1 SECOND';
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION timestamp_to_string (dt timestamp) RETURNS text AS $$
BEGIN
IF dt IS NULL THEN
RETURN '';
END IF;
RETURN EXTRACT(EPOCH FROM dt);
END;
$$ LANGUAGE plpgsql;
Let's test these functions:
postgres=# SELECT timestamp_to_string(NULL) = '';
?column?
----------
t
(1 row)
postgres=# SELECT string_to_timestamp('') IS NULL;
?column?
----------
t
(1 row)
postgres=# SELECT string_to_timestamp('1222222222');
string_to_timestamp
---------------------
2008-09-24 12:10:22
(1 row)
postgres=# SELECT timestamp_to_string(TIMESTAMP '2008-9-24 12:10:22');
timestamp_to_string
---------------------
1222222222
(1 row)
Looks quite convincing, though we should assure ourselves that it handles timezones how we want it to.
Now we can add a new column, and update it to correlate with the existing column we want it to mirror:
ALTER TABLE spio ADD redtime TIMESTAMP;
UPDATE spio SET redtime=string_to_timestamp(RETT);
Finally, we define trigger functions and configure them as triggers on insert and update operations to keep the two columns synchronised:
CREATE OR REPLACE FUNCTION InitialiseRedemptionTime() RETURNS trigger AS $$
BEGIN
IF NEW.RETT IS NULL THEN
NEW.RETT := timestamp_to_string(NEW.REDTIME);
END IF;
IF NEW.REDTIME IS NULL THEN
NEW.REDTIME := string_to_timestamp(NEW.RETT);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER InitialiseRedemptionTime BEFORE INSERT ON spio
FOR EACH ROW EXECUTE PROCEDURE InitialiseRedemptionTime();
CREATE OR REPLACE FUNCTION SynchroniseRedemptionTime() RETURNS trigger AS $$
BEGIN
IF NOT(NEW.RETT=OLD.RETT) THEN
NEW.REDTIME := string_to_timestamp(NEW.RETT);
END IF;
IF NOT(NEW.REDTIME=OLD.REDTIME) THEN
NEW.RETT := timestamp_to_string(NEW.REDTIME);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER SynchroniseRedemptionTime BEFORE UPDATE ON spio
FOR EACH ROW EXECUTE PROCEDURE SynchroniseRedemptionTime();
So if we create a new record with RETT set:
postgres=# insert into spio(RETT) VALUES (1212121212); INSERT 0 1 postgres=# select uniq, RETT, redtime from spio order by uniq desc limit 1; uniq | rett | redtime -------+------------+--------------------- 17381 | 1212121212 | 2008-05-30 14:20:12 (1 row)
Or if we just set REDTIME:
postgres=# insert into spio(REDTIME) VALUES (TIMESTAMP '2008-05-30 14:20:12'); INSERT 0 1 postgres=# select uniq, RETT, redtime from spio order by uniq desc limit 1; uniq | rett | redtime -------+------------+--------------------- 17382 | 1212121212 | 2008-05-30 14:20:12 (1 row)
And if we update RETT:
postgres=# UPDATE spio SET RETT=1212312341 WHERE uniq=17382; UPDATE 1 postgres=# select uniq, RETT, redtime from spio order by uniq desc limit 1; uniq | rett | redtime -------+------------+--------------------- 17382 | 1212312341 | 2008-06-01 19:25:41 (1 row)
Or if we update REDTIME:
postgres=# UPDATE spio SET REDTIME=TIMESTAMP '2008-06-01 19:25:41' WHERE uniq=17381; UPDATE 1 postgres=# select uniq, RETT, redtime from spio order by uniq desc limit 1 OFFSET 1; uniq | rett | redtime -------+------------+--------------------- 17381 | 1212312341 | 2008-06-01 19:25:41 (1 row)
Of course, if you were doing this on an active database you'd need to set up the triggers before doing the one-time synchronisation.