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

Commite7ec022

Browse files
committed
Fix longstanding problems in VACUUM caused by untimely interruptions
In VACUUM FULL, an interrupt after the initial transaction has been recordedas committed can cause postmaster to restart with the following error message:PANIC: cannot abort transaction NNNN, it was already committedThis problem has been reported many times.In lazy VACUUM, an interrupt after the table has been truncated bylazy_truncate_heap causes other backends' relcache to still point to theremoved pages; this can cause future INSERT and UPDATE queries to error outwith the following error message:could not read block XX of relation 1663/NNN/MMMM: read only 0 of 8192 bytesThe window to this race condition is extremely narrow, but it has been seen inthe wild involving a cancelled autovacuum process.The solution for both problems is to inhibit interrupts in both operationsuntil after the respective transactions have been committed. It's not acomplete solution, because the transaction could theoretically be aborted bysome other error, but at least fixes the most common causes of both problems.
1 parentb538b72 commite7ec022

File tree

3 files changed

+50
-13
lines changed

3 files changed

+50
-13
lines changed

‎src/backend/commands/vacuum.c

Lines changed: 30 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
*
1414
*
1515
* IDENTIFICATION
16-
* $PostgreSQL: pgsql/src/backend/commands/vacuum.c,v 1.394 2009/10/26 02:26:29 tgl Exp $
16+
* $PostgreSQL: pgsql/src/backend/commands/vacuum.c,v 1.395 2009/11/10 18:00:06 alvherre Exp $
1717
*
1818
*-------------------------------------------------------------------------
1919
*/
@@ -217,10 +217,10 @@ static List *get_rel_oids(Oid relid, const RangeVar *vacrel,
217217
staticvoidvac_truncate_clog(TransactionIdfrozenXID);
218218
staticvoidvacuum_rel(Oidrelid,VacuumStmt*vacstmt,booldo_toast,
219219
boolfor_wraparound,bool*scanned_all);
220-
staticvoidfull_vacuum_rel(Relationonerel,VacuumStmt*vacstmt);
220+
staticboolfull_vacuum_rel(Relationonerel,VacuumStmt*vacstmt);
221221
staticvoidscan_heap(VRelStats*vacrelstats,Relationonerel,
222222
VacPageListvacuum_pages,VacPageListfraged_pages);
223-
staticvoidrepair_frag(VRelStats*vacrelstats,Relationonerel,
223+
staticboolrepair_frag(VRelStats*vacrelstats,Relationonerel,
224224
VacPageListvacuum_pages,VacPageListfraged_pages,
225225
intnindexes,Relation*Irel);
226226
staticvoidmove_chain_tuple(Relationrel,
@@ -1020,6 +1020,7 @@ vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound,
10201020
Oidtoast_relid;
10211021
Oidsave_userid;
10221022
boolsave_secdefcxt;
1023+
boolheldoff;
10231024

10241025
if (scanned_all)
10251026
*scanned_all= false;
@@ -1186,9 +1187,9 @@ vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound,
11861187
* Do the actual work --- either FULL or "lazy" vacuum
11871188
*/
11881189
if (vacstmt->full)
1189-
full_vacuum_rel(onerel,vacstmt);
1190+
heldoff=full_vacuum_rel(onerel,vacstmt);
11901191
else
1191-
lazy_vacuum_rel(onerel,vacstmt,vac_strategy,scanned_all);
1192+
heldoff=lazy_vacuum_rel(onerel,vacstmt,vac_strategy,scanned_all);
11921193

11931194
/* Restore userid */
11941195
SetUserIdAndContext(save_userid,save_secdefcxt);
@@ -1202,6 +1203,10 @@ vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound,
12021203
PopActiveSnapshot();
12031204
CommitTransactionCommand();
12041205

1206+
/* now we can allow interrupts again, if disabled */
1207+
if (heldoff)
1208+
RESUME_INTERRUPTS();
1209+
12051210
/*
12061211
* If the relation has a secondary toast rel, vacuum that too while we
12071212
* still hold the session lock on the master table. Note however that
@@ -1235,8 +1240,11 @@ vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound,
12351240
*
12361241
*At entry, we have already established a transaction and opened
12371242
*and locked the relation.
1243+
*
1244+
*The return value indicates whether this function has held off
1245+
*interrupts -- caller must RESUME_INTERRUPTS() after commit if true.
12381246
*/
1239-
staticvoid
1247+
staticbool
12401248
full_vacuum_rel(Relationonerel,VacuumStmt*vacstmt)
12411249
{
12421250
VacPageListDatavacuum_pages;/* List of pages to vacuum and/or
@@ -1247,6 +1255,7 @@ full_vacuum_rel(Relation onerel, VacuumStmt *vacstmt)
12471255
intnindexes,
12481256
i;
12491257
VRelStats*vacrelstats;
1258+
boolheldoff= false;
12501259

12511260
vacuum_set_xid_limits(vacstmt->freeze_min_age,vacstmt->freeze_table_age,
12521261
onerel->rd_rel->relisshared,
@@ -1298,7 +1307,7 @@ full_vacuum_rel(Relation onerel, VacuumStmt *vacstmt)
12981307
if (fraged_pages.num_pages>0)
12991308
{
13001309
/* Try to shrink heap */
1301-
repair_frag(vacrelstats,onerel,&vacuum_pages,&fraged_pages,
1310+
heldoff=repair_frag(vacrelstats,onerel,&vacuum_pages,&fraged_pages,
13021311
nindexes,Irel);
13031312
vac_close_indexes(nindexes,Irel,NoLock);
13041313
}
@@ -1324,6 +1333,8 @@ full_vacuum_rel(Relation onerel, VacuumStmt *vacstmt)
13241333
/* report results to the stats collector, too */
13251334
pgstat_report_vacuum(RelationGetRelid(onerel),onerel->rd_rel->relisshared,
13261335
true,vacstmt->analyze,vacrelstats->rel_tuples);
1336+
1337+
returnheldoff;
13271338
}
13281339

13291340

@@ -1874,8 +1885,11 @@ scan_heap(VRelStats *vacrelstats, Relation onerel,
18741885
*for them after committing (in hack-manner - without losing locks
18751886
*and freeing memory!) current transaction. It truncates relation
18761887
*if some end-blocks are gone away.
1888+
*
1889+
*The return value indicates whether this function has held off
1890+
*interrupts -- caller must RESUME_INTERRUPTS() after commit if true.
18771891
*/
1878-
staticvoid
1892+
staticbool
18791893
repair_frag(VRelStats*vacrelstats,Relationonerel,
18801894
VacPageListvacuum_pages,VacPageListfraged_pages,
18811895
intnindexes,Relation*Irel)
@@ -1900,6 +1914,7 @@ repair_frag(VRelStats *vacrelstats, Relation onerel,
19001914
intkeep_tuples=0;
19011915
intkeep_indexed_tuples=0;
19021916
PGRUsageru0;
1917+
boolheldoff= false;
19031918

19041919
pg_rusage_init(&ru0);
19051920

@@ -2705,8 +2720,12 @@ repair_frag(VRelStats *vacrelstats, Relation onerel,
27052720
*
27062721
* XXX This desperately needs to be revisited.Any failure after this
27072722
* point will result in a PANIC "cannot abort transaction nnn, it was
2708-
* already committed"!
2723+
* already committed"! As a precaution, we prevent cancel interrupts
2724+
* after this point to mitigate this problem; caller is responsible for
2725+
* re-enabling them after committing the transaction.
27092726
*/
2727+
HOLD_INTERRUPTS();
2728+
heldoff= true;
27102729
ForceSyncCommit();
27112730
(void)RecordTransactionCommit();
27122731
}
@@ -2906,6 +2925,8 @@ repair_frag(VRelStats *vacrelstats, Relation onerel,
29062925
pfree(vacrelstats->vtlinks);
29072926

29082927
ExecContext_Finish(&ec);
2928+
2929+
returnheldoff;
29092930
}
29102931

29112932
/*

‎src/backend/commands/vacuumlazy.c

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -29,7 +29,7 @@
2929
*
3030
*
3131
* IDENTIFICATION
32-
* $PostgreSQL: pgsql/src/backend/commands/vacuumlazy.c,v 1.122 2009/08/24 02:18:32 tgl Exp $
32+
* $PostgreSQL: pgsql/src/backend/commands/vacuumlazy.c,v 1.123 2009/11/1018:00:06 alvherre Exp $
3333
*
3434
*-------------------------------------------------------------------------
3535
*/
@@ -140,8 +140,11 @@ static intvac_cmp_itemptr(const void *left, const void *right);
140140
*
141141
*At entry, we have already established a transaction and opened
142142
*and locked the relation.
143+
*
144+
*The return value indicates whether this function has held off
145+
*interrupts -- caller must RESUME_INTERRUPTS() after commit if true.
143146
*/
144-
void
147+
bool
145148
lazy_vacuum_rel(Relationonerel,VacuumStmt*vacstmt,
146149
BufferAccessStrategybstrategy,bool*scanned_all)
147150
{
@@ -153,6 +156,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
153156
TimestampTzstarttime=0;
154157
boolscan_all;
155158
TransactionIdfreezeTableLimit;
159+
boolheldoff= false;
156160

157161
pg_rusage_init(&ru0);
158162

@@ -194,12 +198,22 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
194198
*
195199
* Don't even think about it unless we have a shot at releasing a goodly
196200
* number of pages. Otherwise, the time taken isn't worth it.
201+
*
202+
* Note that after we've truncated the heap, it's too late to abort the
203+
* transaction; doing so would lose the sinval messages needed to tell
204+
* the other backends about the table being shrunk. We prevent interrupts
205+
* in that case; caller is responsible for re-enabling them after
206+
* committing the transaction.
197207
*/
198208
possibly_freeable=vacrelstats->rel_pages-vacrelstats->nonempty_pages;
199209
if (possibly_freeable>0&&
200210
(possibly_freeable >=REL_TRUNCATE_MINIMUM||
201211
possibly_freeable >=vacrelstats->rel_pages /REL_TRUNCATE_FRACTION))
212+
{
213+
HOLD_INTERRUPTS();
214+
heldoff= true;
202215
lazy_truncate_heap(onerel,vacrelstats);
216+
}
203217

204218
/* Vacuum the Free Space Map */
205219
FreeSpaceMapVacuum(onerel);
@@ -246,6 +260,8 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
246260

247261
if (scanned_all)
248262
*scanned_all=vacrelstats->scanned_all;
263+
264+
returnheldoff;
249265
}
250266

251267

‎src/include/commands/vacuum.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/commands/vacuum.h,v 1.85 2009/06/11 14:49:11 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/commands/vacuum.h,v 1.86 2009/11/10 18:00:06 alvherre Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -146,7 +146,7 @@ extern bool vac_is_partial_index(Relation indrel);
146146
externvoidvacuum_delay_point(void);
147147

148148
/* in commands/vacuumlazy.c */
149-
externvoidlazy_vacuum_rel(Relationonerel,VacuumStmt*vacstmt,
149+
externboollazy_vacuum_rel(Relationonerel,VacuumStmt*vacstmt,
150150
BufferAccessStrategybstrategy,bool*scanned_all);
151151

152152
/* in commands/analyze.c */

0 commit comments

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