Using PostgreSQL row commit IDs and live stats

Once again, it’s time to write down some notes for personal use. Let me know if what I’m jotting down here is particularly wrong.

PostgreSQL can tell you what are the commit IDs that touched a particular table. You query the system column named xmin.

prosody=> select xmin from prosody limit 5;
  xmin
--------
 212236
 770460
 770460
 967052
   1493
(5 rows)

You can also ask for the timestamp when this commit was created; however, that requires you to start tracking timestamps for each commit ID.

prosody=> select pg_xact_commit_timestamp(xmin), * from prosody;

ERROR:  could not get commit timestamp data
HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.

This seems to be rather useful for built-in tracking of the modification timestamp and for etags. If the database backend is well structured, it may be possible to structure queries in such a way to quickly check when the results were last modified, and help the web frontend avoid serving and requesting the results. I don’t have a clear way to do it yet, but while I don’t think impact on the database will be significant, it may help shave off some serving bytes or requests to other backends.

Commit IDs also roll over after 32bit, so their use on a high traffic site needs to be closely considered. Then again, by the time you have over four billion writes, your caches will probably otherwise expire anyway.

Table pg_stat_activity is interesting and lets you see the transactions and even queries in flight. This’ll be slightly messy, but click ‘view raw code’ to see the original formatting, and scroll around a bit.

$ sudo -u postgres psql
psql (10.1, server 9.6.10)
Type "help" for help.

postgres=# SELECT pid, query FROM pg_stat_activity;
 pid  |                                                                                                                                                                                                              query                                                                                                                                                            
------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 8968 | SELECT o0."id", o0."token", o0."refresh_token", o0."valid_until", o0."user_id", o0."app_id", o0."inserted_at", o0."updated_at" FROM "oauth_tokens" AS o0 WHERE (o0."token" = $1)
...
...
 9869 | SELECT pid, query FROM pg_stat_activity;
(12 rows)
postgres=# SELECT * FROM pg_stat_activity;
 datid  |   datname   | pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | wait_event_type | wait_event |        state        | backend_xid | backend_xmin |                                                                                                                                                                                                              query                       
--------+-------------+------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+------------+---------------------+-------------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 146762 | yyyyyyy_dev | 8968 |   146761 | yyyyyyy |                  | 127.0.0.1   |                 |       47545 | 2018-11-08 23:44:52.761448+00 |                               | 2018-11-08 23:50:33.742608+00 | 2018-11-08 23:50:40.75014+00  |                 |            | idle                |             |              | SELECT u0."id", u0."bio", u0."email", u0."name", u0."nickname", u0."password_hash", u0."following", u0."ap_id", u0."avatar", u0."local", u0."info", u0."follower_address", u0."last_refreshed_at", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = $1)
....
  16391 | prosody     | 8833 |    16386 | prosody  |                  | 10.0.AA.AAA |                 |       51490 | 2018-11-08 23:44:30.819644+00 | 2018-11-08 23:50:28.826344+00 | 2018-11-08 23:50:28.826344+00 | 2018-11-08 23:50:28.826362+00 |                 |            | idle in transaction |             |              | BEGIN
  12409 | postgres    | 9869 |       10 | postgres | psql             |             |                 |          -1 | 2018-11-08 23:48:29.262371+00 | 2018-11-08 23:50:41.487958+00 | 2018-11-08 23:50:41.487958+00 | 2018-11-08 23:50:41.487961+00 |                 |            | active              |             |      1606549 | SELECT * FROM pg_stat_activity;
(12 rows)

On Mastodon and the “fediverse”

I do like the freedom from lock-in. That’s why I run my own federated XMPP server (though a domain whitelist applies due to spam – contact me if you want to interop).

I do want to regain control over my social postings. I don’t use Facebook; I’m mainly on Twitter. I don’t mind Twitter as much, but it would be nice to host my own posts.

That’s why I will not be signing up for a Mastodon instance. Allegedly migration to another instance is easy. But permalinks to posts would still be stored on a domain owned by someone else. Thank you – but then I might as well stay on Twitter.

What about running my own? I have a test deployment I can spin up, but I don’t want to pay for the resources that would be required to make it a permanent thing. Mastodon’s minimum requirements are huge.

Why not GNU Social? I am trying to reduce my consumption of PHP.

Why not Pleroma? Maybe I’ll do that. I need to check it out, however, I’m not well versed in Erlang and fediverse stuff seems like something I may want to customize.

Customize in what way? Bridging to XMPPs microblogging seems like an interesting possibility. And sharing more than just “notes” (Twitter’s tweets, Mastodon’s toots) seems like a good way of weaning myself off of WordPress. ActivityStreams vocabulary (which makes an appearance in OStatus and is basis for ActivityPub) has more than just Notes.

Enabling zoom ‘key’ and spell key on Microsoft Natural® Ergonomic Keyboard 4000

The procedure is not best described elsewhere on the web. This article is a mess, too, but it works for me.

Keys need to be remapped to something under keycode 256 in order to work under X11.

  • Try using evtest and pressing keys to see what the keys map to right now.
    • evtest can will also tell you what are all the events supported by the device.
    • evtest will show two devices; you are interested in the second one (which exposes all the extended keys, such as new, reply, open, send, etc.
  • Use xev to see whether the keys are recognized, and as what are they recognized, in X11.

Now for the juicy part:

# put this into: /etc/udev/hwdb.d/61-keyboard-custom.hwdb

# then to update:
#  sudo udevadm hwdb --update && sudo udevadm control --reload-rules && sudo udevadm control --reload
# and:
#  sudo udevadm trigger
# or:
#  for i in /sys/class/input/* ; do if [[ -e "$i"/id/vendor ]] && [[ -e "$i"/id/product ]] && [[ "$(cat "$i"/id/vendor)" == 045e ]] && [[ "$(cat "$i"/id/product)" == 00db ]] ;  then echo $i ; echo change | sudo tee $i/uevent ; fi ; done

# Natural Keyboard 4000
# formerly:
#keyboard:usb:v045Ep00DB*
# now:
evdev:input:b0003v045Ep00DB*
 KEYBOARD_KEY_0c01ab=finance             # KEY_SPELLCHECK    to KEY_FINANCE
 KEYBOARD_KEY_c022d=up
 KEYBOARD_KEY_c022e=down

We’re naming it 61-keyboard-custom.hwdb in order to have it come after /lib/udev/hwdb.d/60-keyboard.hwdb.

Instead of finance, up and down keys, try taking something from this list: quirk-keymap-list.txt — however, I am not certain how to determine which ones are under 256 except by looking at evtest‘s output.

You can map to keycode 255 and use xmodmap -e "keycode 255 = XF86ZoomIn" to map to a ‘proper’ zoom in key.

On a related note: If you want to remap scancodes to keycodes, you can do it on the fly using setkeycodes(8)

Some sources:

Things XMPP can do that IRC can’t

This keeps popping up. I’m sure there’s a better article than this one. There’s a few things that I happen to value, even if I do use and like IRC as well.

So: what are some of the things XMPP can do that IRC can’t?

Widespread across all clients/servers:

  1. Per-person presence.
  2. Multi-line messages.
  3. Rich-text messages. 1 XEP-0071: XHTML-IM
  4. Avatars. XEP-0084: User Avatar, XEP-0153: vCard-based Avatars and more.
  5. Offline messages.
  6. Multiple concurrent logins with same credentials. Gets superpowers with XEP-0280: Message Carbons2 and XEP-0313: Message Archive Management.
  7. Cross-“domain”, nearly-trustless s2s federation.

Easy to set up server-side; sufficiently widely supported on mobile and desktop:

  1. Rich status updates. Particularly with extensions based on XEP-0163: Personal Eventing Protocol such as XEP-0118: User Tune or XEP-0080: User Location.
  2. Personal chat history.3 XEP-0313: Message Archive Management
  3. Chatroom history. 4 XEP-0313: Message Archive Management
  4. Chatroom backlog. Built into the multi-user chat specification.
  5. Per-chatroom nickname.
  6. Integrated HTTP file upload file sharing. Peer to peer with XEP-0234: Jingle File Transfer similar to IRC’s DCC, or, for a much better, offline-compatible and multi-client-friendly experience, XEP-0363: HTTP File Upload.
  7. Session resumption. XEP-0198: Stream Management.
  8. Standardized HTTP-based transports to support web-based clients. draft-ietf-xmpp-websocket-00, XEP-0124: BOSH, XEP-0206: XMPP over BOSH.

Rarely well supported on mobile, desktop or web:

  1. Microblogging. XEP-0277
  2. Integrated VVoIP. XEP-0166: Jingle-based XEP-0167: Jingle RTP Sessions.

A story

Now for a short use case description for something that XMPP allows me, and IRC doesn’t allow trivially…

As a side effect of some design choices in how per-protocol “gateways” operate, it’s trivial for a per-protocol “gateway” to also act as an equivalent of an IRC bouncer. For instance, I am logged into XMPP from multiple devices, and I have configured the gateway 5 to persist connection into some IRC channels. This means that I am effectively logged into IRC from multiple locations, and even if I lose the TCP connection from one of the clients (such as my phone), I won’t be kicked out. Plus Message Archive Management will let me obtain the backlog for the chatroom. Session resumption also means that, as far as the IRC component is aware, I will not have lost the connection anyway in most of the cases.

I am simply “available” in chatrooms, and particularly in IRC. And if I am not, I can catch up without referring to an external chatroom log service. Nor do I have to have a persistently-running IRC client in a tmux session, or an IRC bouncer; the IRC component connected to my self-hosted server is acting as a bouncer. If I were using a bouncer instead of a persistent client (enabling multiple connections and enabling logging), I would still easily lose some of the IRC history, as it would be trivial for me to see only the immediate backlog of ~20 messages.

Closing remarks

Are there other protocols offering openness, self-hosting, federation, presence, extensibility (which allows for a bunch of features quoted above)? Probably. None of them attracted me6. Despite low use of XMPP, other such protocols are used even less.

Can IRC be extended? Sure. How long before clients such as Irsii, Hexchat and mIRC are updated? If they are not, how long before they are pried from people’s hands? How long would it take for various IRC daemons to be updated? How long before daemon deployments are updated to support multiline messages?

Finally, if I am mistaken, and IRC has a capability I don’t know about or an upcoming protocol revision is supposed to provide a capability, leave a comment.

Updates

2018-02-27 16:30

The following were brought up on Twitter:


  1. Yes, technically, IRC has some support for message formatting in form of color, weight and slant control characters. 
  2. This enables realtime replication of messages aimed at one client onto other clients. 
  3. Stored server-side and retrievable incrementally. 
  4. Useful for catching up on offline discussions. 
  5. Despite occasional quirks, I highly recommend Biboumi as a featureful, yet sane and no-frills, IRC gateway component. 
  6. I had a very lengthy text here. tl;dr: XML’s namespacing eases extensibility and makes me tolerate XML’s verbosity. And HTTP-based protocols are good and acceptable, but also a sign of a modern-day “I have a hammer, every problem looks like a nail” attitude. 
  7. Apparently, there’s a way to use OTR with IRC using Pidgin.- 
  8. E2E interferes with my interest in keeping various devices in sync using MAM, without having to keep their encryption keys in sync. Instead I hope the system hosting my XMPP server is secure enough, and rarely use E2E. I perhaps shouldn’t, but I do. 
  9. Mobile push is important on platforms that don’t allow for long-running applications maintaining background connections out of energy concerns, and therefore require UI notifications to go through their systems. Notifications, out of necessity, have to go through client developer’s infrastructure, so they partially defeat the point of self-hosting. 

AppArmor on Ubuntu and MySQL in custom directories

AppArmor profile on Ubuntu is (rightly) restrictive and prevents the daemon binary mysqld from writing to unexpected locations.

So here’s another one in my series of ‘stupid notes to self’ — things that may help a reader, things that will help me, but things that are not proper or full guides to solving a problem.

Starting an already initialized datadir:

normal-user$ /usr/sbin/mysqld --defaults-file=/tmp/barproject-mysql-my.cnf
2018-01-09T21:24:28.090896Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2018-01-09T21:24:28.090907Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable

Initializing a new one:

normal-user$ /usr/sbin/mysqld --defaults-file=/tmp/barproject-mysql-my.cnf --datadir="$(pwd)/mysql" --log-level-verbosity=VERBOSE --initialize-insecure
mysqld: Can't create directory '/home/foo/projects/bar/_dev/mysql/' (Errcode: 13 -     Permission denied)
2016-10-10T16:23:29.515470Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-10-10T16:23:29.519420Z 0 [ERROR] Aborting

(Note that /tmp/barproject-mysql-my.cnf has been created from a template prior to running either of these. It specifies many values including datadir.)

First, I worked under the assumption the daemon was running under the wrong user or that the directory has wrong permissions. However, changing settings to any reasonable value did not get rid of errors with either initialization step or run step.

AppArmor has profiles that may block accesses atypical for the program executed. One such profile is for /usr/sbin/mysqld and is located in /etc/apparmor.d/usr.sbin.mysqld.

I tried symlinking it to /etc/apparmor.d/disable directory and restarting apparmor with systemctl restart apparmor. This didn’t change anything. I also tried whitelisting the directory by adding a local configuration change to /etc/apparmor.d/local/usr.sbin.mysqld:

/home/foo/projects/bar/_dev/mysql/** rwk,

systemctl restart apparmor — i.e. restarting through systemd — did not help.

However telling apparmor to tear itself down using the service command, as well as telling it to reload its profile cache, did:

service apparmor stop
service apparmor teardown
service apparmor recache
service apparmor start

There’s probably a smarter way, but this is good enough for me.