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