pg_dirtyread
pg_dirtyread
pg_dirtyread : Read dead but unvacuumed rows from table
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 5050 | pg_dirtyread
|
pg_dirtyread
|
2.7 |
ADMIN
|
BSD 3-Clause
|
C
|
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--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
| Type | Repo | Version | PG Major Compatibility | Package Pattern | Dependencies |
|---|---|---|---|---|---|
| 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 / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
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
Install
Make sure PGDG repo available:
pig repo add pgdg -u # add pgdg repo and update cacheInstall 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 14Create this extension with:
CREATE EXTENSION pg_dirtyread;Usage
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 TestDropped 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