Ratio Ordering in Postgres

A while ago, I had an idea about how to use a materialised view to handle ordering and pagination. Today in #postgresql on IRC, there was a discussion about using ratio ordering. Which reminded me that I also did some work on that.

It’s possible to use both of these techniques together to allow easy access to ordinal position, and more importantly, fast pagination over large data sets. We can also make use of a few other new postgres features to make things even nicer.

CREATE TABLE condition (
  condition_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  name TEXT UNIQUE NOT NULL,
  position_a INTEGER NOT NULL,
  position_b INTEGER NOT NULL,
  ratio_position NUMERIC UNIQUE GENERATED ALWAYS AS (position_a::NUMERIC / position_b::NUMERIC) STORED
);

We can add some data to make sure we have correct behaviour on those generated columns:

INSERT INTO condition (name, position_a, position_b)
VALUES ('One', 1, 1),
       ('Two', 2, 1);

We can also try adding in values that should be prevented:

-- Different position_a/position_b, but position_a / position_b clashes.
INSERT INTO condition (name, position_a, position_b)
VALUES ('Three', 2, 2);

-- Not able to divide by zero.
INSERT INTO condition (name, position_a, position_b)
VALUES ('Three', 2, 0);

Okay, it would be really neat if by default we just inserted a new value at the end of the list. However, this would require us to be able to have a DEFAULT on a pair of columns. Perhaps if we were storing these as a custom type, with a custom sequence, we could do that.

Anyway, onward. We can insert values between two others by doing some simple mathematics:

INSERT INTO condition (name, position_a, position_b)
VALUES (
  'One-point-five',
  1 + 2,  -- The sum of the previous and next items' position_a values
  1 + 1   -- The sum of the previous and next items' position_b values
);

And, we can see that we now have the sortable by the correct field:

SELECT name FROM condition ORDER BY ratio_position;
name
One
One-point-five
Two

(3 rows)

So, what about getting the ordinal positions? We can do that using a row_number() window function, but that’s only useful if we are fetching all of them.

We can re-use the trick of creating a materialised view that contains all of them:

CREATE MATERIALIZED VIEW condition_position AS

SELECT condition_id,
       row_number() OVER () AS position
  FROM condition
  ORDER BY ratio_position;

CREATE UNIQUE INDEX condition_position_condition ON condition_position(condition_id);
CREATE UNIQUE INDEX condition_position_position ON condition_position(position);

And now we can use it:

SELECT *
  FROM condition
 INNER JOIN condition_position USING (condition_id)
 ORDER BY position;

We want this to recalculate whenever any positions change:

CREATE OR REPLACE FUNCTION refresh_positions()
RETURNS TRIGGER AS $$
BEGIN
  EXECUTE 'REFRESH MATERIALIZED VIEW ' || TG_ARGV[0];
  RETURN NEW;
END;
$$ LANGUAGE plpgsql STRICT;

CREATE TRIGGER condition_refresh_positions
AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
ON condition
FOR EACH STATEMENT
EXECUTE PROCEDURE refresh_positions('condition_position');

Now we can add more data:

INSERT INTO condition (name, position_a, position_b)
VALUES ('Three', 5, 1);

And fetch our data again:

SELECT *
  FROM condition
 INNER JOIN condition_position USING (condition_id)
 ORDER BY position;
condition_id name position_a position_b ratio_position position
1 One 1 1 1.00000000000000000000 1
2 Two 2 1 2.0000000000000000 2
5 One-point-five 3 2 1.5000000000000000 3
8 Three 5 1 5.0000000000000000 4

(4 rows)

Hacking the DETA rewireable plug

There are a bunch of inexpensive IoT devices coming onto the market. One of these ranges are the GridConnect devices available from Bunnings. I’ve been eyeing these off, because they include some fairly reasonable looking switches and plugs, and, most importantly, are legal to have installed in your home in Australia.

I’m not okay with sticking a bunch of devices into my home network that communicate with the outside world. I like to use Apple’s HomeKit, which means the communication is funnelled through their systems, and everything I have configured as a HomeKit accessory either has no cloud component, and is where possible isolated onto a seperate wireless network.

In fact, most of my devices are home built, and have limited accesibility: they are permitted to connect to an MQTT server and nothing else.

Most of the commercially available items in Australia fail this test: they all have their own little cloud service. I dislike this for a couple of reasons: the first is security and privacy. I don’t trust that these providers will protect my data.

The second is a little more subtle: if an IoT provider goes out of business (or decides to end-of-life some products), then you are no longer able to access them in the way you might have liked.

So, I’ve been creating my own devices, or re-purposing commercial devices where possible. I grabbed a couple of the Mirabella globes, and was able to flash them with an ESPHome firmware, that I believe hardens the device somewhat. Having controllable light globes is neat, but realistically, they are of limited use. Having to use an App to turn on and off your lights is unacceptable, but also so is having an always listening microphone. And, again, what happens when you want to turn your lights on and your internet is down?

So, I’m more interested in smart switches.

I was not able to find much information about the Deta line of products that support GridConnect, so I went and bought the cheapest one, that I thought I was going to be realistically able to reverse engineer.

Opening up the “user accessible” region, we see the three screws for attaching the cable, and some lovely 2.0mm triangular screws.

![IMG 2119][/images/2019/IMG_2119.jpg]

After spending more money on a set of screwdriver bits that had this size and shape, we are able to see the top of the PCB.

![IMG 2120][/images/2019/IMG_2120.jpg]

Note that there are two relays: this is safer than things like the Sonoff Basic, that only switch the active wire, rather than both.

The underneath shows clearly the separation between the different AC lines.

![IMG 2121][/images/2019/IMG_2121.jpg]

Finally, we can see the brains behind this switch:

![IMG 2122][/images/2019/IMG_2122.jpg]

Note the code on the microcontroller: TYWE2S. Plugging that into a search engine yielded some interesting results.

It turns out that not only are these using an ESP8285 (which is just an ESP8266 with onboard flash, and in a smaller package), but they are running the Tuya firmware. So, it wasn’t even necessary to have opened up the casing.

Since I had a Raspberry Pi Zero W configured to run tuya-convert, I built up a simple firmware that would enable me to run OTA updates after flashing, and settled down to working out which GPIO pins are attached to which parts: the button, LED and relay.

(Oh, and this was interesting: both the active and neutral lines are toggled by relays, as opposed to something like the Sonoff, that only toggles the active line).

At some point, possibly because I was tired, I flashed a firmware that accessed GPIO pins 6 and 7. This locks the device up, and prevents booting. So, I then soldered some wires onto the device (after opening it back up again: so it turned out I did need that fancy screwdriver bit) to get access to the UART.

Interestingly, the ESPHome firmware detects that it’s been unable to boot, and boots into a safe mode. So, again, in hindsight, I still didn’t need to open it up.

Through trial and error, I was able to determine which GPIO was the button. That’s the easiest (and was helpful for me to be able to determine the LED and relay).

Surprisingly, it was GPIO1. Those familiar with the ESP8266 may know this is normally the TX pin for the UART. Which explains why, when I had the serial port connected, I was seeing a bunch of weird things when I pushed the button.

Once I had that, it was a matter of trying each GPIO in turn to see which was the relay and which was the LED. I did them in pairs, and happened to choose GPIO13 and GPIO14 in the same test, so for a while I thought maybe the relay and the LED were hardwired together.

Anyway, I now have a working firmware for this device:

substitutions:
  device_name: deta_plug

wifi:
  # Hah!

esphome:
  name: $device_name
  platform: ESP8266
  board: esp01_1m

binary_sensor:
  - platform: status
    name: "Status"

  - platform: gpio
    pin:
      number: GPIO1
      inverted: true
      mode: INPUT_PULLUP
    name: GPIO1
    on_press:
      - switch.toggle: relay

switch:
  - platform: gpio
    id: led
    pin:
      number: GPIO13
      inverted: true
  - platform: gpio
    id: relay
    pin: GPIO14
    on_turn_on:
      - switch.turn_on: led
      - mqtt.publish:
          topic: HomeKit/${device_name}/Switch/On
          retain: ON
          payload: 1
    on_turn_off:
      - switch.turn_off: led
      - mqtt.publish:
         topic: HomeKit/${device_name}/Switch/On
         retain: ON
         payload: 0


sensor:
  - platform: wifi_signal
    name: "WiFi signal sensor"
    update_interval: 60s

ota:

logger:

mqtt:
  broker: "mqtt.lan"
  discovery: false
  topic_prefix: esphome/${device_name}
  on_message:
    - topic: HomeKit/${device_name}/Switch/On
      payload: "1"
      then:
        - switch.turn_on:
            id: relay
    - topic: HomeKit/${device_name}/Switch/On
      payload: "0"
      then:
        - switch.turn_off:
            id: relay

I’m using my MQTT ⟺ HomeKit bridge, since that works great, but you could easily change the MQTT topics, or do whatever else you want to do.

Update: turns out, I just needed to search for the code: this page has everything I would have needed.

Sonoff Basic unable to connect to Wifi on AC power

I have a few of the Sonoff Basic devices. I’d flashed one of them with a custom firmware before, but then was using the stock firmware, and having them operate in LAN mode.

However, that’s less than awesome: for one, it means I need to have (and maintain) a custom service running somewhere on my network, and because of the way you need to provision them, it’s hard to move this to a different machine.

With ESPhome, I’ve started re-flashing my old ESP8266 devices, so I had a go at some of the Sonoff. Neither of the ones I had installed (with the stock firwmare) had headers soldered onto the UART pins, but it turned out that one of my USB-UART devices had the pins in the correct order that I was able to (after disconnecting it from the mains, of course), hold the button down, press the USB-UART device into position, and then connect the USB port. After a couple of seconds, I released the button, and, eventually (after a couple of tries), have the device in “flash” mode.

Of course, I only discovered this after totally disconnecting one, soldering on some header pins and then connecting the device using hookup wires.

Then I uploaded the custom firmware. At which point I was able to re-upload firmware using the OTA mode, which means I no longer had to juggle the serial connector, USB and GPIO0 button.

So, I then flashed another one, this time without having to solder, and had both of them configured as devices on my IoT network.

But, there was a problem. They would only connect to the Wifi when they were connected to the serial connector. When I disconnected the serial port, and reconnected them to the mains supply, they would operate correctly, but would not connect to Wifi. Even though one of them was literally less than a metre from the router.

I did a bit of research, and there apparently are a batch that are like this. Tasmota firmware has issues that mention this on GitHub, but then in those issues they are marked as “resolved”. Unfortunately there was not really a good resolution - more than likely it was just some type of stale-bot just closing issues that had not been updated in a certain time frame. At least one of these suggested that a newer firmware worked, but that was no good for me.

A couple of these issues identified that there was a missing capacitor, but one of them mentioned that they had soldered together the 3v3 supply, and the RX and TX pins.

So, I ran some experiments. Connecting the 3v3 pin to either of those other pins had no effect, but connecting the RX and TX together suddenly allowed my device with the header pins to connect!

Luckily, these pins are adjacent, so I found a jumper (I remember when these were on just about every hard drive, but I couldn’t find one today with a single pin), and hooked it up.

All good!

Unfortunately, when putting it back together I didn’t align the top cover with the button correctly, so I managed to break that. Which just means I can’t control the switch directly, only remotely. That’s pretty annoying (it’s one of the reasons I went with Sonoff over some other solutions), but at least it works on HomeKit now.

Random, Fixed Ordering and Pagination

Consider the following situation:

We have a set of items that we want to show in a random order. The order, however, should remain fixed for a period. The display of items will need to be paginated, and we have over 200,000 items.

If the ordering is not truly random, then we could have an expression index, and allow ordering based on that. However, that doesn’t really help out with the pagination, and issues around LIMIT/OFFSET ordering of very large sets.

Instead, I came up with a solution this afternoon that uses Postgres Materialised Views.

Let’s start with a Django model:

class Post(models.Model):
    title = models.TextField()
    content = models.TextField()
    posted_at = models.DateTimeField()

We can build up a materialised view that associates each post with a position:

CREATE MATERIALISED VIEW post_ordering AS

SELECT post.id AS post_id,
       row_number() OVER () AS position
  FROM (
    SELECT id FROM blog_post ORDER BY random()
  ) post;

CREATE INDEX post_ordering_id ON post_ordering(post_id);
CREATE INDEX post_ordering_position ON post_ordering(position);

Because a materialised view stores a copy of the data, we need to index it if we want to get performance benefits.

This materialised view is interesting from the pagination perspective because there are no gaps in the position values, even if there are missing post_id values. This means we can use filtering to paginate, instead of having to use the regular slicing notation.

Do note that the ordering needs to be done inside a subquery, otherwise it will not work correctly.

We do need to stick a model in front of this to access it from Django:

class PostPosition(models.Model):
    post = models.OneToOneField(
        primary_key=True,
        related_name='ordering',
        on_delete=models.DO_NOTHING,
    )
    position = models.IntegerField()

    class Meta:
        managed = False
        db_table = 'post_ordering'

Now, because we have a relationship defined, we may filter using this:

page_2 = Post.objects.filter(
    ordering__position__gte=100,
    ordering__position__lt=200
).order_by('ordering__position')

From here, we just need to create a custom paginator to use this instead of the normal slicing.

class PositionPaginator(django.core.paginators.Paginator):
    def page(self, number):
        number = self.validate_number(number)
        bottom = (number - 1) * self.per_page
        top = bottom + self.per_page
        if top + self.orphans >= self.count:
            top = self.count
        return self._get_page(self.object_list.filter(
            ordering__position__gte=bottom,
            ordering__position__lt=top
        ).order_by('ordering__position'), self)

The last piece of the puzzle is getting the refresh of the ordering. In postgres, you just need:

REFRESH MATERIALISED VIEW post_ordering;