pg_dirtyread

pg_dirtyread : Read dead but unvacuumed rows from table

Overview

Packages

Source

pig build pkg pg_dirtyread;		# build rpm

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_dirtyread;		# install via package name, for the active PG version

pig install pg_dirtyread -v 18;   # install for PG 18
pig install pg_dirtyread -v 17;   # install for PG 17
pig install pg_dirtyread -v 16;   # install for PG 16
pig install pg_dirtyread -v 15;   # install for PG 15
pig install pg_dirtyread -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION pg_dirtyread;

Usage

Sources: upstream README, Debian changelog, tags.

pg_dirtyread reads dead or updated heap rows that have not yet been vacuumed away. The function returns record, so every call needs a table alias that declares the columns you want back.

Basic Usage

CREATE EXTENSION pg_dirtyread;

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

Columns are matched by name, so the alias can omit columns or place them in a different order.

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);

The deleted row can remain visible until vacuum removes it.

Dropped Columns

Dropped column contents can be retrieved as long as the table has not been rewritten by operations such as VACUUM FULL or CLUSTER. Use dropped_N, where N is the original 1-based column position:

CREATE TABLE ab(a text, b text);
INSERT INTO ab VALUES ('Hello', 'World');
ALTER TABLE ab DROP COLUMN b;
DELETE FROM ab;

SELECT *
FROM pg_dirtyread('ab') AS ab(a text, dropped_2 text);

Only limited type checks are possible because PostgreSQL removes the dropped column’s original type metadata.

System Columns

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

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, including on standby servers. The oid system column is only available on PostgreSQL 11 and earlier.

Caveats

• Pigsty packages pg_dirtyread 2.8 as RPMs for PostgreSQL 14-18; DEB availability comes from PGDG as postgresql-$v-dirtyread.
• Upstream 2.8 is a PostgreSQL 19 compatibility release for the default TOAST compression change to lz4; no new user-facing SQL function is documented.
• pg_dirtyread is for forensic and recovery-style inspection. It bypasses normal MVCC visibility expectations and should not be used for application reads.