Skip to content

PGSQL Phriday #007 – Triggers for tracking changes in a table

This month's #PGSQLPhriday is hosted by Lætita Avrot, and she asks about triggers.

History time. Shortly after I started using PostgreSQL, I had a need to track changes in tables. Back then - this was the early version 7.x days - there was no such option available. I set out to write a tool for it. The logical choice to do that was to pick triggers to implement this. Today the world is different, PostgreSQL gained replication, and along with this, one can hook tools into the replication and stream all the changes. Back then there was no replication.

The second thing I discovered was that pl/pgSQL can't do the job. That was a rather big disappointment. My idea was to have one function which can be used with a trigger, the function figures out the columns and writes the changes into a separate table. However one can't access arbitrary column names in NEW and OLD in pl/pgSQL trigger functions. Something like this doesn't work:

columnname1 := "created_at";
columnname2 := "changed_at";

NEW.$columnname1 := OLD.$columnname1;
NEW.$columnname2 := OLD.$columnname2;

In pl/pgSQL, you have to "know" the column names in advance. Depesz recently posted some workarounds for this, but these options also have not been available back then.

Which made me write the tool in C. This at least allowed me to access the NEW and OLD values, and recoed changes. The tool is called "table_log", I also presented it at the first PGDay in Prato, Italy in 2007, and originally it was hosted on pgfoundry. This site is also long gone, I later copied the code to GitHub. But I also know that PostgreSQL 9.x had some internal changes which renders the tool non-working. However because the entire ecosystem had improved, and other tools are available, I did not update the old code anymore.

My conclusion: Triggers were one of the first "advanced" features I used in PostgreSQL, and I like them very much. They allowed me to implement an audit feature I need.

fwupdmgr: /usr/libexec/fwupd/efi/fwupdx64.efi and /usr/libexec/fwupd/efi/fwupdx64.efi.signed cannot be found

From time to time our laptops receive firmware updates, by using the Linux Vendor Firmware Service (short: fwupd). This worked fine for a long time, until it didn't. One day I was facing the following error message:

root@laptop:/root# fwupdmgr update

╔══════════════════════════════════════════════════════════════════════════════╗
║ Upgrade Embedded Controller from 0.1.23 to 0.1.25?                           ║
╠══════════════════════════════════════════════════════════════════════════════╣
║                                                                              ║
║ ...                                                                          ║
║                                                                              ║
╚══════════════════════════════════════════════════════════════════════════════╝

Perform operation? [Y|n]: 
Downloading…             [***************************************]
Decompressing…           [***************************************]
Authenticating…          [***************************************]
Waiting…                 [***************************************]
Waiting…                 [***************************************]
/usr/libexec/fwupd/efi/fwupdx64.efi and /usr/libexec/fwupd/efi/fwupdx64.efi.signed cannot be found

During the first few occasions I basically ignored the error message, and attributed it to a glitch in a software package. Maybe a later update will fix this.

But this never happened, so I looked into the issue.

Ubuntu split the 1.6.x version of fwupd into separate packages, and does not install the packages fwupd-signed and fwupd-unsigned to deal with EFI binaries.

apt-get install -y fwupd-signed fwupd-unsigned

Now everything is working again:

root@laptop:/root# fwupdmgr update
╔══════════════════════════════════════════════════════════════════════════════╗
║ Upgrade Embedded Controller from 0.1.23 to 0.1.25?                           ║
╠══════════════════════════════════════════════════════════════════════════════╣
║                                                                              ║
║ ...                                                                          ║
║                                                                              ║
╚══════════════════════════════════════════════════════════════════════════════╝

Perform operation? [Y|n]: 
Downloading…             [***************************************]
Downloading…             [***************************************]
Decompressing…           [***************************************]
Authenticating…          [***************************************]
Waiting…                 [***************************************]
Writing…                 [***************************************]
Waiting…                 [***************************************]
Successfully installed firmware
Do not turn off your computer or remove the AC adapter while the update is in progress.
Waiting…                 [***************************************]

Voila! Also updated the playbook which installs the laptops, to include the two new packages.