PG Phriday: Wanton Animal Cruelty
The last few PG Phriday articles have been somewhat dense content, so how about something a bit more irreverent? Rather than wax on about AI, HA architectures, or conceptual advancements to Postgres clusters, why not write a game instead? To keep things simple, let’s just build a no-frills Tamagotchi virtual pet for bored database professionals.
There’s a lot of SQL in this article, so check out the git page for this blog if you want to follow along.
Game Mechanics
Playing a game directly in Postgres can be done several ways, and we’re not the first to broach the topic. The Schemaverse lets users pit their SQL skills against each other for managing space fleets. Ours is… notably less ambitious. This is what we want:
- As a Tamagotchi, each user can only have one pet ever.
- If the pet dies, it’s permanently dead.
- Users can play with or feed their pet.
- Pets can only remain unwatched for a total of 24 hours before dying.
- Pets can not be transferred between owners.
It should be fairly simple to impose these rules, and also provide a few user-friendly SQL shortcuts at the same time.
Necessary Revisions
We’ve actually covered this topic before way back in 2016, and it resulted in the untimely demise of poor Princess Kittybutt. But we learned from our mistakes, and Postgres is bigger and better than ever. So what could we change this time around?
- Pets should have birthdays so we can determine their age.
- Why not allow direct manipulation of pet records via SQL?
- Can we simplify any of the syntax from before?
Additionally, we’ll need a new subject for our… experimentation. Meet Baron Von Fluffybottoms!
No, he does not look exactly like Princess Kittybutt; you’re just imagining things.
Base Design
Firstly, we want to use this as our new base table:
CREATE TABLE db_pet (
player VARCHAR NOT NULL PRIMARY KEY,
pet_name VARCHAR NOT NULL,
mood INT NOT NULL DEFAULT 24,
food INT NOT NULL DEFAULT 24,
is_dead BOOLEAN NOT NULL DEFAULT FALSE,
birth_date TIMESTAMPTZ NOT NULL DEFAULT now(),
death_date TIMESTAMPTZ NULL
);
GRANT SELECT, INSERT, UPDATE ON db_pet TO PUBLIC;
There are actually only three major differences here compared to the past version. First, we removed the unnecessary surrogate pet_id key. Each player can only ever have one pet, so it simply wasn’t necessary in the previous iteration. We also added a birth and death date so the pet can have an age, and we can record their demise.
Unlike last time, we want to allow SQL manipulation, but how can we do that? Row Level Security was introduced to Postgres with version 9.5 way back in 2016. It was a fairly new technique at the time, so we simply didn’t use it. This time around, we can be a bit more adventurous and set a policy where the currently logged in user can only interact with their own records.
Something like this:
ALTER TABLE db_pet ENABLE ROW LEVEL SECURITY;
CREATE POLICY only_pet_owner
ON db_pet
FOR ALL TO PUBLIC
USING (player = SESSION_USER);
While this won’t work for superusers, everyone else should only see their own pet. Let’s try it:
INSERT INTO db_pet (player, pet_name) VALUES (SESSION_USER, 'Fluffy');
CREATE USER bob_smith;
SET SESSION AUTHORIZATION bob_smith;
INSERT INTO db_pet (player, pet_name) VALUES (SESSION_USER, 'Snookums');
SELECT * FROM db_pet;
-[ RECORD 1 ]-----------------------------
player | bob_smith
pet_name | Snookums
mood | 12
food | 12
is_dead | f
birth_date | 2024-04-05 14:54:43.232449+00
death_date |
As we can see, Bob can only interact with Snookums, and doesn’t even know that Fluffy exists. But of course, this is still less than ideal because bob still had to supply his own user name, and there are no game mechanics.
That means we need triggers!
Stopping SQL Shenanigans
The first trigger we need is for incorporating new pets. We have the primary key to prevent users from having multiple pets, but the error there isn’t quite verbose enough. We also want to override certain values so the user doesn’t need to supply them. Something like this is a good start:
CREATE OR REPLACE FUNCTION check_pet_state()
RETURNS TRIGGER AS
$$
DECLARE
pet_dead BOOLEAN;
BEGIN
SELECT is_dead INTO pet_dead FROM db_pet;
IF pet_dead THEN
RAISE EXCEPTION 'Your ONLY pet is dead forever. Murderer!';
ELSEIF FOUND THEN
RAISE EXCEPTION 'You can only ever have one pet!';
END IF;
NEW.player = SESSION_USER;
NEW.birth_date = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER t_enforce_pet_state_b_i
BEFORE INSERT ON db_pet
FOR EACH ROW EXECUTE PROCEDURE check_pet_state();
With this trigger in place, we can explicitly tell users they can only have one pet, rather than the default primary key constraint violation. We can also be… rather forceful in admonishing their carelessness. Note how we set the player and birth date in a way the user can’t override as well.
Now it’s possible to do this:
INSERT INTO db_pet (pet_name) VALUES ('Fluffy');
SELECT * FROM db_pet;
-[ RECORD 1 ]---------------------------
player | bones
pet_name | Fluffy
mood | 24
food | 24
is_dead | f
birth_date | 2024-04-05 15:27:03.5018+00
death_date |
Great! Now we need some way to ensure the pet can’t be overfed, only remain entertained for a certain amount of time, and aren’t the subject of unsanctioned necromancy. This should work!
CREATE OR REPLACE FUNCTION check_pet_limits()
RETURNS TRIGGER AS
$$
BEGIN
IF OLD.is_dead THEN
RAISE EXCEPTION 'Your ONLY pet is dead forever. Murderer!';
END IF;
IF OLD.player != NEW.player THEN
RAISE EXCEPTION 'You must commit to your pet. No selling!';
END IF;
NEW.mood := least(24, NEW.mood);
NEW.food := least(24, NEW.food);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER t_enforce_pet_limits_b_ud
BEFORE UPDATE OR DELETE ON db_pet
FOR EACH ROW EXECUTE PROCEDURE check_pet_limits();
Nothing too complicated here. The great thing about the function we used is that it also works for deletes. We could have simply disallowed deleting dead pets, but that wouldn’t be any fun. Now we can explicitly chastise users who try to cover up their crimes!
Anyway, let’s try interacting with our pet:
UPDATE db_pet SET mood = 100, food = 100 WHERE player = 'bones';
SELECT * FROM db_pet;
-[ RECORD 1 ]-----------------------------
player | bones
pet_name | Fluffy
mood | 24
food | 24
is_dead | f
birth_date | 2024-04-05 15:34:45.773941+00
death_date |
Perfect!
Beginning the March
The final cog we need to complete the actual game engine is the game loop itself. The easiest way to accomplish this is to use pg_cron, which adds cron scheduling to Postgres. We merely need to define a function to advance the game loop, and call it.
Let’s start with the game loop function, which subtracts one hour from each of our health-tracking metrics, and… other sundries:
CREATE OR REPLACE FUNCTION pet_game_loop()
RETURNS VOID AS
$$
UPDATE db_pet
SET food = food - 1,
mood = mood - 1
WHERE NOT is_dead;
UPDATE db_pet
SET is_dead = TRUE,
death_date = now()
WHERE (mood < 1 OR food < 1)
AND NOT is_dead;
$$ LANGUAGE SQL;
Then we need to implement pg_cron. Make sure pg_cron is listed in shared_preload_libraries, and restart Postgres if necessary. Then:
CREATE EXTENSION pg_cron;
SELECT cron.schedule(
'game-loop', '1 second', 'SELECT pet_game_loop()'
);
This is actually an exciting misuse of a new feature pg_cron gained since our last article on this topic. While it won’t allow standard interval syntax, pg_cron does now support syntax for specifying a number of seconds. This makes it possible to schedule events with a higher granularity than once per minute.
Technically we could stop here, as the game is now fully functional, though mildly flawed due to our scheduling oversight.
User Friendliness
But that’s just the back-end. We’ve established that users can leverage SQL to directly manipulate their pet’s record, which is an interesting variant on the previous game. Still, let’s make some wrapper functions anyway for when they get lazy.
Let’s start with a view to decode our esoteric values:
CREATE OR REPLACE VIEW my_pet AS
SELECT pet_name,
CASE WHEN food = 0 THEN 'NONE!'
WHEN food < 8 THEN 'STARVING'
WHEN food < 16 THEN 'HUNGRY'
ELSE 'FULL'
END AS appetite,
CASE WHEN mood = 0 THEN 'NONE!'
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,
(COALESCE(death_date, now()) - birth_date)::INTERVAL(0) AS age
FROM db_pet
WHERE player = SESSION_USER;
GRANT SELECT ON my_pet TO PUBLIC;
This is essentially the same as the my_pet view in the previous version of the game, but now includes an age column that’s accurate down to the second. If only real pets could be traced so efficiently! We’ll use this view frequently.
Next, we need a function to create new pets:
CREATE OR REPLACE FUNCTION new_pet(p_name TEXT)
RETURNS SETOF my_pet AS
$$
INSERT INTO db_pet (pet_name) VALUES (p_name);
SELECT * FROM my_pet;
$$ LANGUAGE SQL;
Again, we depart from the previous technique. At the time, we created a plpgsql language function, but that’s not actually necessary. We’re not really using any advanced syntax, so any regular SQL is sufficient.
And we can continue with feed / entertain functions:
CREATE OR REPLACE FUNCTION feed_pet()
RETURNS SETOF my_pet AS
$$
UPDATE db_pet SET food = food + 8 WHERE player = SESSION_USER;
SELECT * FROM my_pet;
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION train_pet()
RETURNS SETOF my_pet AS
$$
UPDATE db_pet SET mood = mood + 12 WHERE player = SESSION_USER;
SELECT * FROM my_pet;
$$ LANGUAGE SQL;
As before, we’re just using SQL here. One statement to interact with the pet, and another to show its current state afterwards. These functions also existed in the last game we built, but they also imposed the game logic and restrictions. Now that we’ve moved all of that into the triggers, these functions are merely convenient statement wrappers.
And that’s it. That’s the whole game! Let’s play.
The Legacy of Baron Von Fluffybottoms
Let’s start with a new pet, a handsome Ragdoll we’ll give a suitably audacious moniker.
SELECT * FROM new_pet('Baron Von Fluffybottoms');
pet_name | appetite | spirits | health | age
-------------------------+----------+---------+--------+----------
Baron Von Fluffybottoms | FULL | HAPPY | GREAT | 00:00:00
Now we’ll go to another room for some coffee, and check back with him later.
SELECT * FROM my_pet;
pet_name | appetite | spirits | health | age
-------------------------+----------+---------+---------------------+----------
Baron Von Fluffybottoms | NONE! | NONE! | DEAD (you monster!) | 00:00:24
W… what?! No! Mr. Fluffybottoms! We hardly knew ye in your tragically short 24-second lifespan! What will we tell the kids? That’s OK, we have SQL access! We can fix him, make him stronger!
UPDATE db_pet SET is_dead = false WHERE pet_name = 'Baron Von Fluffybottoms';
ERROR: Your ONLY pet is dead forever. Murderer!
CONTEXT: PL/pgSQL function check_pet_limits() line 6 at RAISE
Hey! That’s uncalled for! How rude!
Final Thoughts
Despite the untimely passing of Baron Von Fluffybottoms, we’ve successfully demonstrated that not even a superuser can circumvent the game logic this time around. Previously it was possible to give a pet essentially infinite food and training, or raise them from the dead, simply by eschewing the API functions in favor of SQL. Now we have no such freedom. It’s the difference between imposing constraints within as a natural attribute of the database, or through system calls.
This is sometimes necessary when there are multiple potential vectors for data ingestion. Perhaps most new data is handled through a front-end API, and some is provided through a back-end batch processing system. Maybe there is a legacy system that’s being deprecated but still actively handling requests until it’s fully retired. Normally we would have to reproduce the game logic in each of these sources, or make very judicious and consistent use of reusable libraries.
But there’s one level you can’t circumvent. Some data integrity concerns can and should be imposed at the database level. Perhaps not to the extent shown here, but this should at least demonstrate what is possible.
Oh, and before we sign off for the week, let’s fix that bug so pets live a natural lifespan and can benefit from food and an owner’s love:
SELECT cron.schedule(
'game-loop', '0 * * * *', 'SELECT pet_game_loop()'
);
Now your users can have a fun little distraction between grueling rounds of writing CTEs, window functions, and optimization.