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

Commit2646d2d

Browse files
Further changes to REINDEX SCHEMA
Ensure we reindex indexes built on Mat Views.Based on patch from Micheal PaquierAdd thorough tests to check that indexes ontables, toast tables and mat views are reindexed.Simon Riggs
1 parent0845264 commit2646d2d

File tree

3 files changed

+95
-15
lines changed

3 files changed

+95
-15
lines changed

‎src/backend/commands/indexcmds.c

Lines changed: 10 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1867,16 +1867,16 @@ ReindexObject(const char *objectName, ReindexObjectType objectKind)
18671867
*/
18681868
if (objectKind==REINDEX_OBJECT_SCHEMA)
18691869
{
1870-
scan_keys=palloc(sizeof(ScanKeyData)*2);
1870+
/*
1871+
* Return all objects in schema. We filter out
1872+
* inappropriate objects as we walk through results.
1873+
*/
1874+
num_keys=1;
1875+
scan_keys=palloc(sizeof(ScanKeyData));
18711876
ScanKeyInit(&scan_keys[0],
18721877
Anum_pg_class_relnamespace,
18731878
BTEqualStrategyNumber,F_OIDEQ,
18741879
ObjectIdGetDatum(objectOid));
1875-
ScanKeyInit(&scan_keys[1],
1876-
Anum_pg_class_relkind,
1877-
BTEqualStrategyNumber,F_CHAREQ,
1878-
'r');
1879-
num_keys=2;
18801880
}
18811881
else
18821882
num_keys=0;
@@ -1894,6 +1894,10 @@ ReindexObject(const char *objectName, ReindexObjectType objectKind)
18941894
Form_pg_classclasstuple= (Form_pg_class)GETSTRUCT(tuple);
18951895
Oidrelid=HeapTupleGetOid(tuple);
18961896

1897+
/*
1898+
* Only regular tables and matviews can have indexes,
1899+
* so filter out any other kind of object.
1900+
*/
18971901
if (classtuple->relkind!=RELKIND_RELATION&&
18981902
classtuple->relkind!=RELKIND_MATVIEW)
18991903
continue;

‎src/test/regress/expected/create_index.out

Lines changed: 54 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -2837,9 +2837,53 @@ explain (costs off)
28372837
REINDEX SCHEMA schema_to_reindex; -- failure, schema does not exist
28382838
ERROR: schema "schema_to_reindex" does not exist
28392839
CREATE SCHEMA schema_to_reindex;
2840-
CREATE TABLE schema_to_reindex.table1(col1 SERIAL PRIMARY KEY);
2841-
CREATE TABLE schema_to_reindex.table2(col1 SERIAL PRIMARY KEY, col2 VARCHAR(100) NOT NULL);
2842-
CREATE INDEX ON schema_to_reindex.table2(col2);
2840+
SET search_path = 'schema_to_reindex';
2841+
CREATE TABLE table1(col1 SERIAL PRIMARY KEY);
2842+
INSERT INTO table1 SELECT generate_series(1,400);
2843+
CREATE TABLE table2(col1 SERIAL PRIMARY KEY, col2 TEXT NOT NULL);
2844+
INSERT INTO table2 SELECT generate_series(1,400), 'abc';
2845+
CREATE INDEX ON table2(col2);
2846+
CREATE MATERIALIZED VIEW matview AS SELECT col1 FROM table2;
2847+
CREATE INDEX ON matview(col1);
2848+
CREATE VIEW view AS SELECT col2 FROM table2;
2849+
CREATE TABLE reindex_before AS
2850+
SELECT oid, relname, relfilenode, relkind, reltoastrelid
2851+
FROM pg_class
2852+
where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex');
2853+
INSERT INTO reindex_before
2854+
SELECT oid, 'pg_toast_TABLE', relfilenode, relkind, reltoastrelid
2855+
FROM pg_class WHERE oid IN
2856+
(SELECT reltoastrelid FROM reindex_before WHERE reltoastrelid > 0);
2857+
INSERT INTO reindex_before
2858+
SELECT oid, 'pg_toast_TABLE_index', relfilenode, relkind, reltoastrelid
2859+
FROM pg_class where oid in
2860+
(select indexrelid from pg_index where indrelid in
2861+
(select reltoastrelid from reindex_before where reltoastrelid > 0));
2862+
REINDEX SCHEMA schema_to_reindex;
2863+
CREATE TABLE reindex_after AS SELECT oid, relname, relfilenode, relkind
2864+
FROM pg_class
2865+
where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex');
2866+
SELECTb.relname,
2867+
b.relkind,
2868+
CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'
2869+
ELSE 'relfilenode has changed' END
2870+
FROM reindex_before b JOIN pg_class a ON b.oid = a.oid;
2871+
relname | relkind | case
2872+
----------------------+---------+--------------------------
2873+
table1_col1_seq | S | relfilenode is unchanged
2874+
table1 | r | relfilenode is unchanged
2875+
table1_pkey | i | relfilenode has changed
2876+
table2_col1_seq | S | relfilenode is unchanged
2877+
table2 | r | relfilenode is unchanged
2878+
table2_pkey | i | relfilenode has changed
2879+
table2_col2_idx | i | relfilenode has changed
2880+
matview | m | relfilenode is unchanged
2881+
matview_col1_idx | i | relfilenode has changed
2882+
view | v | relfilenode is unchanged
2883+
pg_toast_TABLE | t | relfilenode is unchanged
2884+
pg_toast_TABLE_index | i | relfilenode has changed
2885+
(12 rows)
2886+
28432887
REINDEX SCHEMA schema_to_reindex;
28442888
BEGIN;
28452889
REINDEX SCHEMA schema_to_reindex; -- failure, cannot run in a transaction
@@ -2854,6 +2898,10 @@ ERROR: must be owner of schema schema_to_reindex
28542898
RESET ROLE;
28552899
DROP ROLE regression_reindexuser;
28562900
DROP SCHEMA schema_to_reindex CASCADE;
2857-
NOTICE: drop cascades to 2 other objects
2858-
DETAIL: drop cascades to table schema_to_reindex.table1
2859-
drop cascades to table schema_to_reindex.table2
2901+
NOTICE: drop cascades to 6 other objects
2902+
DETAIL: drop cascades to table table1
2903+
drop cascades to table table2
2904+
drop cascades to materialized view matview
2905+
drop cascades to view view
2906+
drop cascades to table reindex_before
2907+
drop cascades to table reindex_after

‎src/test/regress/sql/create_index.sql

Lines changed: 31 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -970,9 +970,37 @@ explain (costs off)
970970
--
971971
REINDEX SCHEMA schema_to_reindex;-- failure, schema does not exist
972972
CREATESCHEMAschema_to_reindex;
973-
CREATETABLEschema_to_reindex.table1(col1SERIALPRIMARY KEY);
974-
CREATETABLEschema_to_reindex.table2(col1SERIALPRIMARY KEY, col2VARCHAR(100)NOT NULL);
975-
CREATEINDEXONschema_to_reindex.table2(col2);
973+
SET search_path='schema_to_reindex';
974+
CREATETABLEtable1(col1SERIALPRIMARY KEY);
975+
INSERT INTO table1SELECT generate_series(1,400);
976+
CREATETABLEtable2(col1SERIALPRIMARY KEY, col2TEXTNOT NULL);
977+
INSERT INTO table2SELECT generate_series(1,400),'abc';
978+
CREATEINDEXON table2(col2);
979+
CREATE MATERIALIZED VIEW matviewASSELECT col1FROM table2;
980+
CREATEINDEXON matview(col1);
981+
CREATEVIEWviewASSELECT col2FROM table2;
982+
CREATETABLEreindex_beforeAS
983+
SELECToid, relname, relfilenode, relkind, reltoastrelid
984+
FROM pg_class
985+
where relnamespace= (SELECToidFROM pg_namespaceWHERE nspname='schema_to_reindex');
986+
INSERT INTO reindex_before
987+
SELECToid,'pg_toast_TABLE', relfilenode, relkind, reltoastrelid
988+
FROM pg_classWHEREoidIN
989+
(SELECT reltoastrelidFROM reindex_beforeWHERE reltoastrelid>0);
990+
INSERT INTO reindex_before
991+
SELECToid,'pg_toast_TABLE_index', relfilenode, relkind, reltoastrelid
992+
FROM pg_classwhereoidin
993+
(select indexrelidfrom pg_indexwhere indrelidin
994+
(select reltoastrelidfrom reindex_beforewhere reltoastrelid>0));
995+
REINDEX SCHEMA schema_to_reindex;
996+
CREATETABLEreindex_afterASSELECToid, relname, relfilenode, relkind
997+
FROM pg_class
998+
where relnamespace= (SELECToidFROM pg_namespaceWHERE nspname='schema_to_reindex');
999+
SELECTb.relname,
1000+
b.relkind,
1001+
CASE WHENa.relfilenode=b.relfilenode THEN'relfilenode is unchanged'
1002+
ELSE'relfilenode has changed' END
1003+
FROM reindex_before bJOIN pg_class aONb.oid=a.oid;
9761004
REINDEX SCHEMA schema_to_reindex;
9771005
BEGIN;
9781006
REINDEX SCHEMA schema_to_reindex;-- failure, cannot run in a transaction

0 commit comments

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