Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit34a947c

Browse files
committed
New contrib module, pg_surgery, with heap surgery functions.
Sometimes it happens that the visibility information for a tuplebecomes corrupted, either due to bugs in the database software orexternal factors. Provide a function heap_force_kill() that canbe used to truncate such dead tuples to dead line pointers, anda function heap_force_freeze() that can be used to overwrite thevisibility information in such a way that the tuple becomesall-visible.These functions are unsafe, in that you can easily use them tocorrupt a database that was not previously corrupted, and you canuse them to further corrupt an already-corrupted database or todestroy data. The documentation accordingly cautions againstcasual use. However, in some cases they permit recovery of datathat would otherwise be very difficult to recover, or to allow asystem to continue to function when it would otherwise be difficultto do so.Because we may want to add other functions for performing otherkinds of surgery in the future, the new contrib module is calledpg_surgery rather than something specific to these functions. Iproposed back-patching this so that it could be more easily usedby people running existing releases who are facing these kinds ofproblems, but that proposal did not attract enough support, sono 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
1 parentc02767d commit34a947c

File tree

12 files changed

+860
-0
lines changed

12 files changed

+860
-0
lines changed

‎contrib/Makefile

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -34,6 +34,7 @@ SUBDIRS = \
3434
pg_prewarm\
3535
pg_standby\
3636
pg_stat_statements\
37+
pg_surgery\
3738
pg_trgm\
3839
pgcrypto\
3940
pgrowlocks\

‎contrib/pg_surgery/.gitignore

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
# Generated subdirectories
2+
/log/
3+
/results/
4+
/tmp_check/

‎contrib/pg_surgery/Makefile

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
# contrib/pg_surgery/Makefile
2+
3+
MODULE_big = pg_surgery
4+
OBJS =\
5+
$(WIN32RES)\
6+
heap_surgery.o
7+
8+
EXTENSION = pg_surgery
9+
DATA = pg_surgery--1.0.sql
10+
PGFILEDESC = "pg_surgery - perform surgery on a damaged relation"
11+
12+
REGRESS = heap_surgery
13+
14+
ifdefUSE_PGXS
15+
PG_CONFIG = pg_config
16+
PGXS :=$(shell$(PG_CONFIG) --pgxs)
17+
include$(PGXS)
18+
else
19+
subdir = contrib/pg_surgery
20+
top_builddir = ../..
21+
include$(top_builddir)/src/Makefile.global
22+
include$(top_srcdir)/contrib/contrib-global.mk
23+
endif
Lines changed: 180 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,180 @@
1+
create extension pg_surgery;
2+
-- create a normal heap table and insert some rows.
3+
-- note that we don't commit the transaction, so autovacuum can't interfere.
4+
begin;
5+
create table htab(a int);
6+
insert into htab values (100), (200), (300), (400), (500);
7+
-- test empty TID array
8+
select heap_force_freeze('htab'::regclass, ARRAY[]::tid[]);
9+
heap_force_freeze
10+
-------------------
11+
12+
(1 row)
13+
14+
-- nothing should be frozen yet
15+
select * from htab where xmin = 2;
16+
a
17+
---
18+
(0 rows)
19+
20+
-- freeze forcibly
21+
select heap_force_freeze('htab'::regclass, ARRAY['(0, 4)']::tid[]);
22+
heap_force_freeze
23+
-------------------
24+
25+
(1 row)
26+
27+
-- now we should have one frozen tuple
28+
select ctid, xmax from htab where xmin = 2;
29+
ctid | xmax
30+
-------+------
31+
(0,4) | 0
32+
(1 row)
33+
34+
-- kill forcibly
35+
select heap_force_kill('htab'::regclass, ARRAY['(0, 4)']::tid[]);
36+
heap_force_kill
37+
-----------------
38+
39+
(1 row)
40+
41+
-- should be gone now
42+
select * from htab where ctid = '(0, 4)';
43+
a
44+
---
45+
(0 rows)
46+
47+
-- should now be skipped because it's already dead
48+
select heap_force_kill('htab'::regclass, ARRAY['(0, 4)']::tid[]);
49+
NOTICE: skipping tid (0, 4) for relation "htab" because it is marked dead
50+
heap_force_kill
51+
-----------------
52+
53+
(1 row)
54+
55+
select heap_force_freeze('htab'::regclass, ARRAY['(0, 4)']::tid[]);
56+
NOTICE: skipping tid (0, 4) for relation "htab" because it is marked dead
57+
heap_force_freeze
58+
-------------------
59+
60+
(1 row)
61+
62+
-- freeze two TIDs at once while skipping an out-of-range block number
63+
select heap_force_freeze('htab'::regclass,
64+
ARRAY['(0, 1)', '(0, 3)', '(1, 1)']::tid[]);
65+
NOTICE: skipping block 1 for relation "htab" because the block number is out of range
66+
heap_force_freeze
67+
-------------------
68+
69+
(1 row)
70+
71+
-- we should now have two frozen tuples
72+
select ctid, xmax from htab where xmin = 2;
73+
ctid | xmax
74+
-------+------
75+
(0,1) | 0
76+
(0,3) | 0
77+
(2 rows)
78+
79+
-- out-of-range TIDs should be skipped
80+
select heap_force_freeze('htab'::regclass, ARRAY['(0, 0)', '(0, 6)']::tid[]);
81+
NOTICE: skipping tid (0, 0) for relation "htab" because the item number is out of range
82+
NOTICE: skipping tid (0, 6) for relation "htab" because the item number is out of range
83+
heap_force_freeze
84+
-------------------
85+
86+
(1 row)
87+
88+
rollback;
89+
-- set up a new table with a redirected line pointer
90+
create table htab2(a int) with (autovacuum_enabled = off);
91+
insert into htab2 values (100);
92+
update htab2 set a = 200;
93+
vacuum htab2;
94+
-- redirected TIDs should be skipped
95+
select heap_force_kill('htab2'::regclass, ARRAY['(0, 1)']::tid[]);
96+
NOTICE: skipping tid (0, 1) for relation "htab2" because it redirects to item 2
97+
heap_force_kill
98+
-----------------
99+
100+
(1 row)
101+
102+
-- now create an unused line pointer
103+
select ctid from htab2;
104+
ctid
105+
-------
106+
(0,2)
107+
(1 row)
108+
109+
update htab2 set a = 300;
110+
select ctid from htab2;
111+
ctid
112+
-------
113+
(0,3)
114+
(1 row)
115+
116+
vacuum freeze htab2;
117+
-- unused TIDs should be skipped
118+
select heap_force_kill('htab2'::regclass, ARRAY['(0, 2)']::tid[]);
119+
NOTICE: skipping tid (0, 2) for relation "htab2" because it is marked unused
120+
heap_force_kill
121+
-----------------
122+
123+
(1 row)
124+
125+
-- multidimensional TID array should be rejected
126+
select heap_force_kill('htab2'::regclass, ARRAY[['(0, 2)']]::tid[]);
127+
ERROR: argument must be empty or one-dimensional array
128+
-- TID array with nulls should be rejected
129+
select heap_force_kill('htab2'::regclass, ARRAY[NULL]::tid[]);
130+
ERROR: array must not contain nulls
131+
-- but we should be able to kill the one tuple we have
132+
select heap_force_kill('htab2'::regclass, ARRAY['(0, 3)']::tid[]);
133+
heap_force_kill
134+
-----------------
135+
136+
(1 row)
137+
138+
-- materialized view.
139+
-- note that we don't commit the transaction, so autovacuum can't interfere.
140+
begin;
141+
create materialized view mvw as select a from generate_series(1, 3) a;
142+
select * from mvw where xmin = 2;
143+
a
144+
---
145+
(0 rows)
146+
147+
select heap_force_freeze('mvw'::regclass, ARRAY['(0, 3)']::tid[]);
148+
heap_force_freeze
149+
-------------------
150+
151+
(1 row)
152+
153+
select * from mvw where xmin = 2;
154+
a
155+
---
156+
3
157+
(1 row)
158+
159+
select heap_force_kill('mvw'::regclass, ARRAY['(0, 3)']::tid[]);
160+
heap_force_kill
161+
-----------------
162+
163+
(1 row)
164+
165+
select * from mvw where ctid = '(0, 3)';
166+
a
167+
---
168+
(0 rows)
169+
170+
rollback;
171+
-- check that it fails on an unsupported relkind
172+
create view vw as select 1;
173+
select heap_force_kill('vw'::regclass, ARRAY['(0, 1)']::tid[]);
174+
ERROR: "vw" is not a table, materialized view, or TOAST table
175+
select heap_force_freeze('vw'::regclass, ARRAY['(0, 1)']::tid[]);
176+
ERROR: "vw" is not a table, materialized view, or TOAST table
177+
-- cleanup.
178+
drop table htab2;
179+
drop view vw;
180+
drop extension pg_surgery;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp