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