 All right, so I'm going to talk to you about using P-SQL to watch Star Wars. Now P-SQL, that's the client, terminal-based client for Postgres, and Star Wars is a movie that I'm sure some of you like, and maybe some of you don't care for at all, to put me sort of in between those two extremes, I can tell you that I don't know the name of any of the bounty hunters except for Boba Fett, but I do know that Salacious Crumb is that gesture for Jabba, so you can sort of place me there. Real brief, my name's Will, I've done Postgres as a services for seven years now, first at Veroku and now at Citus. The slides here, and the code for the slides are up here on my GitHub, and the flight I came from San Francisco, and I only mentioned that because the flight was long enough that I decided to do it all as a program. You can see my commit messages are very useful, it makes up for my shaz. Okay, so the actual work here that you're gonna see is actually done over many, many years by this guy, Simon Jansen, who does AsciiMation.co.nz, and what it looks like is he actually has gone through most of Star Wars Episode 4 and turned it into, by hand, the sort of Ascii version, and so we're gonna put that into Postgres, you know, because of Postgres. So the first step is to get all that data, but unfortunately, you can't just curl it down from his site because it requires that you use compression because it's a lot of text data for the slides. That's no problem though, we can, we learned everything about compression in yesterday's talk, and so we can just say with curl, hey, give me something in Gzip, we can unzip it and then store it in site.html, and it is about two megabits of this guy who has gone through and done all this. Here's what it actually looks like. The first 30, 34 lines is just standard HTML and JavaScript, and then it gets a little crazy, and this line 34 is where most of that two megabytes is. It is just, he has this custom format where it's a number followed by 13 new lines followed by another number, and the number is how many times to repeat that frame, and this is sort of like a home-built compression thing, so he doesn't have to copy and paste the same frame if it's going to stay there for pretty long. And also my very advanced slide presentation software, I can do syntax settings there, so that's pretty nice. Okay, so we're going to put it in the Postgres, and the table here is not that complicated. We have a column called I, which is just an auto-incrementing thing to say what slide or what frame we're on, the number of times count to repeat that frame, and then the actual frame as new text. Okay, now we get to do an extract transform load job, so we're going to read that site into memory. We're going to find the one that happens to start with var film. Now, this is a little bit similar to the tofu not tofu problem from last, from yesterday, but a little bit different. There's something somewhere in that string that is just not parsable by, it's not UTF-8 compliant, and so we're just going to bulldoze through that and replace anything we don't understand with an empty string, because that's probably good enough. Also, since it was designed to be a JavaScript string, all of the single quotes are escaped, so we're going to go through and replace the escape character with a regular quote, and we're going to chop off the first part that says var film and the last part that was turning it into new lines, and we ourselves are going to split by new lines. And then we're going to put that into groups of 14, and so that way we have the first one in the group of 14 being how many times to repeat it, and then the other ones being the actual frame data. And now it's time to put it into Postgres. I really love the SQL libraries, we're going to use that. We're going to go through each of the things. The first one, again, is the count, and the rest of it is going to be the actual frame data. We're going to turn the count into an integer, and then we're going to rejoin the frames with new lines and then shove it into the database. And now we're almost there. There's still a couple more spots left. There's a lot of boilerplate here, don't worry about it, it's not that important, but basically we want to make a function inside Postgres that can sleep for the appropriate amount of time and then show the frame. And so Postgres comes with something called PG sleep, which does just that, it sleeps, and we can pass in like a speed thing, you know, some number between zero and one to say how fast we should sleep. And we want to look at the previous frame for how long to do it. And then we just look at our current, our current little counter there and show the frame associated with that. But how do we keep track of the counter? How do we know what frame that we're on? Postgres sequences. Now when you create a normal table that has an auto incrementing column, what Postgres is actually doing is creating a sequence and using that to keep track of the current number. But you can use a sequence for anything you want. It doesn't have to be attached to a table. For example, for a little while, the hit counter on my website, I used a Postgres sequence, and it was just a sequence by itself. And so we can create a new sequence here called counter, we can increment it, it goes up, it goes up, it goes up. You can set it to any number you want, and then it'll start counting from there. These are some of the things you can do with Postgres sequences. Okay, so are we done now? We have that go function that can show us a frame and wait for the appropriate amount of time. And we have our way to keep track of the frame. So we can say go, we can hit up and enter, say go again, up and enter, say go again, and we can slowly watch the movie. But there has to be a better way. And that is backslash watch. This is my favorite feature in Postgres. It was added in Postgres 9.3. It works like this. So we can see here this is the current time. I hit up and enter, that's the current time. But if I want to, you know, have it go, it can do backslash watch, and it'll just keep rerunning that last command over and over again. This is useful for a clock in case you don't have one on your computer. It's useful for, it's useful, you know, really it's useful if you're trying to watch something change, you can do a nice little select and have that go. I've also used it for inserting dummy data. Had something that inserted a bunch of rows, but just had that go over and over again with watch in case one of them erred out, the whole thing doesn't get erred out. But the reason I really like watch is because I think it might be one of the best contributions in the history of computer science. I've heard it argued. A little bit about Postgres. They don't use GitHub. They use an old style where you mail in patches to the mailing list. And see, this was added in 2013. And while Tom Lane was the person who committed the code, what's nice about Postgres is not only they list the authors at the end, but they also list anyone who happened to review the code because often reviewing Postgres features is a lot of work in and of itself. So, we have all the pieces. Let's watch some Star Wars. So I'm just going to turn off a couple things in Postgres that makes for a bad movie. We can select go, and here's a frame, here's a frame. Let's do watch with a short time out. And here we are, we're watching some Star Wars. This is not my favorite scene though, so we can advance a little bit and we can reset the counter to be, let's say, 2100 and watch and go. And we can see here, there's the Death Star. And so this is a perfect way, if you're tired of doing some work, you can pretend like you're doing some work and watch some Star Wars. And that is what I have for the talk here. Thank you very much. The code's there. And thank you.