Skip to content

Commit df957a7

Browse files
bmomjianbmomjian
committed
In pg_upgrade, limit schema name filter to include toast tables. Bug
introduced recently when trying to filter out temp tables. Backpatch to 9.0 and 9.1.
1 parent 9354f5b commit df957a7

File tree

2 files changed

+30
-17
lines changed

2 files changed

+30
-17
lines changed

‎contrib/pg_upgrade/info.c

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -328,9 +328,11 @@ get_rel_infos(migratorContext *ctx, const DbInfo *dbinfo,
328328
" ON c.reltablespace = t.oid "
329329
"WHERE (( "
330330
/* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
331-
" n.nspname !~ '^pg_' "
332-
" AND n.nspname != 'information_schema' "
333-
" AND c.oid >= %u "
331+
" n.nspname != 'pg_catalog' "
332+
" AND n.nspname !~ '^pg_temp_' "
333+
" AND n.nspname !~ '^pg_toast_temp_' "
334+
" AND n.nspname != 'information_schema' "
335+
" AND c.oid >= %u "
334336
" ) OR ( "
335337
" n.nspname = 'pg_catalog' "
336338
" AND relname IN "

‎contrib/pg_upgrade/version_old_8_3.c

Lines changed: 25 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -61,9 +61,11 @@ old_8_3_check_for_name_data_type_usage(migratorContext *ctx, Cluster whichCluste
6161
" NOT a.attisdropped AND "
6262
" a.atttypid = 'pg_catalog.name'::pg_catalog.regtype AND "
6363
" c.relnamespace = n.oid AND "
64-
/* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
65-
" n.nspname !~ '^pg_' AND "
66-
" n.nspname != 'information_schema'");
64+
/* exclude possibly orphaned temp tables */
65+
" n.nspname != 'pg_catalog' AND "
66+
" n.nspname !~ '^pg_temp_' AND "
67+
" n.nspname !~ '^pg_toast_temp_' AND "
68+
" n.nspname != 'information_schema' ");
6769

6870
ntups = PQntuples(res);
6971
i_nspname = PQfnumber(res, "nspname");
@@ -152,9 +154,11 @@ old_8_3_check_for_tsquery_usage(migratorContext *ctx, Cluster whichCluster)
152154
" NOT a.attisdropped AND "
153155
" a.atttypid = 'pg_catalog.tsquery'::pg_catalog.regtype AND "
154156
" c.relnamespace = n.oid AND "
155-
/* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
156-
" n.nspname !~ '^pg_' AND "
157-
" n.nspname != 'information_schema'");
157+
/* exclude possibly orphaned temp tables */
158+
" n.nspname != 'pg_catalog' AND "
159+
" n.nspname !~ '^pg_temp_' AND "
160+
" n.nspname !~ '^pg_toast_temp_' AND "
161+
" n.nspname != 'information_schema' ");
158162

159163
ntups = PQntuples(res);
160164
i_nspname = PQfnumber(res, "nspname");
@@ -252,9 +256,11 @@ old_8_3_rebuild_tsvector_tables(migratorContext *ctx, bool check_mode,
252256
" NOT a.attisdropped AND "
253257
" a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND "
254258
" c.relnamespace = n.oid AND "
255-
/* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
256-
" n.nspname !~ '^pg_' AND "
257-
" n.nspname != 'information_schema'");
259+
/* exclude possibly orphaned temp tables */
260+
" n.nspname != 'pg_catalog' AND "
261+
" n.nspname !~ '^pg_temp_' AND "
262+
" n.nspname !~ '^pg_toast_temp_' AND "
263+
" n.nspname != 'information_schema' ");
258264

259265
/*
260266
* This macro is used below to avoid reindexing indexes already rebuilt
@@ -271,8 +277,10 @@ old_8_3_rebuild_tsvector_tables(migratorContext *ctx, bool check_mode,
271277
" NOT a.attisdropped AND " \
272278
" a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " \
273279
" c.relnamespace = n.oid AND " \
274-
" n.nspname !~ '^pg_' AND " \
275-
" n.nspname != 'information_schema') "
280+
" n.nspname != 'pg_catalog' AND " \
281+
" n.nspname !~ '^pg_temp_' AND " \
282+
" n.nspname !~ '^pg_toast_temp_' AND " \
283+
" n.nspname != 'information_schema')"
276284

277285
ntups = PQntuples(res);
278286
i_nspname = PQfnumber(res, "nspname");
@@ -641,9 +649,12 @@ old_8_3_create_sequence_script(migratorContext *ctx, Cluster whichCluster)
641649
" pg_catalog.pg_namespace n "
642650
"WHERE c.relkind = 'S' AND "
643651
" c.relnamespace = n.oid AND "
644-
/* exclude pg_catalog and pg_temp_ (could be orphaned tables) */
645-
" n.nspname !~ '^pg_' AND "
646-
" n.nspname != 'information_schema'");
652+
/* exclude possibly orphaned temp tables */
653+
" n.nspname != 'pg_catalog' AND "
654+
" n.nspname !~ '^pg_temp_' AND "
655+
" n.nspname !~ '^pg_toast_temp_' AND "
656+
" n.nspname != 'information_schema' ");
657+
647658

648659
ntups = PQntuples(res);
649660
i_nspname = PQfnumber(res, "nspname");

0 commit comments

Comments
 (0)