ads' corner

The one thing I like to do is challenge PostgreSQL - and readers - to the extreme. A while ago I posted about the Beer o'clock. Building Zoé was another interesting example. And at some point I blog about Dancing with the Stars as well. Today it's all about nothing. Empty. Where is all the data gone?

A while ago I posted this screenshot on Twitter, and as far as I can tell no one found the correct answer.

The screenshot looks like there's nothing. Empty names, schema name, role name, database name, even no data. And yet this all comes together and somehow works. Let's dig in.

A couple days ago Devrim forked PostgreSQL to get a beer.

That effort is of course laudable, but a bit extreme.

PostgreSQL is very extensible, and allows to implement a beer now clock with just a few easy steps.

Let's walk through this.

Your career as a hacker brings you more and more unreasonable tasks. Today you arrive at the ferry station, head to your next gate (no one mentioned if you even got an ice cream), and figure out where to seat. Even though you are the first person in the waiting area that does not stop you from knowing the seating habits for all other passengers, and calculate their seating patterns.

Task 1: There are some seats, and some floor tiles. Every seat is empty. Seating follows some rules: 1) if a seat is empty, and no adjacent seat is occupied, someone will absolutely sit there 2) if a seat is currently occupied, but four or more adjacent seats are also occupied, the person will instantly jump up, disconnect all charging devices, and find a new seat 3) no one sits on the floor 4) this will not occupy all seats - which seems to be a good thing in Corona-19 times. Repeat that pattern until the seats do not change between two repeats. Then count the number of occupied seats.

Task 2: Adjacent seats spawn across floor tiles, so it can be a couple steps in each of the 8 possible directions. Looks like people don't like to look at other people here. Oh, and now it's 5 or more seats which must be occupied, before someone jumps up and changes seat. Again repeat the pattern of people getting up and finding new seats all one by one, until everyone is satisfied with their current seats. And then hope no additional passenger arrives!

After you finished hacking the plane, your laptop dies: you forgot to charge it! Rookie mistake, or the calculations do consume a lot of power. The plane has a power outlet, but somehow it's a non-standard one, you need an adapter. But first a cocktail! Then you empty your backback on the seat next to you and find out that you have dozens of different adapters, which you can stack together. None of them really work, and hopefully you don't blow another fuse, or set the plane on fire!

The adapters you have can transform an imaginary Jolts value to another Jolts value, up to 3 Jolts up. And you find out that your laptop charger jumps 3 Jolts more than the highest rated adapter you have, although right now you don't know either of these value ... And since your laptop died, you need to calculate this on paper. Here is the list of 90 adapters in your backpack. Good luck!

Task 1: If you plug all 90 adapters together (Hopefully the plane is long enough, and the flight attendant does not spot what you are doing), what is the distribution of Joltage differences between the outlet (with 0 Jolts - seriously, no juice to begin with?), all adapters, and your +3 Jolts charger? Find a set that uses all adapters, calculate the Joltage differences, and multiply the +1 with the +3 differences. Seriously?

Task 2: Someone things that 90 adapters are not enough. Figure out in how many ways the 90 adapters can be arranged. On paper, because your laptop is still dead.

Uh oh, either the ice cream at the airport was bad, or the cocktails are not good for you! How else can you explain that you hack the airplane systems with some paperclips and break the XMAS encryption, while the plane is airborne? The plane's on-board systems emits some cryptic numbers and your task is it to decrypt this. Wait, did I say task? Who gave this task to you? Oh well, please be careful, if you shorten a circuit you might as well crash your own plane.

Task 1: 25 numbers, followed by more numbers. The first 25 are the initial set, in the following numbers if you add two of the last 25 numbers the result might be he current number. You have to find the first one which is not the sum of two of the previous 25 numbers.

Tast 2: Now that you found the first number which does not add up, find a contiguous set of two or more numbers which add up to the result of task 1.

You fixed the colours of the bags yesterday and security allowed you to leave the baggage section and go get your ice cream before you board your next flight. On the flight the kid next to you recognizes you as the great hacker you are, and asks you to fix a problem with the handheld game console. Go and hack the bootloader while no documentation is available because the mobile internet is off. Turns out someone coded an infinite loop into the bootloader, and missed that fact during testing. What testing, do you ask? Well, let's not jump too deep into details, you have to hack a bootloader and make a kid happy. It's Christmas soon, after all! And your cocktail is waiting!

Task 1: The assembler instructions are easy to read, to make the kid happy you just have to figure out the state of the accumulator before the bootloader repeats its set of instructions into another great infinite loop. Not sure how that fixes the problem, but ok.

Task 2: The instruction list is corrupt. But not in the way you think. The code is supposed to produce a buffer overrun, jump outside of the instruction list and stop. Modern operating systems would prevent this and abort the program, here that is "normal termination". Change exactly one "jmp" into a "nop" or vice versa, but do not change the operand. Whichever change aborts the program by jumping outside the code - the accumulator state is what we are looking for. Hopefully the number is high enough to pay for all the cocktails!

They really trust you a lot ... Now all flights are delayed, that's quite usual in advent times. Except it's 2020 and no one is traveling anyway. But they also impose more regulations, and now bags need to have colours, and bags need to go into other bags. And someone has to figure it out. That's right, it's you! I hope the stopover is long enough to at least grab some ice cream!

Task 1: There is a huge list of rules which colores bag can hold how many other colored bags. You have a "shiny golden" bag, but apparently you are not allowed to carry it around, it has to go into another bag. Find out which ones are allowed, and how many of them. Oh, and bags can go into bags, which means: recursion.

Task 2: As usual someone made a mistake. You are, after all, allowed to carry your "shiny golden" bag, but now you also have to have other bags in it. Calculate the possibilities, based on the rules. Yes, recursion again, and tree climbing.

And if your head is swirling: so is mine. Why on North Pole Earth does anyone even remotely care about your shiny golden bag?

The flight is approaching a regional airport, but you still have to fill out a customs form. Since you are good with hacking things together, you walk around the plane and gather all the answers from all groups on the plane. There are 26 yes-or-no questions, marked from a to z.

Task 1: Count how many questions are answeres "yes" in each group. Multiple "yes" answers for the same question do not count. The data is provided as blocks, kind of like two days before from the passport scanners. I can re-use some of the code - which will turn out to be a major headache!

Task 2: Of course someone wrote the wrong instructions down! It's up to you to fix the mess now. You have to find out how many groups have the same question answered by anyone in the group.

Pretty amazing the tasks they ask you to do "on the fly": Yesterday we fixed the passport scanner, today you have to write a piece of code which scans all the boarding passes in your environment (no one said they all carry them in the hand, and show it to you), and while waiting in the gangway you also have to identify your designated seat. Because, you know, you are such a good hacker you lost your boarding pass on the way from the gate to the plane ;-)

Task 1: Scan all the boarding passes with your phone camera, extract the code, and apply binary pattern matching to identify which seats exist. The airline has a pretty complex scheme going on, which depends on binary space partitioning. Find the highest seat number on the plane.

Task 2: Use the scanned data and find your seat. Seat belts sign is already on and you are still hacking in the gangway, hurry up! The only information you have: your seat exists, it's not the first and last one, and the seats next to yours (-1, +1) exist.

The challenge for day 4 is more suited for databases. The input is fuzzy, but with a CTE or two I should be able to clean it up. Solved the first part already with the kid earlier, but we decided to skip part two: mainly because it involved more complex string operations (best suited for regex operations), which is something the kid hasn't learned yet.

Task 1: The airport is busy, and the passport scanners do not function correctly. Wondering why every airport seems to be the same mess, but oh well. Anyway, being someone who can do some IT support they ask you to basically hack this thing on the fly and implement passport verification. If that would be possible on a real-world airport ... In addition to all the IT problems there, some people arrive with an ID which was issued by some "North Pole" organization which is not an official government body. But because everyone is waiting in line the people in charge decide to accept this ID as well. Only problem: this ID does not have a "Country ID" code. Your task, if you accept it: check the input data and if only the cid (Country ID) is missing, then accept this non-passport as valid document.

Task 2: Lines are getting longer and longer, and there are still problems with some passports. Your task is to apply additional validation on the fuzzy data which comes from the scanners. The task does not say what happens to people with invalid ID documents, but one can only assume that they cannot board the plane.

Have to admit, on first glance this challenge looks a bit complicated. It's well suited for languages which can do string manipulations, but it's not well suited for PostgreSQL. Earlier today I already looked at this problem together with the kid, in Python. Therefore I already knew that I have to jump multiple rows as well. To sum it up: adjust x, including overruns, jump multiple rows in y direction, count trees along the way. All in a single SQL query.

Decided to do the map search in a pl/pgSQL instead, and write a function for it.

Task 1: you get a map of "." and "#", where the "#" are trees. You get instructions to move a certain number of steps into x and y direction, and see if there is a tree. Then repeat until the end of the map. A detail problem is that the number of fields in x direction is smaller than the y direction. There are no clear instructions how to handle this, but the correct solution is to just start at the leftmost position again.

Task 2: Repeat the task from task 1, but 5 times with 5 different instructions for x and y movement. The resulting numbers are to be multiplied.

Day 2, another challenge: fix a broken password database. In order to do that, the passwords which violate the policy must be identified.

Task 1: I get a string, consisting of 2 numbers, a letter, and a password string. Check how many passwords have count(letter) which is between number 1 and number 2.

Task 2: The numbers are positions in the password string (beginning by 1, not 0). Exactly one of the two letters in the string must match the letter.

Started "Advent of Code" with the kid, the kid is polishing the Python (and English) skills. I thought I better do this in SQL.

The Task 1 for "Day 1" is: you get 200 numbers from the accounting department, find the two which in sum are 2020. This two numbers multiplied is the result of the task.

Task 2 is like task 1, except it's using three numbers. All three in sum will be 2020, and then multiply these three and that is the result.

Pavlo recently pointed out that the pg_sleep() function in PostgreSQL has two alternatives: pg_sleep_for() and pg_sleep_until().

What do they do?