Smokes your problems, coughs fresh air.
Since my initial announcement here of pg_safer_settings, my extensions for safe (through per-column GRANTs), fast (through IMMUTABLE, auto-(re)generated accessor functions) settings has matured quite a bit, and is now at version 0.8.12.
Soon, later this month, still in the first quarter of 2024, I expect to release the first officially stable 1.0 version…
I just released pg_text_semver version 1.0.0 on PGXN. As usual, it’s also available for your forking pleasure.
Upgrading from 0.1.5 to 1.0.0 should be seamless. As usual, upgrade scripts are provided, though I didn’t need to according to the Semantic Versioning for pre-1.0 releases. 😉
Since my initial release, I’ve learned that, besides the pre-existing semver extension that I tried before developing pg_text_semver, there also existed a PG_SEMVER extension that I did not test and compare with my own solution. It might certainly be faster than my version, given that both these other extensions have their crucial parts written in C. (Performance was really not an issue for my use case – much less so in any case than completeness, correctness and ease of use.) See the Alternative to pg_text_semver section of the README for more details.
My computer sometimes doesn’t want to boot and/or shutdown anymore and gives the warning at boot time in plain text (not graphically): The ME FW of system was found abnormal. It happened last night and because I had this before, I still had a USB stick with firmwares on it from last time, it was quickly recovered. But, I did need to refresh my memory about which version to use. Last time I used the most recent version, and that caused problems. So, hence this blog post.
As a reminder to myself: the emergency firmware E7971IMS.B50 is located on two USB sticks: the one on my key chain and the ‘switch blade’ one on my desk.
Mainboard: MSI H170A PC MATE (MS-7971)
Somehow, while working on the ranking of “De Schuilplaats” in the shittified 2023 Google Search, between writing and peddling guest blog posts to spammy website with reasonable PageRank, I overlooked the fact that I could add a profile for a holiday home for free to a website operated by the province of Drenthe to promote tourism in the region. So here is our new profile on the Drenthe.nl website, that has an OpenPageRank of … 5/10:
https://www.drenthe.nl/verblijven-overnachten/vakantiehuisjes/3958391244/de-schuilplaats
The following is a copy of an guest post of mine that was published on Hoogsensitief.NL on June 19, 2023: Overgevoelig voor programmeren?
Programmeren lijkt bij uitstek een activiteit die geschikt is voor gevoelige mensen. Zolang je computer in een omgeving staat met prettige verlichting (het liefste daglicht) en zonder rumoerige collega’s of (veel erger!) achtergrondmuziek, zit je vaak in een bubbel waarin je zelf de controle hebt over binnenkomende prikkels. Klinkt ideaal toch?
What a mess… After postponing this for many months because I knew what awaited me, I double-upgraded an Ubuntu 18.04 server today, and aside from MySQL breaking in some spectacular ways (corrupt redo log for one), the Roundcube package was broken, primarily due to MySQL.
In MySQL 8, ‘system’ is now a reserved keyword; a change that in my opinion was quite risky, because many people use that word. This resulted in the Roundcube Debian migration scripts failing, because the word ‘system’ was used without back-ticks around it.
mysql said: ERROR 1064 (42000) at line 15: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system SET value='2018021600' WHERE name='roundcube-version'' at line 1.
These scripts are actually part of the .deb file, to be executed by dbconfig.
I reported the bug, but there was no interest in fixing it. Today I posted my work-around there too. Look there for the SQL files I used. I can’t upload them to this blog.
I had more problems, so I wanted to lay out what I did to fix it.
Ubuntu 20.04 installs this deb. In it are the SQL migration scripts in ‘usr/share/dbconfig-common/data/roundcube/upgrade/mysql’. I extacted it with ‘dpkg –extract’ and I took out ‘1.4.1-1’ and removed everything above the line that failed (because in MySQL the migration is not atomic…) and ran it like:
mysql roundcube < 1.4.1-1__starting_from_first_failed_statement.sql
Then on the next upgrade, I had to do the same with the deb for 1.5.
This one was more problematic, because the migration steps contained a change to case insensitive collation. This breaks if you have multiple users with different collation:
ERROR 1062 (23000) at line 33: Duplicate entry 'Info@example.nl-mail.example.nl' for key 'users.username'
I had to remove the users in question, from the table ‘users’ and ‘identities’, and continued running the script from the offending line (because again, DDL is not atomic in MySQL).
To be honest, I didn’t check if it cascaded foreign keys, like users’ address books and such… It was primarily ancient users, so I didn’t care.
A weird observation, is that the ‘system’ table is actually empty. That “SET value=’2018021600′” was a no-op. Now I wonder if I should add that row…?
The ‘system’ problem is likely to happen again on the next upgrade. At least I’m prepared now.
Early during the development of the PostgreSQL backend for FlashMQ.com—that is our amazing managed MQTT hosting service!—, I decided on using UUIDs rather than auto-incrementing integers wherever I needed or wanted surrogate keys. I am one of those people who prefers the use of natural keys where their use is … natural, but I certainly have nothing _against_ surrogate keys, only to their overuse, which usually results from an over-reliance on ORMs (which I do have something against). There are a couple of advantages to using UUIDs over auto-incrementing integers (available in PostgreSQL via sequences):
I needed to compare semantic versions within Postgres. I tried the existing semver extension, but when I found that it was a bit problematic to adjust to be fully compliant with the semantic versioning spec, I decided to write a simple domain-based alternative myself: pg_text_semver.
(Version parsing was part of my effort for more robust pg_restore of FlashMQ extension data. I might write more about that in the future.)
My newest PostgreSQL extension—pg_extra_time—contains the date-time functions, operators and casts that I was surprised to not find in the standard library. Maybe one day…
Again, this extension is a shoot-off from the PostgreSQL work I’m doing for the backend of the high-performant FlashMQ.com MQTT hosting service, that is due to launch later this quarter.
Like most of the FlashMQ.com PostgreSQL extensions, pg_extra_time is also available via PGXN—The PostgreSQL Extension Network: https://github.com/bigsmoke/pg_extra_time
Yesterday, I released a tiny PostgreSQL extension that I use to validate email address in the backend of FlashMQ.com: pg_html5_email_address validates email addresses in the same way that modern web browsers do for <input type="email"> elements.
The advantage of having email validation in your data backend to be in sync with the HTML5 spec is twofold:
pg_html5_email_address is available on PGXN: https://pgxn.org/dist/pg_html5_email_address/1.1.0/
© 2025 BigSmoke
Theme by Anders Noren — Up ↑
Recent Comments