© Laurenz Albe 2022 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 […]

© Laurenz Albe 2021 Both cursors and transactions are basic ingredients for developing a database application. This article describes how cursors and transactions interact and how WITH HOLD can work around their limitations. We will also see the dangers involved and how to properly use WITH HOLD cursors in a PL/pgSQL procedure. Cursors in PostgreSQL […]

© Laurenz Albe 2020   Recently, while troubleshooting PostgreSQL performance problems, I ran into problems with subtransactions twice. So I thought this was a nice topic for another blog post. What are subtransactions? Everybody knows database transactions. In PostgreSQL, which is operating in autocommit mode, you have to start a transaction that spans multiple statements […]

xmax is a PostgreSQL system column that is used to implement Multiversion Concurrency Control (MVCC). The documentation is somewhat terse: The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version. It is possible for this column to be nonzero in a visible row version. That usually indicates that the deleting […]

About sequences Sequences are used to generate artificial numeric primary key columns for tables. A sequence provides a “new ID” that is guaranteed to be unique, even if many database sessions are using the sequence at the same time. Sequences are not transaction safe, because they are not supposed to block the caller. That is […]