Extensions
pg_dirtyread

pg_dirtyread

pg_dirtyread : Read dead but unvacuumed rows from table

Overview

IDExtensionPackageVersionCategoryLicenseLanguage
5050
pg_dirtyread
pg_dirtyread
2.7
ADMIN
BSD 3-Clause
C
AttributeHas BinaryHas LibraryNeed LoadHas DDLRelocatableTrusted
--s-d-r
No
Yes
No
Yes
yes
no
Relationships
See Also
pg_orphaned
pg_surgery
pageinspect
pg_visibility
pg_cheat_funcs
amcheck
pg_repack
pg_squeeze

Packages

TypeRepoVersionPG Major CompatibilityPackage PatternDependencies
EXT
PGDG
2.7
18
17
16
15
14
pg_dirtyread-
RPM
PGDG
2.7
18
17
16
15
14
pg_dirtyread_$v-
DEB
PGDG
2.7
18
17
16
15
14
postgresql-$v-dirtyread-
Linux / PGPG18PG17PG16PG15PG14
el8.x86_64
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
el8.aarch64
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
el9.x86_64
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
el9.aarch64
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
el10.x86_64
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
el10.aarch64
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
d12.x86_64
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
d12.aarch64
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
d13.x86_64
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
d13.aarch64
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
u22.x86_64
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
u22.aarch64
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
u24.x86_64
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
u24.aarch64
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7
PGDG 2.7

Source

Repository
github.com/df7cb/pg_dirtyread
Source Tarball
pg_dirtyread-2.7.tar.gz

Install

Make sure PGDG repo available:

pig repo add pgdg -u    # add pgdg 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

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

Last updated on
pg_squeezepgfincore