Skip to content

Commit 2646d2d

Browse files
simonat2ndQuadrantsimonat2ndQuadrant
committed
Further changes to REINDEX SCHEMA
Ensure we reindex indexes built on Mat Views. Based on patch from Micheal Paquier Add thorough tests to check that indexes on tables, toast tables and mat views are reindexed. Simon Riggs
1 parent 0845264 commit 2646d2d

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_class classtuple = (Form_pg_class) GETSTRUCT(tuple);
18951895
Oid relid = 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+
SELECT b.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
CREATE SCHEMA schema_to_reindex;
973-
CREATE TABLE schema_to_reindex.table1(col1 SERIAL PRIMARY KEY);
974-
CREATE TABLE schema_to_reindex.table2(col1 SERIAL PRIMARY KEY, col2 VARCHAR(100) NOT NULL);
975-
CREATE INDEX ON schema_to_reindex.table2(col2);
973+
SET search_path = 'schema_to_reindex';
974+
CREATE TABLE table1(col1 SERIAL PRIMARY KEY);
975+
INSERT INTO table1 SELECT generate_series(1,400);
976+
CREATE TABLE table2(col1 SERIAL PRIMARY KEY, col2 TEXT NOT NULL);
977+
INSERT INTO table2 SELECT generate_series(1,400), 'abc';
978+
CREATE INDEX ON table2(col2);
979+
CREATE MATERIALIZED VIEW matview AS SELECT col1 FROM table2;
980+
CREATE INDEX ON matview(col1);
981+
CREATE VIEW view AS SELECT col2 FROM table2;
982+
CREATE TABLE reindex_before AS
983+
SELECT oid, relname, relfilenode, relkind, reltoastrelid
984+
FROM pg_class
985+
where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex');
986+
INSERT INTO reindex_before
987+
SELECT oid, 'pg_toast_TABLE', relfilenode, relkind, reltoastrelid
988+
FROM pg_class WHERE oid IN
989+
(SELECT reltoastrelid FROM reindex_before WHERE reltoastrelid > 0);
990+
INSERT INTO reindex_before
991+
SELECT oid, 'pg_toast_TABLE_index', relfilenode, relkind, reltoastrelid
992+
FROM pg_class where oid in
993+
(select indexrelid from pg_index where indrelid in
994+
(select reltoastrelid from reindex_before where reltoastrelid > 0));
995+
REINDEX SCHEMA schema_to_reindex;
996+
CREATE TABLE reindex_after AS SELECT oid, relname, relfilenode, relkind
997+
FROM pg_class
998+
where relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'schema_to_reindex');
999+
SELECT b.relname,
1000+
b.relkind,
1001+
CASE WHEN a.relfilenode = b.relfilenode THEN 'relfilenode is unchanged'
1002+
ELSE 'relfilenode has changed' END
1003+
FROM reindex_before b JOIN pg_class a ON b.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)