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

Commitd2bf06d

Browse files
pg_amcheck: avoid unhelpful verification attempts.
Avoid calling contrib/amcheck functions with relations that areunsuitable for checking. Specifically, don't attempt verification oftemporary relations, or indexes whose pg_index entry indicates that theindex is invalid, or not ready.These relations are not supported by any of the contrib/amcheckfunctions, for reasons that are pretty fundamental. For example, theimplementation of REINDEX CONCURRENTLY can add its own "transient"pg_index entries, which has rather unclear implications for the B-Treeverification functions, at least in the general case -- so they justtreat it as an error. It falls to the amcheck caller (in this casepg_amcheck) to deal with the situation at a higher level.pg_amcheck now simply treats these conditions as additional "visibilityconcerns" when it queries system catalogs. This is a little arbitrary.It seems to have the least problems among any of the availablealternatives.Author: Mark Dilger <mark.dilger@enterprisedb.com>Reported-By: Alexander Lakhin <exclusion@gmail.com>Reviewed-By: Peter Geoghegan <pg@bowt.ie>Reviewed-By: Robert Haas <robertmhaas@gmail.com>Bug: #17212Discussion:https://postgr.es/m/17212-34dd4a1d6bba98bf@postgresql.orgBackpatch: 14-, where pg_amcheck was introduced.
1 parent6df1543 commitd2bf06d

File tree

3 files changed

+299
-29
lines changed

3 files changed

+299
-29
lines changed

‎doc/src/sgml/ref/pg_amcheck.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -435,6 +435,18 @@ PostgreSQL documentation
435435
</variablelist>
436436
</para>
437437

438+
<warning>
439+
<para>
440+
The extra checks performed against B-tree indexes when the
441+
<option>--parent-check</option> option or the
442+
<option>--rootdescend</option> option is specified require
443+
relatively strong relation-level locks. These checks are the only
444+
checks that will block concurrent data modification from
445+
<command>INSERT</command>, <command>UPDATE</command>, and
446+
<command>DELETE</command> commands.
447+
</para>
448+
</warning>
449+
438450
<para>
439451
The following command-line options control the connection to the server:
440452

‎src/bin/pg_amcheck/pg_amcheck.c

Lines changed: 54 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -816,6 +816,9 @@ main(int argc, char *argv[])
816816
* names matching the expectations of verify_heap_slot_handler, which will
817817
* receive and handle each row returned from the verify_heapam() function.
818818
*
819+
* The constructed SQL command will silently skip temporary tables, as checking
820+
* them would needlessly draw errors from the underlying amcheck function.
821+
*
819822
* sql: buffer into which the heap table checking command will be written
820823
* rel: relation information for the heap table to be checked
821824
* conn: the connection to be used, for string escaping purposes
@@ -825,10 +828,10 @@ prepare_heap_command(PQExpBuffer sql, RelationInfo *rel, PGconn *conn)
825828
{
826829
resetPQExpBuffer(sql);
827830
appendPQExpBuffer(sql,
828-
"SELECT blkno, offnum, attnum, msg FROM %s.verify_heapam("
829-
"\nrelation := %u, on_error_stop := %s, check_toast := %s, skip := '%s'",
831+
"SELECT v.blkno, v.offnum, v.attnum, v.msg "
832+
"FROM pg_catalog.pg_class c, %s.verify_heapam("
833+
"\nrelation := c.oid, on_error_stop := %s, check_toast := %s, skip := '%s'",
830834
rel->datinfo->amcheck_schema,
831-
rel->reloid,
832835
opts.on_error_stop ?"true" :"false",
833836
opts.reconcile_toast ?"true" :"false",
834837
opts.skip);
@@ -838,7 +841,10 @@ prepare_heap_command(PQExpBuffer sql, RelationInfo *rel, PGconn *conn)
838841
if (opts.endblock >=0)
839842
appendPQExpBuffer(sql,", endblock := "INT64_FORMAT,opts.endblock);
840843

841-
appendPQExpBufferChar(sql,')');
844+
appendPQExpBuffer(sql,
845+
"\n) v WHERE c.oid = %u "
846+
"AND c.relpersistence != 't'",
847+
rel->reloid);
842848
}
843849

844850
/*
@@ -849,6 +855,10 @@ prepare_heap_command(PQExpBuffer sql, RelationInfo *rel, PGconn *conn)
849855
* functions do not return any, but rather return corruption information by
850856
* raising errors, which verify_btree_slot_handler expects.
851857
*
858+
* The constructed SQL command will silently skip temporary indexes, and
859+
* indexes being reindexed concurrently, as checking them would needlessly draw
860+
* errors from the underlying amcheck functions.
861+
*
852862
* sql: buffer into which the heap table checking command will be written
853863
* rel: relation information for the index to be checked
854864
* conn: the connection to be used, for string escaping purposes
@@ -858,27 +868,31 @@ prepare_btree_command(PQExpBuffer sql, RelationInfo *rel, PGconn *conn)
858868
{
859869
resetPQExpBuffer(sql);
860870

861-
/*
862-
* Embed the database, schema, and relation name in the query, so if the
863-
* check throws an error, the user knows which relation the error came
864-
* from.
865-
*/
866871
if (opts.parent_check)
867872
appendPQExpBuffer(sql,
868-
"SELECT * FROM %s.bt_index_parent_check("
869-
"index := '%u'::regclass, heapallindexed := %s, "
870-
"rootdescend := %s)",
873+
"SELECT %s.bt_index_parent_check("
874+
"index := c.oid, heapallindexed := %s, rootdescend := %s)"
875+
"\nFROM pg_catalog.pg_class c, pg_catalog.pg_index i "
876+
"WHERE c.oid = %u "
877+
"AND c.oid = i.indexrelid "
878+
"AND c.relpersistence != 't' "
879+
"AND i.indisready AND i.indisvalid AND i.indislive",
871880
rel->datinfo->amcheck_schema,
872-
rel->reloid,
873881
(opts.heapallindexed ?"true" :"false"),
874-
(opts.rootdescend ?"true" :"false"));
882+
(opts.rootdescend ?"true" :"false"),
883+
rel->reloid);
875884
else
876885
appendPQExpBuffer(sql,
877-
"SELECT * FROM %s.bt_index_check("
878-
"index := '%u'::regclass, heapallindexed := %s)",
886+
"SELECT %s.bt_index_check("
887+
"index := c.oid, heapallindexed := %s)"
888+
"\nFROM pg_catalog.pg_class c, pg_catalog.pg_index i "
889+
"WHERE c.oid = %u "
890+
"AND c.oid = i.indexrelid "
891+
"AND c.relpersistence != 't' "
892+
"AND i.indisready AND i.indisvalid AND i.indislive",
879893
rel->datinfo->amcheck_schema,
880-
rel->reloid,
881-
(opts.heapallindexed ?"true" :"false"));
894+
(opts.heapallindexed ?"true" :"false"),
895+
rel->reloid);
882896
}
883897

884898
/*
@@ -1086,15 +1100,17 @@ verify_btree_slot_handler(PGresult *res, PGconn *conn, void *context)
10861100

10871101
if (PQresultStatus(res)==PGRES_TUPLES_OK)
10881102
{
1089-
intntups=PQntuples(res);
1103+
intntups=PQntuples(res);
10901104

1091-
if (ntups!=1)
1105+
if (ntups>1)
10921106
{
10931107
/*
10941108
* We expect the btree checking functions to return one void row
1095-
* each, so we should output some sort of warning if we get
1096-
* anything else, not because it indicates corruption, but because
1097-
* it suggests a mismatch between amcheck and pg_amcheck versions.
1109+
* each, or zero rows if the check was skipped due to the object
1110+
* being in the wrong state to be checked, so we should output some
1111+
* sort of warning if we get anything more, not because it
1112+
* indicates corruption, but because it suggests a mismatch between
1113+
* amcheck and pg_amcheck versions.
10981114
*
10991115
* In conjunction with --progress, anything written to stderr at
11001116
* this time would present strangely to the user without an extra
@@ -1889,10 +1905,16 @@ compile_relation_list_one_db(PGconn *conn, SimplePtrList *relations,
18891905
"\nAND (c.relam = %u OR NOT ep.btree_only OR ep.rel_regex IS NULL)",
18901906
HEAP_TABLE_AM_OID,BTREE_AM_OID);
18911907

1908+
/*
1909+
* Exclude temporary tables and indexes, which must necessarily belong to
1910+
* other sessions. (We don't create any ourselves.) We must ultimately
1911+
* exclude indexes marked invalid or not ready, but we delay that decision
1912+
* until firing off the amcheck command, as the state of an index may
1913+
* change by then.
1914+
*/
1915+
appendPQExpBufferStr(&sql,"\nWHERE c.relpersistence != 't'");
18921916
if (opts.excludetbl||opts.excludeidx||opts.excludensp)
1893-
appendPQExpBufferStr(&sql,"\nWHERE ep.pattern_id IS NULL");
1894-
else
1895-
appendPQExpBufferStr(&sql,"\nWHERE true");
1917+
appendPQExpBufferStr(&sql,"\nAND ep.pattern_id IS NULL");
18961918

18971919
/*
18981920
* We need to be careful not to break the --no-dependent-toast and
@@ -1944,7 +1966,8 @@ compile_relation_list_one_db(PGconn *conn, SimplePtrList *relations,
19441966
"\nON ('pg_toast' ~ ep.nsp_regex OR ep.nsp_regex IS NULL)"
19451967
"\nAND (t.relname ~ ep.rel_regex OR ep.rel_regex IS NULL)"
19461968
"\nAND ep.heap_only"
1947-
"\nWHERE ep.pattern_id IS NULL");
1969+
"\nWHERE ep.pattern_id IS NULL"
1970+
"\nAND t.relpersistence != 't'");
19481971
appendPQExpBufferStr(&sql,
19491972
"\n)");
19501973
}
@@ -1962,7 +1985,8 @@ compile_relation_list_one_db(PGconn *conn, SimplePtrList *relations,
19621985
"\nINNER JOIN pg_catalog.pg_index i "
19631986
"ON r.oid = i.indrelid "
19641987
"INNER JOIN pg_catalog.pg_class c "
1965-
"ON i.indexrelid = c.oid");
1988+
"ON i.indexrelid = c.oid "
1989+
"AND c.relpersistence != 't'");
19661990
if (opts.excludeidx||opts.excludensp)
19671991
appendPQExpBufferStr(&sql,
19681992
"\nINNER JOIN pg_catalog.pg_namespace n "
@@ -2000,7 +2024,8 @@ compile_relation_list_one_db(PGconn *conn, SimplePtrList *relations,
20002024
"INNER JOIN pg_catalog.pg_index i "
20012025
"ON t.oid = i.indrelid"
20022026
"\nINNER JOIN pg_catalog.pg_class c "
2003-
"ON i.indexrelid = c.oid");
2027+
"ON i.indexrelid = c.oid "
2028+
"AND c.relpersistence != 't'");
20042029
if (opts.excludeidx)
20052030
appendPQExpBufferStr(&sql,
20062031
"\nLEFT OUTER JOIN exclude_pat ep "

0 commit comments

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