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:
- PostgreSQL 12.2
- Nagios 3.5
- 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.
