New contrib module, pg_surgery, with heap surgery functions.

Sometimes it happens that the visibility information for a tuple
becomes corrupted, either due to bugs in the database software or
external factors. Provide a function heap_force_kill() that can
be used to truncate such dead tuples to dead line pointers, and
a function heap_force_freeze() that can be used to overwrite the
visibility information in such a way that the tuple becomes
all-visible.

These functions are unsafe, in that you can easily use them to
corrupt a database that was not previously corrupted, and you can
use them to further corrupt an already-corrupted database or to
destroy data. The documentation accordingly cautions against
casual use. However, in some cases they permit recovery of data
that would otherwise be very difficult to recover, or to allow a
system to continue to function when it would otherwise be difficult
to do so.

Because we may want to add other functions for performing other
kinds of surgery in the future, the new contrib module is called
pg_surgery rather than something specific to these functions. I
proposed back-patching this so that it could be more easily used
by people running existing releases who are facing these kinds of
problems, but that proposal did not attract enough support, so
no back-patch for now.

Ashutosh Sharma, reviewed and tested by Andrey M. Borodin,
M. Beena Emerson, Masahiko Sawada, Rajkumar Raghuwanshi,
Asim Praveen, and Mark Dilger, and somewhat revised by me.

Discussion: http://postgr.es/m/CA+TgmoZW1fsU-QUNCRUQMGUygBDPVeOTLCqRdQZch=EYZnctSA@mail.gmail.com

3 files changed, 109 insertions, 0 deletions

diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml
index 261a559e81c..c82dde27263 100644
--- a/doc/src/sgml/contrib.sgml
+++ b/doc/src/sgml/contrib.sgml
@@ -125,6 +125,7 @@ CREATE EXTENSION <replaceable>module_name</replaceable>;
  &pgrowlocks;
  &pgstatstatements;
  &pgstattuple;
+ &pgsurgery;
  &pgtrgm;
  &pgvisibility;
  &postgres-fdw;
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 64b5da0070c..828396d4a9e 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -139,6 +139,7 @@
 <!ENTITY pgstandby       SYSTEM "pgstandby.sgml">
 <!ENTITY pgstatstatements SYSTEM "pgstatstatements.sgml">
 <!ENTITY pgstattuple     SYSTEM "pgstattuple.sgml">
+<!ENTITY pgsurgery       SYSTEM "pgsurgery.sgml">
 <!ENTITY pgtrgm          SYSTEM "pgtrgm.sgml">
 <!ENTITY pgvisibility    SYSTEM "pgvisibility.sgml">
 <!ENTITY postgres-fdw    SYSTEM "postgres-fdw.sgml">
diff --git a/doc/src/sgml/pgsurgery.sgml b/doc/src/sgml/pgsurgery.sgml
new file mode 100644
index 00000000000..134be9bebde
--- /dev/null
+++ b/doc/src/sgml/pgsurgery.sgml
@@ -0,0 +1,107 @@
+<!-- doc/src/sgml/pgsurgery.sgml -->
+
+<sect1 id="pgsurgery" xreflabel="pg_surgery">
+ <title>pg_surgery</title>
+
+ <indexterm zone="pgsurgery">
+  <primary>pg_surgery</primary>
+ </indexterm>
+
+ <para>
+  The <filename>pg_surgery</filename> 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 <literal>UNIQUE</literal> or
+  <literal>FOREIGN KEY</literal> 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.
+ </para>
+
+ <sect2>
+  <title>Functions</title>
+
+  <variablelist>
+   <varlistentry>
+    <term>
+     <function>heap_force_kill(regclass, tid[]) returns void</function>
+    </term>
+
+    <listitem>
+     <para>
+      <function>heap_force_kill</function> marks <quote>used</quote> line
+      pointers as <quote>dead</quote> without examining the tuples. The
+      intended use of this function is to forcibly remove tuples that are not
+      otherwise accessible. For example:
+<programlisting>
+test=&gt; 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)
+
+</programlisting>
+    </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term>
+     <function>heap_force_freeze(regclass, tid[]) returns void</function>
+    </term>
+
+    <listitem>
+     <para>
+      <function>heap_force_freeze</function> 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:
+<programlisting>
+test=&gt; 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)
+
+</programlisting>
+    </para>
+   </listitem>
+  </varlistentry>
+
+  </variablelist>
+ </sect2>
+
+ <sect2>
+  <title>Authors</title>
+
+  <para>
+   Ashutosh Sharma <email>ashu.coek88@gmail.com</email>
+  </para>
+ </sect2>
+
+</sect1>