From ee49cfbfc153bbf6212d90ac81a2f9d13fa0eef7 Mon Sep 17 00:00:00 2001 From: ashu Date: Fri, 28 Aug 2020 15:00:41 +0530 Subject: [PATCH] Add contrib/pg_surgery to perform surgery on a damaged relation. This contrib module provides couple of functions named heap_force_kill and heap_force_freeze that can be used to perform surgery on a damaged heap table. Author: Ashutosh Sharma and Robert Haas. Reviewed by: Sawada Masahiko, Andrey Borodin, Beena Emerson, Mark Dilger and Robert Haas Tested by: Rajkumar Raghuwanshi Discussion: https://www.postgresql.org/message-id/CA%2BTgmoZW1fsU-QUNCRUQMGUygBDPVeOTLCqRdQZch%3DEYZnctSA%40mail.gmail.com --- contrib/Makefile | 1 + contrib/pg_surgery/.gitignore | 4 + contrib/pg_surgery/Makefile | 23 ++ contrib/pg_surgery/expected/heap_surgery.out | 180 +++++++++++ contrib/pg_surgery/heap_surgery.c | 428 +++++++++++++++++++++++++++ contrib/pg_surgery/pg_surgery--1.0.sql | 18 ++ contrib/pg_surgery/pg_surgery.control | 5 + contrib/pg_surgery/sql/heap_surgery.sql | 91 ++++++ doc/src/sgml/contrib.sgml | 1 + doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/pgsurgery.sgml | 107 +++++++ src/tools/pgindent/typedefs.list | 1 + 12 files changed, 860 insertions(+) create mode 100644 contrib/pg_surgery/.gitignore create mode 100644 contrib/pg_surgery/Makefile create mode 100644 contrib/pg_surgery/expected/heap_surgery.out create mode 100644 contrib/pg_surgery/heap_surgery.c create mode 100644 contrib/pg_surgery/pg_surgery--1.0.sql create mode 100644 contrib/pg_surgery/pg_surgery.control create mode 100644 contrib/pg_surgery/sql/heap_surgery.sql create mode 100644 doc/src/sgml/pgsurgery.sgml diff --git a/contrib/Makefile b/contrib/Makefile index 1846d41..c8d2a16 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -34,6 +34,7 @@ SUBDIRS = \ pg_prewarm \ pg_standby \ pg_stat_statements \ + pg_surgery \ pg_trgm \ pgcrypto \ pgrowlocks \ diff --git a/contrib/pg_surgery/.gitignore b/contrib/pg_surgery/.gitignore new file mode 100644 index 0000000..5dcb3ff --- /dev/null +++ b/contrib/pg_surgery/.gitignore @@ -0,0 +1,4 @@ +# Generated subdirectories +/log/ +/results/ +/tmp_check/ diff --git a/contrib/pg_surgery/Makefile b/contrib/pg_surgery/Makefile new file mode 100644 index 0000000..a66776c --- /dev/null +++ b/contrib/pg_surgery/Makefile @@ -0,0 +1,23 @@ +# contrib/pg_surgery/Makefile + +MODULE_big = pg_surgery +OBJS = \ + $(WIN32RES) \ + heap_surgery.o + +EXTENSION = pg_surgery +DATA = pg_surgery--1.0.sql +PGFILEDESC = "pg_surgery - perform surgery on a damaged relation" + +REGRESS = heap_surgery + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/pg_surgery +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/pg_surgery/expected/heap_surgery.out b/contrib/pg_surgery/expected/heap_surgery.out new file mode 100644 index 0000000..9451c57 --- /dev/null +++ b/contrib/pg_surgery/expected/heap_surgery.out @@ -0,0 +1,180 @@ +create extension pg_surgery; +-- create a normal heap table and insert some rows. +-- note that we don't commit the transaction, so autovacuum can't interfere. +begin; +create table htab(a int); +insert into htab values (100), (200), (300), (400), (500); +-- test empty TID array +select heap_force_freeze('htab'::regclass, ARRAY[]::tid[]); + heap_force_freeze +------------------- + +(1 row) + +-- nothing should be frozen yet +select * from htab where xmin = 2; + a +--- +(0 rows) + +-- freeze forcibly +select heap_force_freeze('htab'::regclass, ARRAY['(0, 4)']::tid[]); + heap_force_freeze +------------------- + +(1 row) + +-- now we should have one frozen tuple +select ctid, xmax from htab where xmin = 2; + ctid | xmax +-------+------ + (0,4) | 0 +(1 row) + +-- kill forcibly +select heap_force_kill('htab'::regclass, ARRAY['(0, 4)']::tid[]); + heap_force_kill +----------------- + +(1 row) + +-- should be gone now +select * from htab where ctid = '(0, 4)'; + a +--- +(0 rows) + +-- should now be skipped because it's already dead +select heap_force_kill('htab'::regclass, ARRAY['(0, 4)']::tid[]); +NOTICE: skipping tid (0, 4) for relation "htab" because it is marked dead + heap_force_kill +----------------- + +(1 row) + +select heap_force_freeze('htab'::regclass, ARRAY['(0, 4)']::tid[]); +NOTICE: skipping tid (0, 4) for relation "htab" because it is marked dead + heap_force_freeze +------------------- + +(1 row) + +-- freeze two TIDs at once while skipping an out-of-range block number +select heap_force_freeze('htab'::regclass, + ARRAY['(0, 1)', '(0, 3)', '(1, 1)']::tid[]); +NOTICE: skipping block 1 for relation "htab" because the block number is out of range + heap_force_freeze +------------------- + +(1 row) + +-- we should now have two frozen tuples +select ctid, xmax from htab where xmin = 2; + ctid | xmax +-------+------ + (0,1) | 0 + (0,3) | 0 +(2 rows) + +-- out-of-range TIDs should be skipped +select heap_force_freeze('htab'::regclass, ARRAY['(0, 0)', '(0, 6)']::tid[]); +NOTICE: skipping tid (0, 0) for relation "htab" because the item number is out of range +NOTICE: skipping tid (0, 6) for relation "htab" because the item number is out of range + heap_force_freeze +------------------- + +(1 row) + +rollback; +-- set up a new table with a redirected line pointer +create table htab2(a int) with (autovacuum_enabled = off); +insert into htab2 values (100); +update htab2 set a = 200; +vacuum htab2; +-- redirected TIDs should be skipped +select heap_force_kill('htab2'::regclass, ARRAY['(0, 1)']::tid[]); +NOTICE: skipping tid (0, 1) for relation "htab2" because it redirects to item 2 + heap_force_kill +----------------- + +(1 row) + +-- now create an unused line pointer +select ctid from htab2; + ctid +------- + (0,2) +(1 row) + +update htab2 set a = 300; +select ctid from htab2; + ctid +------- + (0,3) +(1 row) + +vacuum freeze htab2; +-- unused TIDs should be skipped +select heap_force_kill('htab2'::regclass, ARRAY['(0, 2)']::tid[]); +NOTICE: skipping tid (0, 2) for relation "htab2" because it is marked unused + heap_force_kill +----------------- + +(1 row) + +-- multidimensional TID array should be rejected +select heap_force_kill('htab2'::regclass, ARRAY[['(0, 2)']]::tid[]); +ERROR: argument must be empty or one-dimensional array +-- TID array with nulls should be rejected +select heap_force_kill('htab2'::regclass, ARRAY[NULL]::tid[]); +ERROR: array must not contain nulls +-- but we should be able to kill the one tuple we have +select heap_force_kill('htab2'::regclass, ARRAY['(0, 3)']::tid[]); + heap_force_kill +----------------- + +(1 row) + +-- materialized view. +-- note that we don't commit the transaction, so autovacuum can't interfere. +begin; +create materialized view mvw as select a from generate_series(1, 3) a; +select * from mvw where xmin = 2; + a +--- +(0 rows) + +select heap_force_freeze('mvw'::regclass, ARRAY['(0, 3)']::tid[]); + heap_force_freeze +------------------- + +(1 row) + +select * from mvw where xmin = 2; + a +--- + 3 +(1 row) + +select heap_force_kill('mvw'::regclass, ARRAY['(0, 3)']::tid[]); + heap_force_kill +----------------- + +(1 row) + +select * from mvw where ctid = '(0, 3)'; + a +--- +(0 rows) + +rollback; +-- check that it fails on an unsupported relkind +create view vw as select 1; +select heap_force_kill('vw'::regclass, ARRAY['(0, 1)']::tid[]); +ERROR: "vw" is not a table, materialized view, or TOAST table +select heap_force_freeze('vw'::regclass, ARRAY['(0, 1)']::tid[]); +ERROR: "vw" is not a table, materialized view, or TOAST table +-- cleanup. +drop table htab2; +drop view vw; +drop extension pg_surgery; diff --git a/contrib/pg_surgery/heap_surgery.c b/contrib/pg_surgery/heap_surgery.c new file mode 100644 index 0000000..eb96b4b --- /dev/null +++ b/contrib/pg_surgery/heap_surgery.c @@ -0,0 +1,428 @@ +/*------------------------------------------------------------------------- + * + * heap_surgery.c + * Functions to perform surgery on the damaged heap table. + * + * Copyright (c) 2020, PostgreSQL Global Development Group + * + * IDENTIFICATION + * contrib/pg_surgery/heap_surgery.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "access/heapam.h" +#include "access/visibilitymap.h" +#include "catalog/pg_am_d.h" +#include "catalog/pg_proc_d.h" +#include "miscadmin.h" +#include "storage/bufmgr.h" +#include "utils/acl.h" +#include "utils/rel.h" + +PG_MODULE_MAGIC; + +/* Options to forcefully change the state of a heap tuple. */ +typedef enum HeapTupleForceOption +{ + HEAP_FORCE_KILL, + HEAP_FORCE_FREEZE +} HeapTupleForceOption; + +PG_FUNCTION_INFO_V1(heap_force_kill); +PG_FUNCTION_INFO_V1(heap_force_freeze); + +static int32 tidcmp(const void *a, const void *b); +static Datum heap_force_common(FunctionCallInfo fcinfo, + HeapTupleForceOption heap_force_opt); +static void sanity_check_tid_array(ArrayType *ta, int *ntids); +static void sanity_check_relation(Relation rel); +static BlockNumber find_tids_one_page(ItemPointer tids, int ntids, + OffsetNumber *next_start_ptr); + +/*------------------------------------------------------------------------- + * heap_force_kill() + * + * Force kill the tuple(s) pointed to by the item pointer(s) stored in the + * given TID array. + * + * Usage: SELECT heap_force_kill(regclass, tid[]); + *------------------------------------------------------------------------- + */ +Datum +heap_force_kill(PG_FUNCTION_ARGS) +{ + PG_RETURN_DATUM(heap_force_common(fcinfo, HEAP_FORCE_KILL)); +} + +/*------------------------------------------------------------------------- + * heap_force_freeze() + * + * Force freeze the tuple(s) pointed to by the item pointer(s) stored in the + * given TID array. + * + * Usage: SELECT heap_force_freeze(regclass, tid[]); + *------------------------------------------------------------------------- + */ +Datum +heap_force_freeze(PG_FUNCTION_ARGS) +{ + PG_RETURN_DATUM(heap_force_common(fcinfo, HEAP_FORCE_FREEZE)); +} + +/*------------------------------------------------------------------------- + * heap_force_common() + * + * Common code for heap_force_kill and heap_force_freeze + *------------------------------------------------------------------------- + */ +static Datum +heap_force_common(FunctionCallInfo fcinfo, HeapTupleForceOption heap_force_opt) +{ + Oid relid = PG_GETARG_OID(0); + ArrayType *ta = PG_GETARG_ARRAYTYPE_P_COPY(1); + ItemPointer tids; + int ntids, + nblocks; + Relation rel; + OffsetNumber curr_start_ptr, + next_start_ptr; + bool include_this_tid[MaxHeapTuplesPerPage]; + + if (RecoveryInProgress()) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("recovery is in progress"), + errhint("heap surgery functions cannot be executed during recovery."))); + + /* Check inputs. */ + sanity_check_tid_array(ta, &ntids); + + rel = relation_open(relid, RowExclusiveLock); + + /* Check target relation. */ + sanity_check_relation(rel); + + tids = ((ItemPointer) ARR_DATA_PTR(ta)); + + /* + * If there is more than one TID in the array, sort them so that we can + * easily fetch all the TIDs belonging to one particular page from the + * array. + */ + if (ntids > 1) + qsort((void *) tids, ntids, sizeof(ItemPointerData), tidcmp); + + curr_start_ptr = next_start_ptr = 0; + nblocks = RelationGetNumberOfBlocks(rel); + + /* + * Loop, performing the necessary actions for each block. + */ + while (next_start_ptr != ntids) + { + Buffer buf; + Buffer vmbuf = InvalidBuffer; + Page page; + BlockNumber blkno; + OffsetNumber curoff; + OffsetNumber maxoffset; + int i; + bool did_modify_page = false; + bool did_modify_vm = false; + + CHECK_FOR_INTERRUPTS(); + + /* + * Find all the TIDs belonging to one particular page starting from + * next_start_ptr and process them one by one. + */ + blkno = find_tids_one_page(tids, ntids, &next_start_ptr); + + /* Check whether the block number is valid. */ + if (blkno >= nblocks) + { + /* Update the current_start_ptr before moving to the next page. */ + curr_start_ptr = next_start_ptr; + + ereport(NOTICE, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("skipping block %u for relation \"%s\" because the block number is out of range", + blkno, RelationGetRelationName(rel)))); + continue; + } + + buf = ReadBuffer(rel, blkno); + LockBufferForCleanup(buf); + + page = BufferGetPage(buf); + + maxoffset = PageGetMaxOffsetNumber(page); + + /* + * Figure out which TIDs we are going to process and which ones we are + * going to skip. + */ + memset(include_this_tid, 0, sizeof(include_this_tid)); + for (i = curr_start_ptr; i < next_start_ptr; i++) + { + OffsetNumber offno = ItemPointerGetOffsetNumberNoCheck(&tids[i]); + ItemId itemid; + + /* Check whether the offset number is valid. */ + if (offno == InvalidOffsetNumber || offno > maxoffset) + { + ereport(NOTICE, + errmsg("skipping tid (%u, %u) for relation \"%s\" because the item number is out of range", + blkno, offno, RelationGetRelationName(rel))); + continue; + } + + itemid = PageGetItemId(page, offno); + + /* Only accept an item ID that is used. */ + if (ItemIdIsRedirected(itemid)) + { + ereport(NOTICE, + errmsg("skipping tid (%u, %u) for relation \"%s\" because it redirects to item %u", + blkno, offno, RelationGetRelationName(rel), + ItemIdGetRedirect(itemid))); + continue; + } + else if (ItemIdIsDead(itemid)) + { + ereport(NOTICE, + (errmsg("skipping tid (%u, %u) for relation \"%s\" because it is marked dead", + blkno, offno, RelationGetRelationName(rel)))); + continue; + } + else if (!ItemIdIsUsed(itemid)) + { + ereport(NOTICE, + (errmsg("skipping tid (%u, %u) for relation \"%s\" because it is marked unused", + blkno, offno, RelationGetRelationName(rel)))); + continue; + } + + /* Mark it for processing. */ + Assert(offno < MaxHeapTuplesPerPage); + include_this_tid[offno] = true; + } + + /* + * Before entering the critical section, pin the visibility map page + * if it appears to be necessary. + */ + if (heap_force_opt == HEAP_FORCE_KILL && PageIsAllVisible(page)) + visibilitymap_pin(rel, blkno, &vmbuf); + + /* No ereport(ERROR) from here until all the changes are logged. */ + START_CRIT_SECTION(); + + for (curoff = FirstOffsetNumber; curoff <= maxoffset; + curoff = OffsetNumberNext(curoff)) + { + ItemId itemid; + + if (!include_this_tid[curoff]) + continue; + + itemid = PageGetItemId(page, curoff); + Assert(ItemIdIsNormal(itemid)); + + did_modify_page = true; + + if (heap_force_opt == HEAP_FORCE_KILL) + { + ItemIdSetDead(itemid); + + /* + * If the page is marked all-visible, we must clear + * PD_ALL_VISIBLE flag on the page header and an all-visible + * bit on the visibility map corresponding to the page. + */ + if (PageIsAllVisible(page)) + { + PageClearAllVisible(page); + visibilitymap_clear(rel, blkno, vmbuf, + VISIBILITYMAP_VALID_BITS); + did_modify_vm = true; + } + } + else + { + HeapTupleHeader htup; + + Assert(heap_force_opt == HEAP_FORCE_FREEZE); + + htup = (HeapTupleHeader) PageGetItem(page, itemid); + + /* + * Reset all visibility-related fields of the tuple. This + * logic should mimic heap_execute_freeze_tuple(), but we + * choose to reset xmin and ctid just to be sure that no + * potentially-garbled data is left behind. + */ + ItemPointerSet(&htup->t_ctid, blkno, curoff); + HeapTupleHeaderSetXmin(htup, FrozenTransactionId); + HeapTupleHeaderSetXmax(htup, InvalidTransactionId); + if (htup->t_infomask & HEAP_MOVED) + { + if (htup->t_infomask & HEAP_MOVED_OFF) + HeapTupleHeaderSetXvac(htup, InvalidTransactionId); + else + HeapTupleHeaderSetXvac(htup, FrozenTransactionId); + } + + /* + * Clear all the visibility-related bits of this tuple and + * mark it as frozen. Also, get rid of HOT_UPDATED and + * KEYS_UPDATES bits. + */ + htup->t_infomask &= ~HEAP_XACT_MASK; + htup->t_infomask |= (HEAP_XMIN_FROZEN | HEAP_XMAX_INVALID); + htup->t_infomask2 &= ~HEAP_HOT_UPDATED; + htup->t_infomask2 &= ~HEAP_KEYS_UPDATED; + } + } + + /* + * If the page was modified, only then, we mark the buffer dirty or do + * the WAL logging. + */ + if (did_modify_page) + { + /* Mark buffer dirty before we write WAL. */ + MarkBufferDirty(buf); + + /* XLOG stuff */ + if (RelationNeedsWAL(rel)) + log_newpage_buffer(buf, true); + } + + /* WAL log the VM page if it was modified. */ + if (did_modify_vm && RelationNeedsWAL(rel)) + log_newpage_buffer(vmbuf, false); + + END_CRIT_SECTION(); + + UnlockReleaseBuffer(buf); + + if (vmbuf != InvalidBuffer) + ReleaseBuffer(vmbuf); + + /* Update the current_start_ptr before moving to the next page. */ + curr_start_ptr = next_start_ptr; + } + + relation_close(rel, RowExclusiveLock); + + pfree(ta); + + PG_RETURN_VOID(); +} + +/*------------------------------------------------------------------------- + * tidcmp() + * + * Compare two item pointers, return -1, 0, or +1. + * + * See ItemPointerCompare for details. + * ------------------------------------------------------------------------ + */ +static int32 +tidcmp(const void *a, const void *b) +{ + ItemPointer iptr1 = ((const ItemPointer) a); + ItemPointer iptr2 = ((const ItemPointer) b); + + return ItemPointerCompare(iptr1, iptr2); +} + +/*------------------------------------------------------------------------- + * sanity_check_tid_array() + * + * Perform sanity checks on the given tid array, and set *ntids to the + * number of items in the array. + * ------------------------------------------------------------------------ + */ +static void +sanity_check_tid_array(ArrayType *ta, int *ntids) +{ + if (ARR_HASNULL(ta) && array_contains_nulls(ta)) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("array must not contain nulls"))); + + if (ARR_NDIM(ta) > 1) + ereport(ERROR, + (errcode(ERRCODE_DATA_EXCEPTION), + errmsg("argument must be empty or one-dimensional array"))); + + *ntids = ArrayGetNItems(ARR_NDIM(ta), ARR_DIMS(ta)); +} + +/*------------------------------------------------------------------------- + * sanity_check_relation() + * + * Perform sanity checks on the given relation. + * ------------------------------------------------------------------------ + */ +static void +sanity_check_relation(Relation rel) +{ + if (rel->rd_rel->relkind != RELKIND_RELATION && + rel->rd_rel->relkind != RELKIND_MATVIEW && + rel->rd_rel->relkind != RELKIND_TOASTVALUE) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a table, materialized view, or TOAST table", + RelationGetRelationName(rel)))); + + if (rel->rd_rel->relam != HEAP_TABLE_AM_OID) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("only heap AM is supported"))); + + /* Must be owner of the table or superuser. */ + if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId())) + aclcheck_error(ACLCHECK_NOT_OWNER, + get_relkind_objtype(rel->rd_rel->relkind), + RelationGetRelationName(rel)); +} + +/*------------------------------------------------------------------------- + * find_tids_one_page() + * + * Find all the tids residing in the same page as tids[next_start_ptr], and + * update next_start_ptr so that it points to the first tid in the next page. + * + * NOTE: The input tids[] array must be sorted. + * ------------------------------------------------------------------------ + */ +static BlockNumber +find_tids_one_page(ItemPointer tids, int ntids, OffsetNumber *next_start_ptr) +{ + int i; + BlockNumber prev_blkno, + blkno; + + prev_blkno = blkno = InvalidBlockNumber; + + for (i = *next_start_ptr; i < ntids; i++) + { + ItemPointerData tid = tids[i]; + + blkno = ItemPointerGetBlockNumberNoCheck(&tid); + + if (i == *next_start_ptr) + prev_blkno = blkno; + + if (prev_blkno != blkno) + break; + } + + *next_start_ptr = i; + return prev_blkno; +} diff --git a/contrib/pg_surgery/pg_surgery--1.0.sql b/contrib/pg_surgery/pg_surgery--1.0.sql new file mode 100644 index 0000000..2ae7f22 --- /dev/null +++ b/contrib/pg_surgery/pg_surgery--1.0.sql @@ -0,0 +1,18 @@ +/* contrib/pg_surgery/pg_surgery--1.0.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION pg_surgery" to load this file. \quit + +CREATE FUNCTION heap_force_kill(reloid regclass, tids tid[]) +RETURNS VOID +AS 'MODULE_PATHNAME', 'heap_force_kill' +LANGUAGE C STRICT; + +REVOKE EXECUTE ON FUNCTION heap_force_kill(regclass, tid[]) FROM PUBLIC; + +CREATE FUNCTION heap_force_freeze(reloid regclass, tids tid[]) +RETURNS VOID +AS 'MODULE_PATHNAME', 'heap_force_freeze' +LANGUAGE C STRICT; + +REVOKE EXECUTE ON FUNCTION heap_force_freeze(regclass, tid[]) FROM PUBLIC; \ No newline at end of file diff --git a/contrib/pg_surgery/pg_surgery.control b/contrib/pg_surgery/pg_surgery.control new file mode 100644 index 0000000..2bcdad1 --- /dev/null +++ b/contrib/pg_surgery/pg_surgery.control @@ -0,0 +1,5 @@ +# pg_surgery extension +comment = 'extension to perform surgery on a damaged relation' +default_version = '1.0' +module_pathname = '$libdir/pg_surgery' +relocatable = true diff --git a/contrib/pg_surgery/sql/heap_surgery.sql b/contrib/pg_surgery/sql/heap_surgery.sql new file mode 100644 index 0000000..8a27214 --- /dev/null +++ b/contrib/pg_surgery/sql/heap_surgery.sql @@ -0,0 +1,91 @@ +create extension pg_surgery; + +-- create a normal heap table and insert some rows. +-- note that we don't commit the transaction, so autovacuum can't interfere. +begin; +create table htab(a int); +insert into htab values (100), (200), (300), (400), (500); + +-- test empty TID array +select heap_force_freeze('htab'::regclass, ARRAY[]::tid[]); + +-- nothing should be frozen yet +select * from htab where xmin = 2; + +-- freeze forcibly +select heap_force_freeze('htab'::regclass, ARRAY['(0, 4)']::tid[]); + +-- now we should have one frozen tuple +select ctid, xmax from htab where xmin = 2; + +-- kill forcibly +select heap_force_kill('htab'::regclass, ARRAY['(0, 4)']::tid[]); + +-- should be gone now +select * from htab where ctid = '(0, 4)'; + +-- should now be skipped because it's already dead +select heap_force_kill('htab'::regclass, ARRAY['(0, 4)']::tid[]); +select heap_force_freeze('htab'::regclass, ARRAY['(0, 4)']::tid[]); + +-- freeze two TIDs at once while skipping an out-of-range block number +select heap_force_freeze('htab'::regclass, + ARRAY['(0, 1)', '(0, 3)', '(1, 1)']::tid[]); + +-- we should now have two frozen tuples +select ctid, xmax from htab where xmin = 2; + +-- out-of-range TIDs should be skipped +select heap_force_freeze('htab'::regclass, ARRAY['(0, 0)', '(0, 6)']::tid[]); + +rollback; + +-- set up a new table with a redirected line pointer +create table htab2(a int) with (autovacuum_enabled = off); +insert into htab2 values (100); +update htab2 set a = 200; +vacuum htab2; + +-- redirected TIDs should be skipped +select heap_force_kill('htab2'::regclass, ARRAY['(0, 1)']::tid[]); + +-- now create an unused line pointer +select ctid from htab2; +update htab2 set a = 300; +select ctid from htab2; +vacuum freeze htab2; + +-- unused TIDs should be skipped +select heap_force_kill('htab2'::regclass, ARRAY['(0, 2)']::tid[]); + +-- multidimensional TID array should be rejected +select heap_force_kill('htab2'::regclass, ARRAY[['(0, 2)']]::tid[]); + +-- TID array with nulls should be rejected +select heap_force_kill('htab2'::regclass, ARRAY[NULL]::tid[]); + +-- but we should be able to kill the one tuple we have +select heap_force_kill('htab2'::regclass, ARRAY['(0, 3)']::tid[]); + +-- materialized view. +-- note that we don't commit the transaction, so autovacuum can't interfere. +begin; +create materialized view mvw as select a from generate_series(1, 3) a; + +select * from mvw where xmin = 2; +select heap_force_freeze('mvw'::regclass, ARRAY['(0, 3)']::tid[]); +select * from mvw where xmin = 2; + +select heap_force_kill('mvw'::regclass, ARRAY['(0, 3)']::tid[]); +select * from mvw where ctid = '(0, 3)'; +rollback; + +-- check that it fails on an unsupported relkind +create view vw as select 1; +select heap_force_kill('vw'::regclass, ARRAY['(0, 1)']::tid[]); +select heap_force_freeze('vw'::regclass, ARRAY['(0, 1)']::tid[]); + +-- cleanup. +drop table htab2; +drop view vw; +drop extension pg_surgery; diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml index 261a559..c82dde2 100644 --- a/doc/src/sgml/contrib.sgml +++ b/doc/src/sgml/contrib.sgml @@ -125,6 +125,7 @@ CREATE EXTENSION module_name; &pgrowlocks; &pgstatstatements; &pgstattuple; + &pgsurgery; &pgtrgm; &pgvisibility; &postgres-fdw; diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 64b5da0..828396d 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -139,6 +139,7 @@ + diff --git a/doc/src/sgml/pgsurgery.sgml b/doc/src/sgml/pgsurgery.sgml new file mode 100644 index 0000000..134be9b --- /dev/null +++ b/doc/src/sgml/pgsurgery.sgml @@ -0,0 +1,107 @@ + + + + pg_surgery + + + pg_surgery + + + + The pg_surgery module provides various functions to + perform surgery on a damaged relation. These functions are unsafe by design + and using them may corrupt (or further corrupt) your database. For example, + these functions can easily be used to make a table inconsistent with its + own indexes, to cause UNIQUE or + FOREIGN KEY constraint violations, or even to make + tuples visible which, when read, will cause a database server crash. + They should be used with great caution and only as a last resort. + + + + Functions + + + + + heap_force_kill(regclass, tid[]) returns void + + + + + heap_force_kill marks used line + pointers as dead without examining the tuples. The + intended use of this function is to forcibly remove tuples that are not + otherwise accessible. For example: + +test=> select * from t1 where ctid = '(0, 1)'; +ERROR: could not access status of transaction 4007513275 +DETAIL: Could not open file "pg_xact/0EED": No such file or directory. + +test=# select heap_force_kill('t1'::regclass, ARRAY['(0, 1)']::tid[]); + heap_force_kill +----------------- + +(1 row) + +test=# select * from t1 where ctid = '(0, 1)'; +(0 rows) + + + + + + + + + heap_force_freeze(regclass, tid[]) returns void + + + + + heap_force_freeze marks tuples as frozen without + examining the tuple data. The intended use of this function is to + make accessible tuples which are inaccessible due to corrupted + visibility information, or which prevent the table from being + successfully vacuumed due to corrupted visibility information. + For example: + +test=> vacuum t1; +ERROR: found xmin 507 from before relfrozenxid 515 +CONTEXT: while scanning block 0 of relation "public.t1" + +test=# select ctid from t1 where xmin = 507; + ctid +------- + (0,3) +(1 row) + +test=# select heap_force_freeze('t1'::regclass, ARRAY['(0, 3)']::tid[]); + heap_force_freeze +------------------- + +(1 row) + +test=# select ctid from t1 where xmin = 2; + ctid +------- + (0,3) +(1 row) + + + + + + + + + + + Authors + + + Ashutosh Sharma ashu.coek88@gmail.com + + + + diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 3d99046..421de88 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -3573,3 +3573,4 @@ yyscan_t z_stream z_streamp zic_t +HeapTupleForceOption -- 1.8.3.1