Changing a column type

The plan for today was not decided until after I got out of bed. I sat down with coffee and looked through the list of tasks. I found one which appealed: monitor the cache_clearing_ports & cache_clearing_dates tables

This is part one of a two-part story. The first part deals with fixing a database table before doing the planned work in part two, a Nagios check.

In this post:

  1. PostgreSQL 12.2
  2. Nagios 3.5
  3. FreeBSD 12.1

FreshPorts stores a lot of data in the database, but caches pages on disk. Specific events clear that cache, and the two tables listed will contain lists of items to remove from the cache.

Disk? Cache?

FreshPorts was built long before modern cache tools existed. We built our own. They work great for what is needed. The server uses ZFS and anything frequently used from disk is also cached in memory (see ZFS ARC). A page takes longer to load the first time it is built, but afterwards, it is fast enough.

The checking query

Sometimes the cache clearing lags. Sometimes there is a bug in the code. For whatever reasons behind the lag, I want to be notified if there are entries in this table which are more than 60 minutes old.

Here is the query I started with:

freshports.dev=# SELECT count(*) FROM cache_clearing_dates WHERE date_added > now() - interval '1 hour';
ERROR:  operator does not exist: time without time zone > timestamp with time zone
LINE 1: ...unt(*) FROM cache_clearing_dates WHERE date_added > now() - ...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

What? Why doesn’t what work? Look at the caret above? The comparison operator is the issue. I can’t compae those two values.

Which two values?

A time without time zone and a timestamp with time zone.

Where do I have a time field? Let’s check the table.

freshports.dev=# \d cache_clearing_dates
                                       Table "public.cache_clearing_dates"
    Column     |          Type          | Collation | Nullable |                     Default                      
---------------+------------------------+-----------+----------+--------------------------------------------------
 id            | integer                |           | not null | nextval('cache_clearing_dates_id_seq'::regclass)
 date_to_clear | date                   |           |          | 
 date_added    | time without time zone |           |          | now()

freshports.dev=# 

Oh. The date_added column should be a timestamp without time zone. This table was created incorrectly. I checked test, stage, and prod. All the same.

The cache_clearing_ports table is correct:

freshports.org=# \d public.cache_clearing_ports
                                        Table "public.cache_clearing_ports"
   Column   |            Type             | Collation | Nullable |                     Default                      
------------+-----------------------------+-----------+----------+--------------------------------------------------
 id         | integer                     |           | not null | nextval('cache_clearing_ports_id_seq'::regclass)
 port_id    | integer                     |           | not null | 
 category   | text                        |           | not null | 
 port       | text                        |           | not null | 
 date_added | timestamp without time zone |           | not null | now()
Indexes:
    "cache_clearing_ports_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "cache_clearing_ports_port_id_fkey" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE

Fixing cache_clearing_dates

This is the table structure I started with:

freshports.dev=# \d cache_clearing_dates
                                       Table "public.cache_clearing_dates"
    Column     |          Type          | Collation | Nullable |                     Default                      
---------------+------------------------+-----------+----------+--------------------------------------------------
 id            | integer                |           | not null | nextval('cache_clearing_dates_id_seq'::regclass)
 date_to_clear | date                   |           |          | 
 date_added    | time without time zone |           |          | now()

freshports.dev=#

Ummm, shouldn’t date_added be timestamp without time zone?

I’m sure that field does not have a date value in it. It should.

The tests

I wanted verification this was wrong. I was sure, but I wanted to be positively sure.

The better way to do this, and I thought of this after I started this blog post, is:

freshports.dev=# SELECT now()::time without time zone;
       now       
-----------------
 15:07:35.428659
(1 row)

freshports.dev=# 

To compose this test, take the Default value from line 7, and cast it use the Type on the same line.

What I really want is this:

freshports.dev=# SELECT now()::timestamp without time zone;
            now             
----------------------------
 2020-03-28 15:13:55.946128
(1 row)

freshports.dev=# 

Yes, that’s better. I want to know how long this entry has been in the table. A date is required for that.

Don’t try this at home

What did I try first? I don’t recommend this, but it worked for me.

freshports.dev=# INSERT INTO public.cache_clearing_dates (date_to_clear) VALUES ('2020-01-01');
INSERT 0 1
freshports.dev=# select * from cache_clearing_dates;
   id   | date_to_clear |   date_added    
--------+---------------+-----------------
 162763 | 2020-01-01    | 14:51:33.617565
(1 row)

freshports.dev=# rollback;

Yeah, that column has to change.

The change

PLEASE NOTE that in my specific case, this table has no data. There is no existing data to convert. I will show you one way to do that, but if you have data to convert, you may need to find answers elsewhere.

Here is a first attempt. Notice the transaction. If you have a lot of data in there, please be aware of how this might be blocking your applications. Mind you, if you have such a table, and applications, I would think you would already be aware of those issues.

First failure

This should be easy. ALTER TABLE…

freshports.dev=# ALTER TABLE cache_clearing_dates ALTER COLUMN date_added SET DATA TYPE timestamp without time zone;
ERROR:  column "date_added" cannot be cast automatically to type timestamp without time zone
HINT:  You might need to specify "USING date_added::timestamp without time zone".
freshports.dev=# rollback;
ROLLBACK

That’s new to me. Let’s read up on that a bit.. Oh yes, this is HOW PostgreSQL will convert your existing data.

Overwriting the column

THE FOLLOWING WILL LOSE DATA. What I am doing in the following command will set all rows to now() and existing values will be lost.

freshports.dev=# begin;
BEGIN
freshports.dev=# ALTER TABLE cache_clearing_dates ALTER COLUMN date_added SET DATA TYPE timestamp without time zone USING now();
ALTER TABLE
freshports.dev=# \d cache_clearing_dates
                                          Table "public.cache_clearing_dates"
    Column     |            Type             | Collation | Nullable |                     Default                      
---------------+-----------------------------+-----------+----------+--------------------------------------------------
 id            | integer                     |           | not null | nextval('cache_clearing_dates_id_seq'::regclass)
 date_to_clear | date                        |           |          | 
 date_added    | timestamp without time zone |           |          | now()

freshports.dev=# rollback;
ROLLBACK
freshports.dev=# 

Prepending a fixed date value

This attempt will prepend the current date to all values in the table.

freshports.dev=# begin;
BEGIN
freshports.dev=# -- put some test data into my table
freshports.dev=# INSERT INTO public.cache_clearing_dates (date_to_clear) VALUES ('2020-01-01');
INSERT 0 1
freshports.dev=# -- test out the prepend code
freshports.dev=# select now()::date + date_added from public.cache_clearing_dates;
          ?column?          
----------------------------
 2020-03-28 15:27:22.119605
(1 row)

freshports.dev=# -- alter the table
freshports.dev=# ALTER TABLE cache_clearing_dates ALTER COLUMN date_added SET DATA TYPE timestamp without time zone USING now()::date + date_added;
ALTER TABLE
freshports.dev=# SELECT * FROM public.cache_clearing_dates;
   id   | date_to_clear |         date_added         
--------+---------------+----------------------------
 162765 | 2020-01-01    | 2020-03-28 15:27:22.119605
(1 row)

freshports.dev=# -- undo my changes
freshports.dev=# rollback;
ROLLBACK

The change I used

This is the command I used to update my table:

freshports.dev=# ALTER TABLE cache_clearing_dates ALTER COLUMN date_added SET DATA TYPE timestamp without time zone USING now()::date + date_added;
ALTER TABLE

This is the test I used to verify I was getting what I wanted:

freshports.dev=# begin; insert into public.cache_clearing_dates (date_to_clear) values ('2020-01-01');
BEGIN
INSERT 0 1
freshports.dev=# SELECT * FROM public.cache_clearing_dates;
   id   | date_to_clear |         date_added         
--------+---------------+----------------------------
 162766 | 2020-01-01    | 2020-03-28 15:30:22.439249
(1 row)

freshports.dev=# rollback;
ROLLBACK
freshports.dev=# 

The queries for the Nagios check

The queries are straight forward. There should be zero entries more than 60 minutes old:

freshports.dev=# SELECT count(*) FROM cache_clearing_dates WHERE date_added > now() - interval '1 hour';
 count 
-------
     0
(1 row)

freshports.dev=# SELECT count(*) FROM cache_clearing_ports WHERE date_added > now() - interval '1 hour';
 count 
-------
     0
(1 row)

freshports.dev=# 

I combined those two into a script, which appears in part two of this post.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive

Leave a Comment

Scroll to Top