PostgreSQL Source Code  git master
version.c
Go to the documentation of this file.
1 /*
2  * version.c
3  *
4  * Postgres-version-specific routines
5  *
6  * Copyright (c) 2010-2022, 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
32  const char *base_query,
33  const char *output_path)
34 {
35  bool found = false;
36  FILE *script = NULL;
37  int dbnum;
38 
39  for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
40  {
41  DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
42  PGconn *conn = connectToServer(cluster, active_db->db_name);
43  PQExpBufferData querybuf;
44  PGresult *res;
45  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  */
58  initPQExpBuffer(&querybuf);
59  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  res = executeQueryOrDie(conn, "%s", querybuf.data);
107 
108  ntups = PQntuples(res);
109  i_nspname = PQfnumber(res, "nspname");
110  i_relname = PQfnumber(res, "relname");
111  i_attname = PQfnumber(res, "attname");
112  for (rowno = 0; rowno < ntups; rowno++)
113  {
114  found = true;
115  if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
116  pg_fatal("could not open file \"%s\": %s\n", output_path,
117  strerror(errno));
118  if (!db_used)
119  {
120  fprintf(script, "In database: %s\n", active_db->db_name);
121  db_used = true;
122  }
123  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 
129  PQclear(res);
130 
131  termPQExpBuffer(&querybuf);
132 
133  PQfinish(conn);
134  }
135 
136  if (script)
137  fclose(script);
138 
139  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
154  const char *type_name,
155  const char *output_path)
156 {
157  bool found;
158  char *base_query;
159 
160  base_query = psprintf("SELECT '%s'::pg_catalog.regtype AS oid",
161  type_name);
162 
163  found = check_for_data_types_usage(cluster, base_query, output_path);
164 
165  free(base_query);
166 
167  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
181 {
182  char output_path[MAXPGPATH];
183 
184  prep_status("Checking for incompatible \"line\" data type");
185 
186  snprintf(output_path, sizeof(output_path), "tables_using_line.txt");
187 
188  if (check_for_data_type_usage(cluster, "pg_catalog.line", output_path))
189  {
190  pg_log(PG_REPORT, "fatal\n");
191  pg_fatal("Your installation contains the \"line\" data type in user tables.\n"
192  "This data type changed its internal and input/output format\n"
193  "between your old and new versions so this\n"
194  "cluster cannot currently be upgraded. You can\n"
195  "drop the problem columns and restart the upgrade.\n"
196  "A list of the problem columns is in the file:\n"
197  " %s\n\n", output_path);
198  }
199  else
200  check_ok();
201 }
202 
203 
204 /*
205  * old_9_6_check_for_unknown_data_type_usage()
206  * 9.6 -> 10
207  * It's no longer allowed to create tables or views with "unknown"-type
208  * columns. We do not complain about views with such columns, because
209  * they should get silently converted to "text" columns during the DDL
210  * dump and reload; it seems unlikely to be worth making users do that
211  * by hand. However, if there's a table with such a column, the DDL
212  * reload will fail, so we should pre-detect that rather than failing
213  * mid-upgrade. Worse, if there's a matview with such a column, the
214  * DDL reload will silently change it to "text" which won't match the
215  * on-disk storage (which is like "cstring"). So we *must* reject that.
216  */
217 void
219 {
220  char output_path[MAXPGPATH];
221 
222  prep_status("Checking for invalid \"unknown\" user columns");
223 
224  snprintf(output_path, sizeof(output_path), "tables_using_unknown.txt");
225 
226  if (check_for_data_type_usage(cluster, "pg_catalog.unknown", output_path))
227  {
228  pg_log(PG_REPORT, "fatal\n");
229  pg_fatal("Your installation contains the \"unknown\" data type in user tables.\n"
230  "This data type is no longer allowed in tables, so this\n"
231  "cluster cannot currently be upgraded. You can\n"
232  "drop the problem columns and restart the upgrade.\n"
233  "A list of the problem columns is in the file:\n"
234  " %s\n\n", output_path);
235  }
236  else
237  check_ok();
238 }
239 
240 /*
241  * old_9_6_invalidate_hash_indexes()
242  * 9.6 -> 10
243  * Hash index binary format has changed from 9.6->10.0
244  */
245 void
247 {
248  int dbnum;
249  FILE *script = NULL;
250  bool found = false;
251  char *output_path = "reindex_hash.sql";
252 
253  prep_status("Checking for hash indexes");
254 
255  for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
256  {
257  PGresult *res;
258  bool db_used = false;
259  int ntups;
260  int rowno;
261  int i_nspname,
262  i_relname;
263  DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
264  PGconn *conn = connectToServer(cluster, active_db->db_name);
265 
266  /* find hash indexes */
268  "SELECT n.nspname, c.relname "
269  "FROM pg_catalog.pg_class c, "
270  " pg_catalog.pg_index i, "
271  " pg_catalog.pg_am a, "
272  " pg_catalog.pg_namespace n "
273  "WHERE i.indexrelid = c.oid AND "
274  " c.relam = a.oid AND "
275  " c.relnamespace = n.oid AND "
276  " a.amname = 'hash'"
277  );
278 
279  ntups = PQntuples(res);
280  i_nspname = PQfnumber(res, "nspname");
281  i_relname = PQfnumber(res, "relname");
282  for (rowno = 0; rowno < ntups; rowno++)
283  {
284  found = true;
285  if (!check_mode)
286  {
287  if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
288  pg_fatal("could not open file \"%s\": %s\n", output_path,
289  strerror(errno));
290  if (!db_used)
291  {
292  PQExpBufferData connectbuf;
293 
294  initPQExpBuffer(&connectbuf);
295  appendPsqlMetaConnect(&connectbuf, active_db->db_name);
296  fputs(connectbuf.data, script);
297  termPQExpBuffer(&connectbuf);
298  db_used = true;
299  }
300  fprintf(script, "REINDEX INDEX %s.%s;\n",
301  quote_identifier(PQgetvalue(res, rowno, i_nspname)),
302  quote_identifier(PQgetvalue(res, rowno, i_relname)));
303  }
304  }
305 
306  PQclear(res);
307 
308  if (!check_mode && db_used)
309  {
310  /* mark hash indexes as invalid */
312  "UPDATE pg_catalog.pg_index i "
313  "SET indisvalid = false "
314  "FROM pg_catalog.pg_class c, "
315  " pg_catalog.pg_am a, "
316  " pg_catalog.pg_namespace n "
317  "WHERE i.indexrelid = c.oid AND "
318  " c.relam = a.oid AND "
319  " c.relnamespace = n.oid AND "
320  " a.amname = 'hash'"));
321  }
322 
323  PQfinish(conn);
324  }
325 
326  if (script)
327  fclose(script);
328 
329  if (found)
330  {
331  report_status(PG_WARNING, "warning");
332  if (check_mode)
333  pg_log(PG_WARNING, "\n"
334  "Your installation contains hash indexes. These indexes have different\n"
335  "internal formats between your old and new clusters, so they must be\n"
336  "reindexed with the REINDEX command. After upgrading, you will be given\n"
337  "REINDEX instructions.\n\n");
338  else
339  pg_log(PG_WARNING, "\n"
340  "Your installation contains hash indexes. These indexes have different\n"
341  "internal formats between your old and new clusters, so they must be\n"
342  "reindexed with the REINDEX command. The file\n"
343  " %s\n"
344  "when executed by psql by the database superuser will recreate all invalid\n"
345  "indexes; until then, none of these indexes will be used.\n\n",
346  output_path);
347  }
348  else
349  check_ok();
350 }
351 
352 /*
353  * old_11_check_for_sql_identifier_data_type_usage()
354  * 11 -> 12
355  * In 12, the sql_identifier data type was switched from name to varchar,
356  * which does affect the storage (name is by-ref, but not varlena). This
357  * means user tables using sql_identifier for columns are broken because
358  * the on-disk format is different.
359  */
360 void
362 {
363  char output_path[MAXPGPATH];
364 
365  prep_status("Checking for invalid \"sql_identifier\" user columns");
366 
367  snprintf(output_path, sizeof(output_path), "tables_using_sql_identifier.txt");
368 
369  if (check_for_data_type_usage(cluster, "information_schema.sql_identifier",
370  output_path))
371  {
372  pg_log(PG_REPORT, "fatal\n");
373  pg_fatal("Your installation contains the \"sql_identifier\" data type in user tables.\n"
374  "The on-disk format for this data type has changed, so this\n"
375  "cluster cannot currently be upgraded. You can\n"
376  "drop the problem columns and restart the upgrade.\n"
377  "A list of the problem columns is in the file:\n"
378  " %s\n\n", output_path);
379  }
380  else
381  check_ok();
382 }
383 
384 
385 /*
386  * report_extension_updates()
387  * Report extensions that should be updated.
388  */
389 void
391 {
392  int dbnum;
393  FILE *script = NULL;
394  bool found = false;
395  char *output_path = "update_extensions.sql";
396 
397  prep_status("Checking for extension updates");
398 
399  for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
400  {
401  PGresult *res;
402  bool db_used = false;
403  int ntups;
404  int rowno;
405  int i_name;
406  DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
407  PGconn *conn = connectToServer(cluster, active_db->db_name);
408 
409  /* find extensions needing updates */
411  "SELECT name "
412  "FROM pg_available_extensions "
413  "WHERE installed_version != default_version"
414  );
415 
416  ntups = PQntuples(res);
417  i_name = PQfnumber(res, "name");
418  for (rowno = 0; rowno < ntups; rowno++)
419  {
420  found = true;
421 
422  if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
423  pg_fatal("could not open file \"%s\": %s\n", output_path,
424  strerror(errno));
425  if (!db_used)
426  {
427  PQExpBufferData connectbuf;
428 
429  initPQExpBuffer(&connectbuf);
430  appendPsqlMetaConnect(&connectbuf, active_db->db_name);
431  fputs(connectbuf.data, script);
432  termPQExpBuffer(&connectbuf);
433  db_used = true;
434  }
435  fprintf(script, "ALTER EXTENSION %s UPDATE;\n",
436  quote_identifier(PQgetvalue(res, rowno, i_name)));
437  }
438 
439  PQclear(res);
440 
441  PQfinish(conn);
442  }
443 
444  if (script)
445  fclose(script);
446 
447  if (found)
448  {
449  report_status(PG_REPORT, "notice");
450  pg_log(PG_REPORT, "\n"
451  "Your installation contains extensions that should be updated\n"
452  "with the ALTER EXTENSION command. The file\n"
453  " %s\n"
454  "when executed by psql by the database superuser will update\n"
455  "these extensions.\n\n",
456  output_path);
457  }
458  else
459  check_ok();
460 }
bool check_for_data_types_usage(ClusterInfo *cluster, const char *base_query, const char *output_path)
Definition: version.c:31
void old_11_check_for_sql_identifier_data_type_usage(ClusterInfo *cluster)
Definition: version.c:361
bool check_for_data_type_usage(ClusterInfo *cluster, const char *type_name, const char *output_path)
Definition: version.c:153
void report_extension_updates(ClusterInfo *cluster)
Definition: version.c:390
void old_9_3_check_for_line_data_type_usage(ClusterInfo *cluster)
Definition: version.c:180
void old_9_6_check_for_unknown_data_type_usage(ClusterInfo *cluster)
Definition: version.c:218
void old_9_6_invalidate_hash_indexes(ClusterInfo *cluster, bool check_mode)
Definition: version.c:246
#define CppAsString2(x)
Definition: c.h:289
void cluster(ParseState *pstate, ClusterStmt *stmt, bool isTopLevel)
Definition: cluster.c:108
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4261
void PQclear(PGresult *res)
Definition: fe-exec.c:718
int PQntuples(const PGresult *res)
Definition: fe-exec.c:3340
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3735
int PQfnumber(const PGresult *res, const char *field_name)
Definition: fe-exec.c:3448
#define free(a)
Definition: header.h:65
static void check_ok(void)
Definition: initdb.c:1908
#define pg_fatal(...)
#define MAXPGPATH
void void pg_log(eLogType type, const char *fmt,...) pg_attribute_printf(2
#define fopen_priv(path, mode)
Definition: pg_upgrade.h:380
@ PG_WARNING
Definition: pg_upgrade.h:238
@ PG_REPORT
Definition: pg_upgrade.h:237
PGconn * connectToServer(ClusterInfo *cluster, const char *db_name)
Definition: server.c:28
void prep_status(const char *fmt,...) pg_attribute_printf(1
void report_status(eLogType type, const char *fmt,...) pg_attribute_printf(2
PGresult * executeQueryOrDie(PGconn *conn, const char *fmt,...) pg_attribute_printf(2
#define strerror
Definition: port.h:238
#define snprintf
Definition: port.h:225
#define fprintf
Definition: port.h:229
void initPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:92
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:267
void termPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:131
char * psprintf(const char *fmt,...)
Definition: psprintf.c:46
const char * quote_identifier(const char *ident)
Definition: ruleutils.c:12163
PGconn * conn
Definition: streamutil.c:54
void appendPsqlMetaConnect(PQExpBuffer buf, const char *dbname)
Definition: string_utils.c:590
char * db_name
Definition: pg_upgrade.h:172