1 /*
2 * version.c
3 *
4 * Postgres-version-specific routines
5 *
6 * Copyright (c) 2010-2020, PostgreSQL Global Development Group
7 * src/bin/pg_upgrade/version.c
8 */
9
10 #include "postgres_fe.h"
11
12 #include "catalog/pg_class_d.h"
13 #include "fe_utils/string_utils.h"
14 #include "pg_upgrade.h"
15
16 /*
17 * new_9_0_populate_pg_largeobject_metadata()
18 * new >= 9.0, old <= 8.4
19 * 9.0 has a new pg_largeobject permission table
20 */
21 void
new_9_0_populate_pg_largeobject_metadata(ClusterInfo * cluster,bool check_mode)22 new_9_0_populate_pg_largeobject_metadata(ClusterInfo *cluster, bool check_mode)
23 {
24 int dbnum;
25 FILE *script = NULL;
26 bool found = false;
27 char output_path[MAXPGPATH];
28
29 prep_status("Checking for large objects");
30
31 snprintf(output_path, sizeof(output_path), "pg_largeobject.sql");
32
33 for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
34 {
35 PGresult *res;
36 int i_count;
37 DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
38 PGconn *conn = connectToServer(cluster, active_db->db_name);
39
40 /* find if there are any large objects */
41 res = executeQueryOrDie(conn,
42 "SELECT count(*) "
43 "FROM pg_catalog.pg_largeobject ");
44
45 i_count = PQfnumber(res, "count");
46 if (atoi(PQgetvalue(res, 0, i_count)) != 0)
47 {
48 found = true;
49 if (!check_mode)
50 {
51 PQExpBufferData connectbuf;
52
53 if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
54 pg_fatal("could not open file \"%s\": %s\n", output_path,
55 strerror(errno));
56
57 initPQExpBuffer(&connectbuf);
58 appendPsqlMetaConnect(&connectbuf, active_db->db_name);
59 fputs(connectbuf.data, script);
60 termPQExpBuffer(&connectbuf);
61
62 fprintf(script,
63 "SELECT pg_catalog.lo_create(t.loid)\n"
64 "FROM (SELECT DISTINCT loid FROM pg_catalog.pg_largeobject) AS t;\n");
65 }
66 }
67
68 PQclear(res);
69 PQfinish(conn);
70 }
71
72 if (script)
73 fclose(script);
74
75 if (found)
76 {
77 report_status(PG_WARNING, "warning");
78 if (check_mode)
79 pg_log(PG_WARNING, "\n"
80 "Your installation contains large objects. The new database has an\n"
81 "additional large object permission table. After upgrading, you will be\n"
82 "given a command to populate the pg_largeobject_metadata table with\n"
83 "default permissions.\n\n");
84 else
85 pg_log(PG_WARNING, "\n"
86 "Your installation contains large objects. The new database has an\n"
87 "additional large object permission table, so default permissions must be\n"
88 "defined for all large objects. The file\n"
89 " %s\n"
90 "when executed by psql by the database superuser will set the default\n"
91 "permissions.\n\n",
92 output_path);
93 }
94 else
95 check_ok();
96 }
97
98
99 /*
100 * check_for_data_types_usage()
101 * Detect whether there are any stored columns depending on given type(s)
102 *
103 * If so, write a report to the given file name, and return true.
104 *
105 * base_query should be a SELECT yielding a single column named "oid",
106 * containing the pg_type OIDs of one or more types that are known to have
107 * inconsistent on-disk representations across server versions.
108 *
109 * We check for the type(s) in tables, matviews, and indexes, but not views;
110 * there's no storage involved in a view.
111 */
112 bool
check_for_data_types_usage(ClusterInfo * cluster,const char * base_query,const char * output_path)113 check_for_data_types_usage(ClusterInfo *cluster,
114 const char *base_query,
115 const char *output_path)
116 {
117 bool found = false;
118 FILE *script = NULL;
119 int dbnum;
120
121 for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
122 {
123 DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
124 PGconn *conn = connectToServer(cluster, active_db->db_name);
125 PQExpBufferData querybuf;
126 PGresult *res;
127 bool db_used = false;
128 int ntups;
129 int rowno;
130 int i_nspname,
131 i_relname,
132 i_attname;
133
134 /*
135 * The type(s) of interest might be wrapped in a domain, array,
136 * composite, or range, and these container types can be nested (to
137 * varying extents depending on server version, but that's not of
138 * concern here). To handle all these cases we need a recursive CTE.
139 */
140 initPQExpBuffer(&querybuf);
141 appendPQExpBuffer(&querybuf,
142 "WITH RECURSIVE oids AS ( "
143 /* start with the type(s) returned by base_query */
144 " %s "
145 " UNION ALL "
146 " SELECT * FROM ( "
147 /* inner WITH because we can only reference the CTE once */
148 " WITH x AS (SELECT oid FROM oids) "
149 /* domains on any type selected so far */
150 " SELECT t.oid FROM pg_catalog.pg_type t, x WHERE typbasetype = x.oid AND typtype = 'd' "
151 " UNION ALL "
152 /* arrays over any type selected so far */
153 " SELECT t.oid FROM pg_catalog.pg_type t, x WHERE typelem = x.oid AND typtype = 'b' "
154 " UNION ALL "
155 /* composite types containing any type selected so far */
156 " SELECT t.oid FROM pg_catalog.pg_type t, pg_catalog.pg_class c, pg_catalog.pg_attribute a, x "
157 " WHERE t.typtype = 'c' AND "
158 " t.oid = c.reltype AND "
159 " c.oid = a.attrelid AND "
160 " NOT a.attisdropped AND "
161 " a.atttypid = x.oid ",
162 base_query);
163
164 /* Ranges were introduced in 9.2 */
165 if (GET_MAJOR_VERSION(cluster->major_version) >= 902)
166 appendPQExpBuffer(&querybuf,
167 " UNION ALL "
168 /* ranges containing any type selected so far */
169 " SELECT t.oid FROM pg_catalog.pg_type t, pg_catalog.pg_range r, x "
170 " WHERE t.typtype = 'r' AND r.rngtypid = t.oid AND r.rngsubtype = x.oid");
171
172 appendPQExpBuffer(&querybuf,
173 " ) foo "
174 ") "
175 /* now look for stored columns of any such type */
176 "SELECT n.nspname, c.relname, a.attname "
177 "FROM pg_catalog.pg_class c, "
178 " pg_catalog.pg_namespace n, "
179 " pg_catalog.pg_attribute a "
180 "WHERE c.oid = a.attrelid AND "
181 " NOT a.attisdropped AND "
182 " a.atttypid IN (SELECT oid FROM oids) AND "
183 " c.relkind IN ("
184 CppAsString2(RELKIND_RELATION) ", "
185 CppAsString2(RELKIND_MATVIEW) ", "
186 CppAsString2(RELKIND_INDEX) ") AND "
187 " c.relnamespace = n.oid AND "
188 /* exclude possible orphaned temp tables */
189 " n.nspname !~ '^pg_temp_' AND "
190 " n.nspname !~ '^pg_toast_temp_' AND "
191 /* exclude system catalogs, too */
192 " n.nspname NOT IN ('pg_catalog', 'information_schema')");
193
194 res = executeQueryOrDie(conn, "%s", querybuf.data);
195
196 ntups = PQntuples(res);
197 i_nspname = PQfnumber(res, "nspname");
198 i_relname = PQfnumber(res, "relname");
199 i_attname = PQfnumber(res, "attname");
200 for (rowno = 0; rowno < ntups; rowno++)
201 {
202 found = true;
203 if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
204 pg_fatal("could not open file \"%s\": %s\n", output_path,
205 strerror(errno));
206 if (!db_used)
207 {
208 fprintf(script, "In database: %s\n", active_db->db_name);
209 db_used = true;
210 }
211 fprintf(script, " %s.%s.%s\n",
212 PQgetvalue(res, rowno, i_nspname),
213 PQgetvalue(res, rowno, i_relname),
214 PQgetvalue(res, rowno, i_attname));
215 }
216
217 PQclear(res);
218
219 termPQExpBuffer(&querybuf);
220
221 PQfinish(conn);
222 }
223
224 if (script)
225 fclose(script);
226
227 return found;
228 }
229
230 /*
231 * check_for_data_type_usage()
232 * Detect whether there are any stored columns depending on the given type
233 *
234 * If so, write a report to the given file name, and return true.
235 *
236 * type_name should be a fully qualified type name. This is just a
237 * trivial wrapper around check_for_data_types_usage() to convert a
238 * type name into a base query.
239 */
240 bool
check_for_data_type_usage(ClusterInfo * cluster,const char * type_name,const char * output_path)241 check_for_data_type_usage(ClusterInfo *cluster,
242 const char *type_name,
243 const char *output_path)
244 {
245 bool found;
246 char *base_query;
247
248 base_query = psprintf("SELECT '%s'::pg_catalog.regtype AS oid",
249 type_name);
250
251 found = check_for_data_types_usage(cluster, base_query, output_path);
252
253 free(base_query);
254
255 return found;
256 }
257
258
259 /*
260 * old_9_3_check_for_line_data_type_usage()
261 * 9.3 -> 9.4
262 * Fully implement the 'line' data type in 9.4, which previously returned
263 * "not enabled" by default and was only functionally enabled with a
264 * compile-time switch; as of 9.4 "line" has a different on-disk
265 * representation format.
266 */
267 void
old_9_3_check_for_line_data_type_usage(ClusterInfo * cluster)268 old_9_3_check_for_line_data_type_usage(ClusterInfo *cluster)
269 {
270 char output_path[MAXPGPATH];
271
272 prep_status("Checking for incompatible \"line\" data type");
273
274 snprintf(output_path, sizeof(output_path), "tables_using_line.txt");
275
276 if (check_for_data_type_usage(cluster, "pg_catalog.line", output_path))
277 {
278 pg_log(PG_REPORT, "fatal\n");
279 pg_fatal("Your installation contains the \"line\" data type in user tables. This\n"
280 "data type changed its internal and input/output format between your old\n"
281 "and new clusters so this cluster cannot currently be upgraded. You can\n"
282 "remove the problem tables and restart the upgrade. A list of the problem\n"
283 "columns is in the file:\n"
284 " %s\n\n", output_path);
285 }
286 else
287 check_ok();
288 }
289
290
291 /*
292 * old_9_6_check_for_unknown_data_type_usage()
293 * 9.6 -> 10
294 * It's no longer allowed to create tables or views with "unknown"-type
295 * columns. We do not complain about views with such columns, because
296 * they should get silently converted to "text" columns during the DDL
297 * dump and reload; it seems unlikely to be worth making users do that
298 * by hand. However, if there's a table with such a column, the DDL
299 * reload will fail, so we should pre-detect that rather than failing
300 * mid-upgrade. Worse, if there's a matview with such a column, the
301 * DDL reload will silently change it to "text" which won't match the
302 * on-disk storage (which is like "cstring"). So we *must* reject that.
303 */
304 void
old_9_6_check_for_unknown_data_type_usage(ClusterInfo * cluster)305 old_9_6_check_for_unknown_data_type_usage(ClusterInfo *cluster)
306 {
307 char output_path[MAXPGPATH];
308
309 prep_status("Checking for invalid \"unknown\" user columns");
310
311 snprintf(output_path, sizeof(output_path), "tables_using_unknown.txt");
312
313 if (check_for_data_type_usage(cluster, "pg_catalog.unknown", output_path))
314 {
315 pg_log(PG_REPORT, "fatal\n");
316 pg_fatal("Your installation contains the \"unknown\" data type in user tables. This\n"
317 "data type is no longer allowed in tables, so this cluster cannot currently\n"
318 "be upgraded. You can remove the problem tables and restart the upgrade.\n"
319 "A list of the problem columns is in the file:\n"
320 " %s\n\n", output_path);
321 }
322 else
323 check_ok();
324 }
325
326 /*
327 * old_9_6_invalidate_hash_indexes()
328 * 9.6 -> 10
329 * Hash index binary format has changed from 9.6->10.0
330 */
331 void
old_9_6_invalidate_hash_indexes(ClusterInfo * cluster,bool check_mode)332 old_9_6_invalidate_hash_indexes(ClusterInfo *cluster, bool check_mode)
333 {
334 int dbnum;
335 FILE *script = NULL;
336 bool found = false;
337 char *output_path = "reindex_hash.sql";
338
339 prep_status("Checking for hash indexes");
340
341 for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
342 {
343 PGresult *res;
344 bool db_used = false;
345 int ntups;
346 int rowno;
347 int i_nspname,
348 i_relname;
349 DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
350 PGconn *conn = connectToServer(cluster, active_db->db_name);
351
352 /* find hash indexes */
353 res = executeQueryOrDie(conn,
354 "SELECT n.nspname, c.relname "
355 "FROM pg_catalog.pg_class c, "
356 " pg_catalog.pg_index i, "
357 " pg_catalog.pg_am a, "
358 " pg_catalog.pg_namespace n "
359 "WHERE i.indexrelid = c.oid AND "
360 " c.relam = a.oid AND "
361 " c.relnamespace = n.oid AND "
362 " a.amname = 'hash'"
363 );
364
365 ntups = PQntuples(res);
366 i_nspname = PQfnumber(res, "nspname");
367 i_relname = PQfnumber(res, "relname");
368 for (rowno = 0; rowno < ntups; rowno++)
369 {
370 found = true;
371 if (!check_mode)
372 {
373 if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
374 pg_fatal("could not open file \"%s\": %s\n", output_path,
375 strerror(errno));
376 if (!db_used)
377 {
378 PQExpBufferData connectbuf;
379
380 initPQExpBuffer(&connectbuf);
381 appendPsqlMetaConnect(&connectbuf, active_db->db_name);
382 fputs(connectbuf.data, script);
383 termPQExpBuffer(&connectbuf);
384 db_used = true;
385 }
386 fprintf(script, "REINDEX INDEX %s.%s;\n",
387 quote_identifier(PQgetvalue(res, rowno, i_nspname)),
388 quote_identifier(PQgetvalue(res, rowno, i_relname)));
389 }
390 }
391
392 PQclear(res);
393
394 if (!check_mode && db_used)
395 {
396 /* mark hash indexes as invalid */
397 PQclear(executeQueryOrDie(conn,
398 "UPDATE pg_catalog.pg_index i "
399 "SET indisvalid = false "
400 "FROM pg_catalog.pg_class c, "
401 " pg_catalog.pg_am a, "
402 " pg_catalog.pg_namespace n "
403 "WHERE i.indexrelid = c.oid AND "
404 " c.relam = a.oid AND "
405 " c.relnamespace = n.oid AND "
406 " a.amname = 'hash'"));
407 }
408
409 PQfinish(conn);
410 }
411
412 if (script)
413 fclose(script);
414
415 if (found)
416 {
417 report_status(PG_WARNING, "warning");
418 if (check_mode)
419 pg_log(PG_WARNING, "\n"
420 "Your installation contains hash indexes. These indexes have different\n"
421 "internal formats between your old and new clusters, so they must be\n"
422 "reindexed with the REINDEX command. After upgrading, you will be given\n"
423 "REINDEX instructions.\n\n");
424 else
425 pg_log(PG_WARNING, "\n"
426 "Your installation contains hash indexes. These indexes have different\n"
427 "internal formats between your old and new clusters, so they must be\n"
428 "reindexed with the REINDEX command. The file\n"
429 " %s\n"
430 "when executed by psql by the database superuser will recreate all invalid\n"
431 "indexes; until then, none of these indexes will be used.\n\n",
432 output_path);
433 }
434 else
435 check_ok();
436 }
437
438 /*
439 * old_11_check_for_sql_identifier_data_type_usage()
440 * 11 -> 12
441 * In 12, the sql_identifier data type was switched from name to varchar,
442 * which does affect the storage (name is by-ref, but not varlena). This
443 * means user tables using sql_identifier for columns are broken because
444 * the on-disk format is different.
445 */
446 void
old_11_check_for_sql_identifier_data_type_usage(ClusterInfo * cluster)447 old_11_check_for_sql_identifier_data_type_usage(ClusterInfo *cluster)
448 {
449 char output_path[MAXPGPATH];
450
451 prep_status("Checking for invalid \"sql_identifier\" user columns");
452
453 snprintf(output_path, sizeof(output_path), "tables_using_sql_identifier.txt");
454
455 if (check_for_data_type_usage(cluster, "information_schema.sql_identifier",
456 output_path))
457 {
458 pg_log(PG_REPORT, "fatal\n");
459 pg_fatal("Your installation contains the \"sql_identifier\" data type in user tables\n"
460 "and/or indexes. The on-disk format for this data type has changed, so this\n"
461 "cluster cannot currently be upgraded. You can remove the problem tables or\n"
462 "change the data type to \"name\" and restart the upgrade.\n"
463 "A list of the problem columns is in the file:\n"
464 " %s\n\n", output_path);
465 }
466 else
467 check_ok();
468 }
469
470
471 /*
472 * report_extension_updates()
473 * Report extensions that should be updated.
474 */
475 void
report_extension_updates(ClusterInfo * cluster)476 report_extension_updates(ClusterInfo *cluster)
477 {
478 int dbnum;
479 FILE *script = NULL;
480 bool found = false;
481 char *output_path = "update_extensions.sql";
482
483 prep_status("Checking for extension updates");
484
485 for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
486 {
487 PGresult *res;
488 bool db_used = false;
489 int ntups;
490 int rowno;
491 int i_name;
492 DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
493 PGconn *conn = connectToServer(cluster, active_db->db_name);
494
495 /* find extensions needing updates */
496 res = executeQueryOrDie(conn,
497 "SELECT name "
498 "FROM pg_available_extensions "
499 "WHERE installed_version != default_version"
500 );
501
502 ntups = PQntuples(res);
503 i_name = PQfnumber(res, "name");
504 for (rowno = 0; rowno < ntups; rowno++)
505 {
506 found = true;
507
508 if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
509 pg_fatal("could not open file \"%s\": %s\n", output_path,
510 strerror(errno));
511 if (!db_used)
512 {
513 PQExpBufferData connectbuf;
514
515 initPQExpBuffer(&connectbuf);
516 appendPsqlMetaConnect(&connectbuf, active_db->db_name);
517 fputs(connectbuf.data, script);
518 termPQExpBuffer(&connectbuf);
519 db_used = true;
520 }
521 fprintf(script, "ALTER EXTENSION %s UPDATE;\n",
522 quote_identifier(PQgetvalue(res, rowno, i_name)));
523 }
524
525 PQclear(res);
526
527 PQfinish(conn);
528 }
529
530 if (script)
531 fclose(script);
532
533 if (found)
534 {
535 report_status(PG_REPORT, "notice");
536 pg_log(PG_REPORT, "\n"
537 "Your installation contains extensions that should be updated\n"
538 "with the ALTER EXTENSION command. The file\n"
539 " %s\n"
540 "when executed by psql by the database superuser will update\n"
541 "these extensions.\n\n",
542 output_path);
543 }
544 else
545 check_ok();
546 }
547