Disambiguating variables, or a “this” keyword, for plpgsql

Derry Hamilton - 07/11/2019

Some names, like account_number, or customer_id crop up in a lot of places, and when you’re juggling tables inside a plpgsql function, it can be a bit of a pain. Postgres allows you to disambiguate names by qualifying them with the function name, or the block name. CREATE FUNCTION app.a_long_function_name(id integer) RETURNS...

Why does Postgres call Rows Tuples?

Derry Hamilton - 21/11/2018

Postgres isn’t alone in referring to rows as tuples; it’s a term from mathematics, specifically it’s used in relational algebra which technically underpins the relational model. But there are other reasons… Postgres was (re)built from the ground up as an Object-Relational Database. So while the most common place people...

Changing Postgres Foreign Data Wrappers

Derry Hamilton - 28/09/2017

Migrating from a commercial server to Postgres meant that we needed to change the underlying connection type for the Foreign Data Wrappers. At this time, there’s no ALTER SERVER command that will do the job, but it’s possible to update the system tables instead. UPDATE pg_foreign_server SET srvfdw = (select oid...

In Search of the Simplest Oscillator

Derry Hamilton - 10/02/2014

Studying chemistry, one of the lightbulb moments was the realisation that lab skills weren't about learning how to use techniques, but instead about learning how to avoid stuffing things up, and rescue things once they were stuffed up. As the saying goes: "In theory, there's no difference between theory and practice. In practice, there's a...

Repairing a Watson FC-130

Derry Hamilton - 24/11/2011

There's a range of relatively inexpensive frequency counters, badge engineered by various companies; Watson, MFJ, Optoelectronics, etc. that appear to share a lot of common design. (I suspect that the only thing that changes is the front panel, and the switches installed.) At least, the Watson FC-130, and the Optoelectronics Scout 40 seem to share...

A Powerpole Busbar

Derry Hamilton - 19/11/2011

This is a brief howto, to create a basic PowerPole busbar, useful for connecting various bits of kit at temporary locations. It's fairly robust, but not environmentally sealed. It's also not fused, so you need to fuse the supply seperatly. Required parts: Plastic housings PCB connectors Single sided PCB Small box Potting compound The first step is to assemble...

mysqldump: Error 2013: Lost connection to MySQL server

Derry Hamilton - 19/11/2011

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table Trying to dump and reload a large (multi-hundred GB) database via a pipe, was failing with that error. mysqldump [stuff] | mysql [other stuff] Most of the pages on the internet fail to find any useful resolution, most suggesting that...

Table Auditing in SQL

Derry Hamilton - 19/11/2011

I've seen many weird and wacky ways of creating an audit trail in databases, some better than others. The basic requirements are that users are able to do their jobs, but aren't allowed to do anything they aren't supposed to. And that what they do is logged usefully, securely, and automatically. So, assuming that you...