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-2019, 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
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_type_usage
101  * Detect whether there are any stored columns depending on the given type
102  *
103  * If so, write a report to the given file name, and return true.
104  *
105  * We check for the type in tables, matviews, and indexes, but not views;
106  * there's no storage involved in a view.
107  */
108 static bool
110  char *output_path)
111 {
112  bool found = false;
113  FILE *script = NULL;
114  int dbnum;
115 
116  for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
117  {
118  DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
119  PGconn *conn = connectToServer(cluster, active_db->db_name);
120  PQExpBufferData querybuf;
121  PGresult *res;
122  bool db_used = false;
123  int ntups;
124  int rowno;
125  int i_nspname,
126  i_relname,
127  i_attname;
128 
129  /*
130  * The type of interest might be wrapped in a domain, array,
131  * composite, or range, and these container types can be nested (to
132  * varying extents depending on server version, but that's not of
133  * concern here). To handle all these cases we need a recursive CTE.
134  */
135  initPQExpBuffer(&querybuf);
136  appendPQExpBuffer(&querybuf,
137  "WITH RECURSIVE oids AS ( "
138  /* the target type itself */
139  " SELECT '%s'::pg_catalog.regtype AS oid "
140  " UNION ALL "
141  " SELECT * FROM ( "
142  /* inner WITH because we can only reference the CTE once */
143  " WITH x AS (SELECT oid FROM oids) "
144  /* domains on any type selected so far */
145  " SELECT t.oid FROM pg_catalog.pg_type t, x WHERE typbasetype = x.oid AND typtype = 'd' "
146  " UNION ALL "
147  /* arrays over any type selected so far */
148  " SELECT t.oid FROM pg_catalog.pg_type t, x WHERE typelem = x.oid AND typtype = 'b' "
149  " UNION ALL "
150  /* composite types containing any type selected so far */
151  " SELECT t.oid FROM pg_catalog.pg_type t, pg_catalog.pg_class c, pg_catalog.pg_attribute a, x "
152  " WHERE t.typtype = 'c' AND "
153  " t.oid = c.reltype AND "
154  " c.oid = a.attrelid AND "
155  " NOT a.attisdropped AND "
156  " a.atttypid = x.oid ",
157  typename);
158 
159  /* Ranges came in in 9.2 */
160  if (GET_MAJOR_VERSION(cluster->major_version) >= 902)
161  appendPQExpBuffer(&querybuf,
162  " UNION ALL "
163  /* ranges containing any type selected so far */
164  " SELECT t.oid FROM pg_catalog.pg_type t, pg_catalog.pg_range r, x "
165  " WHERE t.typtype = 'r' AND r.rngtypid = t.oid AND r.rngsubtype = x.oid");
166 
167  appendPQExpBuffer(&querybuf,
168  " ) foo "
169  ") "
170  /* now look for stored columns of any such type */
171  "SELECT n.nspname, c.relname, a.attname "
172  "FROM pg_catalog.pg_class c, "
173  " pg_catalog.pg_namespace n, "
174  " pg_catalog.pg_attribute a "
175  "WHERE c.oid = a.attrelid AND "
176  " NOT a.attisdropped AND "
177  " a.atttypid IN (SELECT oid FROM oids) AND "
178  " c.relkind IN ("
179  CppAsString2(RELKIND_RELATION) ", "
180  CppAsString2(RELKIND_MATVIEW) ", "
181  CppAsString2(RELKIND_INDEX) ") AND "
182  " c.relnamespace = n.oid AND "
183  /* exclude possible orphaned temp tables */
184  " n.nspname !~ '^pg_temp_' AND "
185  " n.nspname !~ '^pg_toast_temp_' AND "
186  /* exclude system catalogs, too */
187  " n.nspname NOT IN ('pg_catalog', 'information_schema')");
188 
189  res = executeQueryOrDie(conn, "%s", querybuf.data);
190 
191  ntups = PQntuples(res);
192  i_nspname = PQfnumber(res, "nspname");
193  i_relname = PQfnumber(res, "relname");
194  i_attname = PQfnumber(res, "attname");
195  for (rowno = 0; rowno < ntups; rowno++)
196  {
197  found = true;
198  if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
199  pg_fatal("could not open file \"%s\": %s\n", output_path,
200  strerror(errno));
201  if (!db_used)
202  {
203  fprintf(script, "In database: %s\n", active_db->db_name);
204  db_used = true;
205  }
206  fprintf(script, " %s.%s.%s\n",
207  PQgetvalue(res, rowno, i_nspname),
208  PQgetvalue(res, rowno, i_relname),
209  PQgetvalue(res, rowno, i_attname));
210  }
211 
212  PQclear(res);
213 
214  termPQExpBuffer(&querybuf);
215 
216  PQfinish(conn);
217  }
218 
219  if (script)
220  fclose(script);
221 
222  return found;
223 }
224 
225 
226 /*
227  * old_9_3_check_for_line_data_type_usage()
228  * 9.3 -> 9.4
229  * Fully implement the 'line' data type in 9.4, which previously returned
230  * "not enabled" by default and was only functionally enabled with a
231  * compile-time switch; as of 9.4 "line" has a different on-disk
232  * representation format.
233  */
234 void
236 {
237  char output_path[MAXPGPATH];
238 
239  prep_status("Checking for incompatible \"line\" data type");
240 
241  snprintf(output_path, sizeof(output_path), "tables_using_line.txt");
242 
243  if (check_for_data_type_usage(cluster, "pg_catalog.line", output_path))
244  {
245  pg_log(PG_REPORT, "fatal\n");
246  pg_fatal("Your installation contains the \"line\" data type in user tables. This\n"
247  "data type changed its internal and input/output format between your old\n"
248  "and new clusters so this cluster cannot currently be upgraded. You can\n"
249  "remove the problem tables and restart the upgrade. A list of the problem\n"
250  "columns is in the file:\n"
251  " %s\n\n", output_path);
252  }
253  else
254  check_ok();
255 }
256 
257 
258 /*
259  * old_9_6_check_for_unknown_data_type_usage()
260  * 9.6 -> 10
261  * It's no longer allowed to create tables or views with "unknown"-type
262  * columns. We do not complain about views with such columns, because
263  * they should get silently converted to "text" columns during the DDL
264  * dump and reload; it seems unlikely to be worth making users do that
265  * by hand. However, if there's a table with such a column, the DDL
266  * reload will fail, so we should pre-detect that rather than failing
267  * mid-upgrade. Worse, if there's a matview with such a column, the
268  * DDL reload will silently change it to "text" which won't match the
269  * on-disk storage (which is like "cstring"). So we *must* reject that.
270  */
271 void
273 {
274  char output_path[MAXPGPATH];
275 
276  prep_status("Checking for invalid \"unknown\" user columns");
277 
278  snprintf(output_path, sizeof(output_path), "tables_using_unknown.txt");
279 
280  if (check_for_data_type_usage(cluster, "pg_catalog.unknown", output_path))
281  {
282  pg_log(PG_REPORT, "fatal\n");
283  pg_fatal("Your installation contains the \"unknown\" data type in user tables. This\n"
284  "data type is no longer allowed in tables, so this cluster cannot currently\n"
285  "be upgraded. You can remove the problem tables and restart the upgrade.\n"
286  "A list of the problem columns is in the file:\n"
287  " %s\n\n", output_path);
288  }
289  else
290  check_ok();
291 }
292 
293 /*
294  * old_9_6_invalidate_hash_indexes()
295  * 9.6 -> 10
296  * Hash index binary format has changed from 9.6->10.0
297  */
298 void
300 {
301  int dbnum;
302  FILE *script = NULL;
303  bool found = false;
304  char *output_path = "reindex_hash.sql";
305 
306  prep_status("Checking for hash indexes");
307 
308  for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
309  {
310  PGresult *res;
311  bool db_used = false;
312  int ntups;
313  int rowno;
314  int i_nspname,
315  i_relname;
316  DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
317  PGconn *conn = connectToServer(cluster, active_db->db_name);
318 
319  /* find hash indexes */
320  res = executeQueryOrDie(conn,
321  "SELECT n.nspname, c.relname "
322  "FROM pg_catalog.pg_class c, "
323  " pg_catalog.pg_index i, "
324  " pg_catalog.pg_am a, "
325  " pg_catalog.pg_namespace n "
326  "WHERE i.indexrelid = c.oid AND "
327  " c.relam = a.oid AND "
328  " c.relnamespace = n.oid AND "
329  " a.amname = 'hash'"
330  );
331 
332  ntups = PQntuples(res);
333  i_nspname = PQfnumber(res, "nspname");
334  i_relname = PQfnumber(res, "relname");
335  for (rowno = 0; rowno < ntups; rowno++)
336  {
337  found = true;
338  if (!check_mode)
339  {
340  if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
341  pg_fatal("could not open file \"%s\": %s\n", output_path,
342  strerror(errno));
343  if (!db_used)
344  {
345  PQExpBufferData connectbuf;
346 
347  initPQExpBuffer(&connectbuf);
348  appendPsqlMetaConnect(&connectbuf, active_db->db_name);
349  fputs(connectbuf.data, script);
350  termPQExpBuffer(&connectbuf);
351  db_used = true;
352  }
353  fprintf(script, "REINDEX INDEX %s.%s;\n",
354  quote_identifier(PQgetvalue(res, rowno, i_nspname)),
355  quote_identifier(PQgetvalue(res, rowno, i_relname)));
356  }
357  }
358 
359  PQclear(res);
360 
361  if (!check_mode && db_used)
362  {
363  /* mark hash indexes as invalid */
365  "UPDATE pg_catalog.pg_index i "
366  "SET indisvalid = false "
367  "FROM pg_catalog.pg_class c, "
368  " pg_catalog.pg_am a, "
369  " pg_catalog.pg_namespace n "
370  "WHERE i.indexrelid = c.oid AND "
371  " c.relam = a.oid AND "
372  " c.relnamespace = n.oid AND "
373  " a.amname = 'hash'"));
374  }
375 
376  PQfinish(conn);
377  }
378 
379  if (script)
380  fclose(script);
381 
382  if (found)
383  {
384  report_status(PG_WARNING, "warning");
385  if (check_mode)
386  pg_log(PG_WARNING, "\n"
387  "Your installation contains hash indexes. These indexes have different\n"
388  "internal formats between your old and new clusters, so they must be\n"
389  "reindexed with the REINDEX command. After upgrading, you will be given\n"
390  "REINDEX instructions.\n\n");
391  else
392  pg_log(PG_WARNING, "\n"
393  "Your installation contains hash indexes. These indexes have different\n"
394  "internal formats between your old and new clusters, so they must be\n"
395  "reindexed with the REINDEX command. The file\n"
396  " %s\n"
397  "when executed by psql by the database superuser will recreate all invalid\n"
398  "indexes; until then, none of these indexes will be used.\n\n",
399  output_path);
400  }
401  else
402  check_ok();
403 }
404 
405 /*
406  * old_11_check_for_sql_identifier_data_type_usage()
407  * 11 -> 12
408  * In 12, the sql_identifier data type was switched from name to varchar,
409  * which does affect the storage (name is by-ref, but not varlena). This
410  * means user tables using sql_identifier for columns are broken because
411  * the on-disk format is different.
412  */
413 void
415 {
416  char output_path[MAXPGPATH];
417 
418  prep_status("Checking for invalid \"sql_identifier\" user columns");
419 
420  snprintf(output_path, sizeof(output_path), "tables_using_sql_identifier.txt");
421 
422  if (check_for_data_type_usage(cluster, "information_schema.sql_identifier",
423  output_path))
424  {
425  pg_log(PG_REPORT, "fatal\n");
426  pg_fatal("Your installation contains the \"sql_identifier\" data type in user tables\n"
427  "and/or indexes. The on-disk format for this data type has changed, so this\n"
428  "cluster cannot currently be upgraded. You can remove the problem tables or\n"
429  "change the data type to \"name\" and restart the upgrade.\n"
430  "A list of the problem columns is in the file:\n"
431  " %s\n\n", output_path);
432  }
433  else
434  check_ok();
435 }
uint32 major_version
Definition: pg_upgrade.h:269
const char * quote_identifier(const char *ident)
Definition: ruleutils.c:10640
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3163
void old_11_check_for_sql_identifier_data_type_usage(ClusterInfo *cluster)
Definition: version.c:414
void termPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:131
void old_9_6_check_for_unknown_data_type_usage(ClusterInfo *cluster)
Definition: version.c:272
PGresult * executeQueryOrDie(PGconn *conn, const char *fmt,...) pg_attribute_printf(2
#define GET_MAJOR_VERSION(v)
Definition: pg_upgrade.h:23
#define pg_fatal(...)
Definition: pg_rewind.h:41
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4125
int PQntuples(const PGresult *res)
Definition: fe-exec.c:2769
#define fprintf
Definition: port.h:196
void appendPsqlMetaConnect(PQExpBuffer buf, const char *dbname)
Definition: string_utils.c:590
PGconn * conn
Definition: streamutil.c:54
#define MAXPGPATH
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:267
static bool check_for_data_type_usage(ClusterInfo *cluster, const char *typename, char *output_path)
Definition: version.c:109
void prep_status(const char *fmt,...) pg_attribute_printf(1
void cluster(ClusterStmt *stmt, bool isTopLevel)
Definition: cluster.c:102
static void check_ok(void)
Definition: initdb.c:2076
PGconn * connectToServer(ClusterInfo *cluster, const char *db_name)
Definition: server.c:27
#define CppAsString2(x)
Definition: c.h:224
int PQfnumber(const PGresult *res, const char *field_name)
Definition: fe-exec.c:2877
void PQclear(PGresult *res)
Definition: fe-exec.c:694
DbInfoArr dbarr
Definition: pg_upgrade.h:260
void void pg_log(eLogType type, const char *fmt,...) pg_attribute_printf(2
#define strerror
Definition: port.h:205
#define fopen_priv(path, mode)
Definition: pg_upgrade.h:382
void report_status(eLogType type, const char *fmt,...) pg_attribute_printf(2
char * db_name
Definition: pg_upgrade.h:181
void new_9_0_populate_pg_largeobject_metadata(ClusterInfo *cluster, bool check_mode)
Definition: version.c:22
DbInfo * dbs
Definition: pg_upgrade.h:192
#define snprintf
Definition: port.h:192
void initPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:92
void old_9_3_check_for_line_data_type_usage(ClusterInfo *cluster)
Definition: version.c:235
void old_9_6_invalidate_hash_indexes(ClusterInfo *cluster, bool check_mode)
Definition: version.c:299