Banning timestamp without time zone

Background

We process a lot of information around financial applications (e.g. getting a loan for your car) so it is important to establish when something happened. When the user logged, when the user uploaded a copy of their passport.

Date-time in software engineering is a notoriously finickity domain. It is easy to get wrong, and pretty hard to get right - unless you design for it from the beginning. This is further complicated by multiple ways to store date-times, and then on top of that there are many ways to display date-times not to mention in the application tier there are many ways to interact with date-times. It can be made harder still if you happen to be in GMT so an incorrect usage looks right for half of the year.

Wouldn’t it be desirable to remove at least one of these pain points? Remove the choice, and force the user down the path of success? Let’s have a stab at that. I am not going to cover why we want to prevent the use of timestamp without time zone that is already covered by the wiki.

Taking the human out of the process

Previous attempts to communicate why we should not be using timestamp without time zone only partially worked. This is down to several factors, people leave, people come, people forget, people copy and paste a previous migration and change what they need. In short, the component most likely to fail in the process is the human.

Sometimes the wrong date time type gets caught at code review; even then there is a slightly awkward moment where you have to have a mentor-like conversation and explain why it is bad, why we don’t want to use that type, and what type is better. Whilst having the conversation is not an issue; the fact the conversation is being had multiple times with different peoples means that existing efforts are not working.

Is it possible?

Before we can ban timestamp without time zone we first need to know if we can programmatically detect it. To grok out the problem, let’s create two tables;

CREATE TABLE public.indy_test_right
(    
    point_in_time timestamp with time zone
);

CREATE TABLE public.indy_test_wrong
(    
    point_in_time timestamp without time zone
);

Postgres has system tables that hold metadata about the tables within a database that we can query; a quick browse shows that pg_attribute might hold what we want:-

SELECT *
FROM pg_attribute
WHERE pg_attribute.attname = 'point_in_time';

Okay, so 1114 and 1184 are the ids we care about, unfortunately I did not have the foresight to give the columns unique names! No matter, a quick join to find the table name:-

SELECT pg_class.relname, pg_attribute.*   
FROM pg_attribute
INNER JOIN pg_class     ON pg_class.oid = pg_attribute.attrelid
                        AND pg_class.relname IN ('indy_test_right', 'indy_test_wrong')
WHERE pg_attribute.attname = 'point_in_time';

That confirms 1114 is type we want to ban. Just to be sure, we can check the source code of Postgres. Now that we have verified we can indeed interrogate the metadata, let us see if we can hook this into the migration framework.

Getting for free

We use DbUp for migrations so we will need to modify the migration passed in - it is important to note the person writing the migration should not need to do anything extra to take advantage of the protection. They should get it for free. A quick whizz round the documentation, and it looks like we can hook into the IScriptPreprocessor:-

public class PreventTimestampWithoutTimezone : IScriptPreprocessor
{
    public string Process(string contents)
    {
        var modified =
            $@"
            DO
            $do$
            BEGIN

            {contents}

            IF EXISTS
            (
                SELECT 1 FROM pg_attribute WHERE atttypid = 1114
            )
            THEN
                RAISE EXCEPTION 'your error message';
            END IF;

            END
            $do$
            ";

        return modified;
    }
}

Et voila! We have a slightly more detailed error message that links to our wiki that gives the person more information. But since we added this protection we have successfully stopped the incorrect date time type being used; we don’t need to educate, communicate, or re-educate on a one-to-one basis. The error message does that for us. This saves time at code reviews, save time on having the conversation and generally a guard rail like this is never going to fail. As always you can find a follow along example over on GitHub.

We have a few more checks like this;

1. enforce that every foreign key has an index
2. enforce the use of bigserial over serial
3. enforce that there can be no cross schema foreign keys

I’ll cover those in a bit more detail in future posts.