Month: June 2022
Transaction anomalies with SELECT FOR UPDATE
I was recently confronted with a nice example of how adding FOR UPDATE to a query can introduce transaction anomalies. This article will explain how that happens and how you can cope with the problem. Be ready to learn some PostgreSQL implementation details! The example Setting the stage We have a table of bills […]
Debugging deadlocks in PostgreSQL
(Updated 2023-04-07) Even if you understand what a deadlock is, debugging deadlocks can be tricky. This article shows some techniques on how to figure out the cause of a deadlock. A simple deadlock example Setting the stage We will test our techniques for debugging deadlocks with the following example: The deadlock To provoke the […]
Case-insensitive pattern matching in PostgreSQL
Case-insensitive search is a much-requested feature, partly (I suspect) to maintain compatibility with Microsoft SQL Server. There are several solutions to the problem, one of which is to use case-insensitive ICU collations. This works like a charm, except if you want to perform pattern matching. So let’s have a closer look at the […]
Migrate scheduled jobs to pg_timetable from pg_cron
Why migrate to pg_timetable from pg_cron? There are a lot of reasons you may want to migrate from pg_cron: maybe you need to run a scheduler on a platform pg_cron doesn’t support; or you want to run several parallel schedulers simultaneously; it’s possible you hit the bugs in pg_cron that haven’t been fixed for ages […]
View permissions and row-level security in PostgreSQL
The details of how view permissions are checked have long puzzled me. PostgreSQL v15 introduces “security invoker” views, which change how permissions are checked. The new security invoker views make it possible to use row-level security effectively. I’ll use this opportunity to explain how view permissions work and how you can benefit from this […]