pg_dirtyread

Overview

Version

Install

You can install pg_dirtyread directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pg_dirtyread;          # Install for current active PG version

pig ext install -y pg_dirtyread -v 18  # PG 18
pig ext install -y pg_dirtyread -v 17  # PG 17
pig ext install -y pg_dirtyread -v 16  # PG 16
pig ext install -y pg_dirtyread -v 15  # PG 15
pig ext install -y pg_dirtyread -v 14  # PG 14

dnf install -y pg_dirtyread_18       # PG 18
dnf install -y pg_dirtyread_17       # PG 17
dnf install -y pg_dirtyread_16       # PG 16
dnf install -y pg_dirtyread_15       # PG 15
dnf install -y pg_dirtyread_14       # PG 14

apt install -y postgresql-18-dirtyread   # PG 18
apt install -y postgresql-17-dirtyread   # PG 17
apt install -y postgresql-16-dirtyread   # PG 16
apt install -y postgresql-15-dirtyread   # PG 15
apt install -y postgresql-14-dirtyread   # PG 14

Create Extension:

CREATE EXTENSION pg_dirtyread;

Usage

pg_dirtyread: Read dead but unvacuumed rows from table

pg_dirtyread allows reading dead (deleted/updated) rows that have not yet been vacuumed. The function returns RECORD, so a table alias describing the schema is required.

Basic Usage

SELECT * FROM pg_dirtyread('foo') AS t(bar bigint, baz text);

Example

CREATE TABLE foo (bar bigint, baz text);
ALTER TABLE foo SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);

INSERT INTO foo VALUES (1, 'Test'), (2, 'New Test');
DELETE FROM foo WHERE bar = 1;

SELECT * FROM pg_dirtyread('foo') AS t(bar bigint, baz text);
 bar |   baz
-----+----------
   1 | Test
   2 | New Test

Dropped Columns

Access dropped column content using dropped_N (Nth column, 1-based), as long as the table has not been rewritten (e.g., via VACUUM FULL or CLUSTER):

ALTER TABLE ab DROP COLUMN b;
DELETE FROM ab;
SELECT * FROM pg_dirtyread('ab') ab(a text, dropped_2 text);

System Columns

Include system columns in the alias to retrieve them. A special dead boolean column reports dead rows:

SELECT * FROM pg_dirtyread('foo')
    AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,
         bar bigint, baz text);

The dead column is not usable during recovery (e.g., on standby servers).