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

Commita95410e

Browse files
committed
pg_upgrade: Handle hash index upgrades more smoothly.
Mark any old hash indexes as invalid so that they don't get used, andcreate a script to run REINDEX on all of them. Without this, we'dstill try to use any upgraded hash indexes, but it would fail.Amit Kapila, reviewed by me. Per a suggestion from Tom Lane.Discussion:http://postgr.es/m/CAA4eK1Jidtagm7Q81q-WoegOVgkotv0OxvHOjFxcvFRP4X=mSw@mail.gmail.com
1 parente807d8b commita95410e

File tree

3 files changed

+130
-1
lines changed

3 files changed

+130
-1
lines changed

‎src/bin/pg_upgrade/check.c

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -98,9 +98,16 @@ check_and_dump_old_cluster(bool live_check)
9898
check_for_reg_data_type_usage(&old_cluster);
9999
check_for_isn_and_int8_passing_mismatch(&old_cluster);
100100

101-
/* Pre-PG 10 allowed tables with 'unknown' type columns */
101+
/*
102+
* Pre-PG 10 allowed tables with 'unknown' type columns and non WAL logged
103+
* hash indexes
104+
*/
102105
if (GET_MAJOR_VERSION(old_cluster.major_version) <=906)
106+
{
103107
old_9_6_check_for_unknown_data_type_usage(&old_cluster);
108+
if (user_opts.check)
109+
old_9_6_invalidate_hash_indexes(&old_cluster, true);
110+
}
104111

105112
/* 9.5 and below should not have roles starting with pg_ */
106113
if (GET_MAJOR_VERSION(old_cluster.major_version) <=905)
@@ -176,6 +183,14 @@ issue_warnings(void)
176183
new_9_0_populate_pg_largeobject_metadata(&new_cluster, false);
177184
stop_postmaster(false);
178185
}
186+
187+
/* Reindex hash indexes for old < 10.0 */
188+
if (GET_MAJOR_VERSION(old_cluster.major_version) <=906)
189+
{
190+
start_postmaster(&new_cluster, true);
191+
old_9_6_invalidate_hash_indexes(&new_cluster, false);
192+
stop_postmaster(false);
193+
}
179194
}
180195

181196

‎src/bin/pg_upgrade/pg_upgrade.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -441,6 +441,8 @@ void new_9_0_populate_pg_largeobject_metadata(ClusterInfo *cluster,
441441
boolcheck_mode);
442442
voidold_9_3_check_for_line_data_type_usage(ClusterInfo*cluster);
443443
voidold_9_6_check_for_unknown_data_type_usage(ClusterInfo*cluster);
444+
voidold_9_6_invalidate_hash_indexes(ClusterInfo*cluster,
445+
boolcheck_mode);
444446

445447
/* parallel.c */
446448
voidparallel_exec_prog(constchar*log_file,constchar*opt_log_file,

‎src/bin/pg_upgrade/version.c

Lines changed: 112 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -287,3 +287,115 @@ old_9_6_check_for_unknown_data_type_usage(ClusterInfo *cluster)
287287
else
288288
check_ok();
289289
}
290+
291+
/*
292+
* old_9_6_invalidate_hash_indexes()
293+
*9.6 -> 10
294+
*Hash index binary format has changed from 9.6->10.0
295+
*/
296+
void
297+
old_9_6_invalidate_hash_indexes(ClusterInfo*cluster,boolcheck_mode)
298+
{
299+
intdbnum;
300+
FILE*script=NULL;
301+
boolfound= false;
302+
char*output_path="reindex_hash.sql";
303+
304+
prep_status("Checking for hash indexes");
305+
306+
for (dbnum=0;dbnum<cluster->dbarr.ndbs;dbnum++)
307+
{
308+
PGresult*res;
309+
booldb_used= false;
310+
intntups;
311+
introwno;
312+
inti_nspname,
313+
i_relname;
314+
DbInfo*active_db=&cluster->dbarr.dbs[dbnum];
315+
PGconn*conn=connectToServer(cluster,active_db->db_name);
316+
317+
/* find hash indexes */
318+
res=executeQueryOrDie(conn,
319+
"SELECT n.nspname, c.relname "
320+
"FROMpg_catalog.pg_class c, "
321+
"pg_catalog.pg_index i, "
322+
"pg_catalog.pg_am a, "
323+
"pg_catalog.pg_namespace n "
324+
"WHEREi.indexrelid = c.oid AND "
325+
"c.relam = a.oid AND "
326+
"c.relnamespace = n.oid AND "
327+
"a.amname = 'hash'"
328+
);
329+
330+
ntups=PQntuples(res);
331+
i_nspname=PQfnumber(res,"nspname");
332+
i_relname=PQfnumber(res,"relname");
333+
for (rowno=0;rowno<ntups;rowno++)
334+
{
335+
found= true;
336+
if (!check_mode)
337+
{
338+
if (script==NULL&& (script=fopen_priv(output_path,"w"))==NULL)
339+
pg_fatal("could not open file \"%s\": %s\n",output_path,
340+
strerror(errno));
341+
if (!db_used)
342+
{
343+
PQExpBufferDataconnectbuf;
344+
345+
initPQExpBuffer(&connectbuf);
346+
appendPsqlMetaConnect(&connectbuf,active_db->db_name);
347+
fputs(connectbuf.data,script);
348+
termPQExpBuffer(&connectbuf);
349+
db_used= true;
350+
}
351+
fprintf(script,"REINDEX INDEX %s.%s;\n",
352+
quote_identifier(PQgetvalue(res,rowno,i_nspname)),
353+
quote_identifier(PQgetvalue(res,rowno,i_relname)));
354+
}
355+
}
356+
357+
PQclear(res);
358+
359+
if (!check_mode&&db_used)
360+
{
361+
/* mark hash indexes as invalid */
362+
PQclear(executeQueryOrDie(conn,
363+
"UPDATE pg_catalog.pg_index i "
364+
"SETindisvalid = false "
365+
"FROMpg_catalog.pg_class c, "
366+
"pg_catalog.pg_am a, "
367+
"pg_catalog.pg_namespace n "
368+
"WHEREi.indexrelid = c.oid AND "
369+
"c.relam = a.oid AND "
370+
"c.relnamespace = n.oid AND "
371+
"a.amname = 'hash'"));
372+
}
373+
374+
PQfinish(conn);
375+
}
376+
377+
if (script)
378+
fclose(script);
379+
380+
if (found)
381+
{
382+
report_status(PG_WARNING,"warning");
383+
if (check_mode)
384+
pg_log(PG_WARNING,"\n"
385+
"Your installation contains hash indexes. These indexes have different\n"
386+
"internal formats between your old and new clusters, so they must be\n"
387+
"reindexed with the REINDEX command. After upgrading, you will be given\n"
388+
"REINDEX instructions.\n\n");
389+
else
390+
pg_log(PG_WARNING,"\n"
391+
"Your installation contains hash indexes. These indexes have different\n"
392+
"internal formats between your old and new clusters, so they must be\n"
393+
"reindexed with the REINDEX command. The file:\n"
394+
" %s\n"
395+
"when executed by psql by the database superuser will recreate all invalid\n"
396+
"indexes; until then, none of these indexes will be used.\n\n",
397+
output_path);
398+
}
399+
else
400+
check_ok();
401+
}

0 commit comments

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