Refactoring a PostgreSQL database

22 September 2008 10:49

When 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.

Leave a comment