PG Phriday: Irrelevant Inclinations
Say hi to Princess Kittybutt. She’ll be our mascot (and subject) for today. We’ll get to her in a minute.
When the only tool you have is a hammer, everything becomes a nail, right? With Postgres becoming more of an environment than simply a database engine, this colloquialism is starting to resemble reality. Of course, that’s not necessarily a bad thing! As Postgres accumulates copious and varied extensions, its role as an adaptive middleware solidifies. When Postgres can do something for itself, the need for sprawling harnesses of support scripts steadily decreases.
Yet there’s also the accusation that’s often levied at Emacs regarding its role as a UNIX kitchen sink. Extensions aid in preventing Postgres from descending into bloated madness. So for now at least, we’re safe to contemplate the tools available before they gain sentience and destroy humanity. For instance, can we build a game using only Postgres?
Not some contrived simulation where every interactive function calls a maintenance routine to advance the game world and respond to input. Can Postgres be the game engine, with an independently operating universe that churns along regardless of a player’s presence? Can we do that without a mature LISTEN / NOTIFY external resource that does all of the heavy lifting in some other language? Do we have all of the parts?
A new extension named pg_cron recently hit the web which makes this much easier. Postgres has had job scheduling for a while thanks to pgAgent, but it always ran as an external daemon which just happened to use Postgres tables to store schedules. Now we have something a bit more native that leverages the Postgres 9.3+ background worker functionality to integrate scheduling more directly. True to its name, Postgres now has a cron service.
Does it work?
CREATE EXTENSION cron;
CREATE TABLE cron_test (message TEXT);
SELECT cron.schedule(
'* * * * *',
$$INSERT INTO cron_test VALUES ('Hello World')$$
);
\x
SELECT * FROM cron.job;
-[ RECORD 1 ]------------------------------------------
jobid | 1
schedule | * * * * *
command | INSERT INTO cron_test VALUES ('Hello World')
nodename | localhost
nodeport | 5432
database | postgres
username | postgres
SELECT pg_sleep(60);
SELECT * FROM cron_test;
message
-------------
Hello World
Success! That job will insert “Hello World” every minute until we remove it. We won’t go through the installation process, as the README covers that well enough. However, we’d be extremely remiss if we neglected to thoroughly abuse it.
We came here to make a game, and that’s what we’re going to do! Well, a “game” anyway. We’re well aware that it’s possible to build a universe using mostly Postgres tools. We highly recommend checking out Schemaverse by the way, it’s a great way to learn Postgres features by example and practice.
Unfortunately, it would be impossible to cram all of that into a reasonable length article or ten. No, we’re going to give birth to a centralized virtual pet system. Pets exist when we aren’t around, need to be fed and played with, and might die if we neglect either for too long. Most importantly, it’s brain-numbingly easy to code. Perfect!
Let’s start with a schema.
CREATE TABLE db_pet (
pet_id SERIAL PRIMARY KEY,
player VARCHAR NOT NULL UNIQUE,
pet_name VARCHAR NOT NULL,
mood INT NOT NULL DEFAULT 24,
food INT NOT NULL DEFAULT 24,
is_dead BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE OR REPLACE VIEW my_pet AS
SELECT pet_name,
CASE WHEN food < 8 THEN 'STARVING'
WHEN food < 16 THEN 'HUNGRY'
ELSE 'FULL'
END AS appetite,
CASE WHEN mood < 8 THEN 'DEPRESSED'
WHEN mood < 16 THEN 'BORED'
ELSE 'HAPPY'
END AS spirits,
CASE WHEN is_dead THEN 'DEAD (you monster!)'
WHEN food < 5 OR mood < 5 THEN 'SICK'
WHEN food < 13 OR mood < 13 THEN 'OK'
ELSE 'GREAT'
END AS health
FROM db_pet
WHERE player = SESSION_USER;
GRANT SELECT ON my_pet TO PUBLIC;
Remember when we said this would be stupidly easy to code? We have one table to store the pets, and one view to display their current status to the owner. In this world, each clock tick is worth one hour. Our view suggest pets must be fed and trained three times a day or they’ll starve to death or die from boredom. Further, we slapped a unique index on the player so we can truly punish neglectful owners. One pet is all you get, and if it dies, too bad! Maybe a less cruel future version will allow players to have multiple pets and move the unique key to player and pet name. For now, let’s be awful sadists.
We spoke a bit about functions recently, and we’ll need a few to facilitate gameplay. In particular, we’ll need only three user functions:
- One to create and personalize the pet. We want some emotional investment, after all!
- One to feed the pet. This function should stave off starvation for 8 hours. That’ll give us roughly three feedings per day to keep it healthy.
- One to train the pet. Let’s assume the pet is more resilient to boredom than starvation and each training is worth 12 hours of entertainment.
Here’s how they might look:
CREATE OR REPLACE FUNCTION new_pet(new_name VARCHAR)
RETURNS SETOF my_pet
LANGUAGE plpgsql AS
$$
DECLARE
pet_state my_pet;
BEGIN
SELECT * INTO pet_state FROM my_pet;
-- If the user tries to create another pet, tell them no.
-- If their pet died, that's too darn bad!
BEGIN
INSERT INTO db_pet (player, pet_name)
VALUES (SESSION_USER, new_name);
EXCEPTION
WHEN unique_violation THEN
IF pet_state.health ~ 'DEAD' THEN
RAISE NOTICE 'Your pet is dead forever. Murderer.';
ELSE
RAISE NOTICE 'You can only ever have one pet!';
END IF;
END;
SELECT * INTO pet_state FROM my_pet;
RETURN NEXT pet_state;
END;
$$ SECURITY DEFINER;
CREATE OR REPLACE FUNCTION feed_pet()
RETURNS SETOF my_pet
LANGUAGE plpgsql AS
$$
DECLARE
pet_state my_pet;
BEGIN
-- Every feeding is good for 8 hours. Don't let the player
-- gorge their pet beyond one day of food.
UPDATE db_pet
SET food = CASE WHEN food >= 16 THEN 24 ELSE food + 8 END
WHERE player = SESSION_USER
AND NOT is_dead;
SELECT * INTO pet_state FROM my_pet;
RETURN NEXT pet_state;
END;
$$ SECURITY DEFINER;
CREATE OR REPLACE FUNCTION train_pet()
RETURNS SETOF my_pet
LANGUAGE plpgsql AS
$$
DECLARE
pet_state my_pet;
BEGIN
-- Playing with a pet will keep it entertained for another
-- 12 hours, up to a 24-hour limit.
UPDATE db_pet
SET mood = CASE WHEN mood >= 12 THEN 24 ELSE mood + 12 END
WHERE player = SESSION_USER
AND NOT is_dead;
SELECT * INTO pet_state FROM my_pet;
RETURN NEXT pet_state;
END;
$$ SECURITY DEFINER;
We created each function to always return the current status of the pet. Returning row sets is just too convenient not invoke, especially when interaction is a key part of game feedback. We also had to declare each of the functions as security definers so players could modify the state of their own pet without having access to the underlying table.
Beyond letting the user do things to their pet, we also need a game loop to push all the pets toward an untimely demise unless their owner intervenes. Then we need to actually schedule it with pg_cron to activate everything.
CREATE OR REPLACE FUNCTION pet_game_loop()
RETURNS VOID
LANGUAGE plpgsql AS
$$
BEGIN
UPDATE db_pet
SET food = food - 1,
mood = mood - 1
WHERE NOT is_dead;
UPDATE db_pet
SET is_dead = TRUE
WHERE (mood < 1 OR food < 1)
AND NOT is_dead;
END;
$$;
SELECT cron.schedule(
'* * * * *', 'SELECT pet_game_loop()'
);
Eagle-eyed cron users will immediately notice we set the game loop to run once per minute instead of once per hour. Hey, this is an article and we have a hard deadline, here. We can’t just wait several hours for Princess Kittybutt to diedemonstrate the game loop!
With that out of the way, let’s all welcome Princess Kittybutt!
SELECT * FROM new_pet('Princess Kittybutt');
pet_name | appetite | spirits | health
--------------------+----------+---------+--------
Princess Kittybutt | FULL | HAPPY | GREAT
Ah, the addition of a new pet to the family! What more could anyone ask for? Princess Kittybutt is happy, healthy, and content with us as owners. Truly, it’s stupendous to be Princess Kittybutt on this fine evening. So long as we play with her at least twice a day, and feed her three times, she will continue to live on in our database, successfully occupying many bytes and not doing much else.
Despite the uh… enhancement to the game timing, let’s check back on her in about ten minutes and see how things are going.
SELECT * FROM my_pet;
pet_name | appetite | spirits | health
--------------------+----------+---------+--------
Princess Kittybutt | HUNGRY | BORED | GREAT
SELECT * FROM feed_pet();
pet_name | appetite | spirits | health
--------------------+----------+---------+--------
Princess Kittybutt | FULL | BORED | GREAT
We noticed Princess was feeling a tad peckish, so we fed her a bit and everything is right as rain. She’s a little bored, but that’s hardly the end of the world, is it? Let’s check back in another ten minutes, shall we?
SELECT * FROM my_pet;
pet_name | appetite | spirits | health
--------------------+----------+-----------+--------
Princess Kittybutt | HUNGRY | DEPRESSED | SICK
Well, Princess Kittybutt is slightly under the weather, but we need to run a quick errand. That’s OK, right? We’re busy professionals after all, and to be honest, the life of a cat is pretty cushy. It’s not like she only has about five minutes to live or anything.
Back in five minutes! Promise!
SELECT * FROM my_pet;
pet_name | appetite | spirits | health
--------------------+----------+-----------+---------------------
Princess Kittybutt | STARVING | DEPRESSED | DEAD (you monster!)
W-what?! Princess Kittybutt! No! What have we done!? When the kids get home from school, they’re going to be utterly devastated! There has to be something we can do… Wait! We can just get another cat that looks just like her, right? Right? They’ll never know the difference.
Just a little bit of SQL and…
SELECT * FROM new_pet('Princess Kittybutt 2');
NOTICE: Your pet is dead forever. Murderer.
pet_name | appetite | spirits | health
--------------------+----------+-----------+---------------------
Princess Kittybutt | HUNGRY | DEPRESSED | DEAD (you monster!)
Why!? Poor Princess Kittybutt, you hardly had a chance to live. Oh, what cruel fate!
Anyway, what kind of practical use is an integral job scheduling system in Postgres? For one, few production databases have a 1-1 ratio between database users and OS users. As a consequence, it’s not always easy or convenient to schedule recurring events without using pgAgent or overloading OS user crontabs. Beyond that, do we really want decentralized scheduled events spread across multiple OS users anyway?
Of particular interest are data loading and manipulation operations. While most ETL systems have scheduling and transformation engines or suggest robust examples of such, sometimes it’s easier to frequently process new data internally. An active data logger or order processing system might have an army of fact tables and dimensions summarizing details at multiple levels of granularity. It’s not uncommon to keep these topped-up with functions that regularly aggregate everything to accelerate report generation.
Or of course, we could write a real game. Like Schemaverse, we might want multiple events to occur per clock tick. We can even create multiple game loop functions so each category of event follows a distinct schedule. It’s common for a game engine to assign independent jobs to available CPUs, giving the illusion of parallel activity. Well, that’s certainly a potential avenue here as well. As long as we don’t mind inhabiting a universe that only advances at a maximum rate of once per minute, that is.
That’s the only real downfall of pg_cron. Instead of being a generic scheduler, it married itself to cron’s limited syntax and granularity; it’s in the name, after all. For events that must occur more frequently than once per minute, we still need to rely on an external invocation routine of some description.
Considering how long cron has survived with that particular shortcoming, I doubt that will hold pg_cron back. It’s certainly a welcome addition to the Postgres extension family. Even though it murdered poor Princess Kittybutt.