\watch ing Star Wars in Postgres

Written by Will Leinweber
December 14, 2018

I recently had the honor of speaking at the last Keep Ruby Weird. A good part of the talk dealt with Postgres and since Citus Data is not only a database company but also a Postgres company, I figured sharing those parts on the Citus Data blog would be a good idea. If you'd like to see it in talk form, or you'd also like to know how to watch movies rendered as emojis in your terminal, I encourge you to watch the talk.

YouTube video still: Using psql to watch Star Wars

So, obviously we want to watch Star Wars in psql. The first step though is getting all of the frames into a format that makes sense. http://asciimation.co.nz by Simon Jansen is an ASCII version of Star Wars, and is perfect for our needs. Simon hand drew all of the frames and it looks like this:

                                        ====
          Help me,                      o o~~
      Obi-Wan Kenobi!                  _\O /_
                           ___        / \ /  \
                          /() \      //| |  |\\
                        _|_____|_   // | |  |//
            ,@         | | === | | //  | |  //
            /=-        |_|  O  |_|('   |===(|
            ||          ||  O  ||      | || |
            ||          ||__*__||      (_)(_)
          --~~---      |~ \___/ ~|     |_||_|
          |     |      /=\ /=\ /=\     |_||_|
 _________|_____|______[_]_[_]_[_]____/__][__\______________________

We can get all of the data using curl as long as we get it using gzip, and then we can extract the frames out of the single, very long javacript line that starts with var film =. The format is a number representing how many times this particular frame should be repeated and is followed by fourteen lines separated by new line characters.

raw = File.readlines("site.html")
  .find {|line| line.start_with? "var film"}
  .encode('UTF-8','binary',invalid: :replace,undef: :replace,replace: '')
  .gsub(%q{\'},"'")[12..-16]
  .split('\n')
  .each_slice(14)

Once we have that we can store it in a postgres table

CREATE TABLE film (
  i     serial PRIMARY KEY,
  count int    NOT NULL,
  frame text   NOT NULL
);
require 'sequel'
db = Sequel.connect("postgres:///")

raw.each do |data|
  count, *frame = data
  db[:film] << {count: count.to_i, frame: frame.join("\n")}
end

To watch this, we're going to want some way that will show the frame then sleep for the appropriate amount of time. Postgres functions are perfect for this. This one will actually sleep for the duration requried for the previous frame, then display the current frame.

CREATE OR REPLACE FUNCTION go(speed numeric, ctr bigint)
RETURNS text AS $$
  begin
    PERFORM pg_sleep(count*speed) FROM film WHERE i=ctr-1; -- 😴
    RETURN (SELECT frame FROM film WHERE i=ctr);           -- 👀
  end
$$ LANGUAGE plpgsql;

We're almost have all the pieces together, but we need a way to keep track of the current frame. If you create a table with an auto-incrementing column, behind the scenes, Postgres creates a sequence to keep track of the current value. You can create unattached sequences however, and control where they start.

CREATE SEQUENCE ctr;
SELECT nextval('ctr'); -- 1
SELECT nextval('ctr'); -- 2
SELECT nextval('ctr'); -- 3

ALTER SEQUENCE ctr RESTART 200;
SELECT nextval('ctr'); -- 200
SELECT nextval('ctr'); -- 201

And with that, SELECT go(0.01, nextval('ctr')); can be run over and over again to watch Star Wars inside psql. But it's awfully annoying to have to hit up and enter over and over again, ins't it? Well let me introduce you to the best feature in all of Postgres, and possibally the history of computer science. \watch was added in 9.3. Much like it's Unix counterpart, it reruns the previous query for you over and over and over again. So if we run the select go command from earlier and add \watch 0.01 to the end, psql will show us the movie as a movie! Run it for yourself, or check out the video earlier to see what it looks like.

Will Leinweber

Written by Will Leinweber

Former principal cloud engineer at Citus Data & Microsoft. Loves challenging database problems. Core Heroku Postgres team. Speaker at PGConf.EU, RailsConf, PostgresConf US, Keep Ruby Weird, & several PgDays. Music lover.