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 "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
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  * old_9_3_check_for_line_data_type_usage()
104  * 9.3 -> 9.4
105  * Fully implement the 'line' data type in 9.4, which previously returned
106  * "not enabled" by default and was only functionally enabled with a
107  * compile-time switch; 9.4 "line" has different binary and text
108  * representation formats; checks tables and indexes.
109  */
110 void
112 {
113  int dbnum;
114  FILE *script = NULL;
115  bool found = false;
116  char output_path[MAXPGPATH];
117 
118  prep_status("Checking for incompatible \"line\" data type");
119 
120  snprintf(output_path, sizeof(output_path), "tables_using_line.txt");
121 
122  for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
123  {
124  PGresult *res;
125  bool db_used = false;
126  int ntups;
127  int rowno;
128  int i_nspname,
129  i_relname,
130  i_attname;
131  DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
132  PGconn *conn = connectToServer(cluster, active_db->db_name);
133 
134  /*
135  * The pg_catalog.line type may be wrapped in a domain or composite
136  * type, or both (9.3 did not allow domains on composite types, but
137  * there may be multi-level composite type). To detect these cases
138  * we need a recursive CTE.
139  */
140  res = executeQueryOrDie(conn,
141  "WITH RECURSIVE oids AS ( "
142  /* the pg_catalog.line type itself */
143  " SELECT 'pg_catalog.line'::pg_catalog.regtype AS oid "
144  " UNION ALL "
145  " SELECT * FROM ( "
146  /* domains on the type */
147  " WITH x AS (SELECT oid FROM oids) "
148  " SELECT t.oid FROM pg_catalog.pg_type t, x WHERE typbasetype = x.oid AND typtype = 'd' "
149  " UNION "
150  /* composite types containing the type */
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  " ) foo "
158  ") "
159  "SELECT n.nspname, c.relname, a.attname "
160  "FROM pg_catalog.pg_class c, "
161  " pg_catalog.pg_namespace n, "
162  " pg_catalog.pg_attribute a "
163  "WHERE c.oid = a.attrelid AND "
164  " NOT a.attisdropped AND "
165  " a.atttypid IN (SELECT oid FROM oids) AND "
166  " c.relkind IN ("
167  CppAsString2(RELKIND_RELATION) ", "
168  CppAsString2(RELKIND_MATVIEW) ", "
169  CppAsString2(RELKIND_INDEX) ") AND "
170  " c.relnamespace = n.oid AND "
171  /* exclude possible orphaned temp tables */
172  " n.nspname !~ '^pg_temp_' AND "
173  " n.nspname !~ '^pg_toast_temp_' AND "
174  " n.nspname NOT IN ('pg_catalog', 'information_schema')");
175 
176  ntups = PQntuples(res);
177  i_nspname = PQfnumber(res, "nspname");
178  i_relname = PQfnumber(res, "relname");
179  i_attname = PQfnumber(res, "attname");
180  for (rowno = 0; rowno < ntups; rowno++)
181  {
182  found = true;
183  if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
184  pg_fatal("could not open file \"%s\": %s\n", output_path,
185  strerror(errno));
186  if (!db_used)
187  {
188  fprintf(script, "In database: %s\n", active_db->db_name);
189  db_used = true;
190  }
191  fprintf(script, " %s.%s.%s\n",
192  PQgetvalue(res, rowno, i_nspname),
193  PQgetvalue(res, rowno, i_relname),
194  PQgetvalue(res, rowno, i_attname));
195  }
196 
197  PQclear(res);
198 
199  PQfinish(conn);
200  }
201 
202  if (script)
203  fclose(script);
204 
205  if (found)
206  {
207  pg_log(PG_REPORT, "fatal\n");
208  pg_fatal("Your installation contains the \"line\" data type in user tables. This\n"
209  "data type changed its internal and input/output format between your old\n"
210  "and new clusters so this cluster cannot currently be upgraded. You can\n"
211  "remove the problem tables and restart the upgrade. A list of the problem\n"
212  "columns is in the file:\n"
213  " %s\n\n", output_path);
214  }
215  else
216  check_ok();
217 }
218 
219 
220 /*
221  * old_9_6_check_for_unknown_data_type_usage()
222  * 9.6 -> 10
223  * It's no longer allowed to create tables or views with "unknown"-type
224  * columns. We do not complain about views with such columns, because
225  * they should get silently converted to "text" columns during the DDL
226  * dump and reload; it seems unlikely to be worth making users do that
227  * by hand. However, if there's a table with such a column, the DDL
228  * reload will fail, so we should pre-detect that rather than failing
229  * mid-upgrade. Worse, if there's a matview with such a column, the
230  * DDL reload will silently change it to "text" which won't match the
231  * on-disk storage (which is like "cstring"). So we *must* reject that.
232  * Also check composite types and domains on the "unknwown" type (even
233  * combinations of both), in case they are used for table columns.
234  * We needn't check indexes, because "unknown" has no opclasses.
235  */
236 void
238 {
239  int dbnum;
240  FILE *script = NULL;
241  bool found = false;
242  char output_path[MAXPGPATH];
243 
244  prep_status("Checking for invalid \"unknown\" user columns");
245 
246  snprintf(output_path, sizeof(output_path), "tables_using_unknown.txt");
247 
248  for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
249  {
250  PGresult *res;
251  bool db_used = false;
252  int ntups;
253  int rowno;
254  int i_nspname,
255  i_relname,
256  i_attname;
257  DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
258  PGconn *conn = connectToServer(cluster, active_db->db_name);
259 
260  /*
261  * The pg_catalog.unknown type may be wrapped in a domain or composite
262  * type, or both (9.3 did not allow domains on composite types, but
263  * there may be multi-level composite type). To detect these cases
264  * we need a recursive CTE.
265  */
266  res = executeQueryOrDie(conn,
267  "WITH RECURSIVE oids AS ( "
268  /* the pg_catalog.unknown type itself */
269  " SELECT 'pg_catalog.unknown'::pg_catalog.regtype AS oid "
270  " UNION ALL "
271  " SELECT * FROM ( "
272  /* domains on the type */
273  " WITH x AS (SELECT oid FROM oids) "
274  " SELECT t.oid FROM pg_catalog.pg_type t, x WHERE typbasetype = x.oid AND typtype = 'd' "
275  " UNION "
276  /* composite types containing the type */
277  " SELECT t.oid FROM pg_catalog.pg_type t, pg_catalog.pg_class c, pg_catalog.pg_attribute a, x "
278  " WHERE t.typtype = 'c' AND "
279  " t.oid = c.reltype AND "
280  " c.oid = a.attrelid AND "
281  " NOT a.attisdropped AND "
282  " a.atttypid = x.oid "
283  " ) foo "
284  ") "
285  "SELECT n.nspname, c.relname, a.attname "
286  "FROM pg_catalog.pg_class c, "
287  " pg_catalog.pg_namespace n, "
288  " pg_catalog.pg_attribute a "
289  "WHERE c.oid = a.attrelid AND "
290  " NOT a.attisdropped AND "
291  " a.atttypid IN (SELECT oid FROM oids) AND "
292  " c.relkind IN ("
293  CppAsString2(RELKIND_RELATION) ", "
294  CppAsString2(RELKIND_MATVIEW) ") AND "
295  " c.relnamespace = n.oid AND "
296  /* exclude possible orphaned temp tables */
297  " n.nspname !~ '^pg_temp_' AND "
298  " n.nspname !~ '^pg_toast_temp_' AND "
299  " n.nspname NOT IN ('pg_catalog', 'information_schema')");
300 
301  ntups = PQntuples(res);
302  i_nspname = PQfnumber(res, "nspname");
303  i_relname = PQfnumber(res, "relname");
304  i_attname = PQfnumber(res, "attname");
305  for (rowno = 0; rowno < ntups; rowno++)
306  {
307  found = true;
308  if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
309  pg_fatal("could not open file \"%s\": %s\n", output_path,
310  strerror(errno));
311  if (!db_used)
312  {
313  fprintf(script, "In database: %s\n", active_db->db_name);
314  db_used = true;
315  }
316  fprintf(script, " %s.%s.%s\n",
317  PQgetvalue(res, rowno, i_nspname),
318  PQgetvalue(res, rowno, i_relname),
319  PQgetvalue(res, rowno, i_attname));
320  }
321 
322  PQclear(res);
323 
324  PQfinish(conn);
325  }
326 
327  if (script)
328  fclose(script);
329 
330  if (found)
331  {
332  pg_log(PG_REPORT, "fatal\n");
333  pg_fatal("Your installation contains the \"unknown\" data type in user tables. This\n"
334  "data type is no longer allowed in tables, so this cluster cannot currently\n"
335  "be upgraded. You can remove the problem tables and restart the upgrade.\n"
336  "A list of the problem columns is in the file:\n"
337  " %s\n\n", output_path);
338  }
339  else
340  check_ok();
341 }
342 
343 /*
344  * old_9_6_invalidate_hash_indexes()
345  * 9.6 -> 10
346  * Hash index binary format has changed from 9.6->10.0
347  */
348 void
350 {
351  int dbnum;
352  FILE *script = NULL;
353  bool found = false;
354  char *output_path = "reindex_hash.sql";
355 
356  prep_status("Checking for hash indexes");
357 
358  for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
359  {
360  PGresult *res;
361  bool db_used = false;
362  int ntups;
363  int rowno;
364  int i_nspname,
365  i_relname;
366  DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
367  PGconn *conn = connectToServer(cluster, active_db->db_name);
368 
369  /* find hash indexes */
370  res = executeQueryOrDie(conn,
371  "SELECT n.nspname, c.relname "
372  "FROM pg_catalog.pg_class c, "
373  " pg_catalog.pg_index i, "
374  " pg_catalog.pg_am a, "
375  " pg_catalog.pg_namespace n "
376  "WHERE i.indexrelid = c.oid AND "
377  " c.relam = a.oid AND "
378  " c.relnamespace = n.oid AND "
379  " a.amname = 'hash'"
380  );
381 
382  ntups = PQntuples(res);
383  i_nspname = PQfnumber(res, "nspname");
384  i_relname = PQfnumber(res, "relname");
385  for (rowno = 0; rowno < ntups; rowno++)
386  {
387  found = true;
388  if (!check_mode)
389  {
390  if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
391  pg_fatal("could not open file \"%s\": %s\n", output_path,
392  strerror(errno));
393  if (!db_used)
394  {
395  PQExpBufferData connectbuf;
396 
397  initPQExpBuffer(&connectbuf);
398  appendPsqlMetaConnect(&connectbuf, active_db->db_name);
399  fputs(connectbuf.data, script);
400  termPQExpBuffer(&connectbuf);
401  db_used = true;
402  }
403  fprintf(script, "REINDEX INDEX %s.%s;\n",
404  quote_identifier(PQgetvalue(res, rowno, i_nspname)),
405  quote_identifier(PQgetvalue(res, rowno, i_relname)));
406  }
407  }
408 
409  PQclear(res);
410 
411  if (!check_mode && db_used)
412  {
413  /* mark hash indexes as invalid */
415  "UPDATE pg_catalog.pg_index i "
416  "SET indisvalid = false "
417  "FROM pg_catalog.pg_class c, "
418  " pg_catalog.pg_am a, "
419  " pg_catalog.pg_namespace n "
420  "WHERE i.indexrelid = c.oid AND "
421  " c.relam = a.oid AND "
422  " c.relnamespace = n.oid AND "
423  " a.amname = 'hash'"));
424  }
425 
426  PQfinish(conn);
427  }
428 
429  if (script)
430  fclose(script);
431 
432  if (found)
433  {
434  report_status(PG_WARNING, "warning");
435  if (check_mode)
436  pg_log(PG_WARNING, "\n"
437  "Your installation contains hash indexes. These indexes have different\n"
438  "internal formats between your old and new clusters, so they must be\n"
439  "reindexed with the REINDEX command. After upgrading, you will be given\n"
440  "REINDEX instructions.\n\n");
441  else
442  pg_log(PG_WARNING, "\n"
443  "Your installation contains hash indexes. These indexes have different\n"
444  "internal formats between your old and new clusters, so they must be\n"
445  "reindexed with the REINDEX command. The file\n"
446  " %s\n"
447  "when executed by psql by the database superuser will recreate all invalid\n"
448  "indexes; until then, none of these indexes will be used.\n\n",
449  output_path);
450  }
451  else
452  check_ok();
453 }
454 
455 /*
456  * old_11_check_for_sql_identifier_data_type_usage()
457  * 11 -> 12
458  * In 12, the sql_identifier data type was switched from name to varchar,
459  * which does affect the storage (name is by-ref, but not varlena). This
460  * means user tables using sql_identifier for columns are broken because
461  * the on-disk format is different.
462  *
463  * We need to check all objects that might store sql_identifier on disk,
464  * i.e. tables, matviews and indexes. Also check composite types in case
465  * they are used in this context.
466  */
467 void
469 {
470  int dbnum;
471  FILE *script = NULL;
472  bool found = false;
473  char output_path[MAXPGPATH];
474 
475  prep_status("Checking for invalid \"sql_identifier\" user columns");
476 
477  snprintf(output_path, sizeof(output_path), "tables_using_sql_identifier.txt");
478 
479  for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
480  {
481  PGresult *res;
482  bool db_used = false;
483  int ntups;
484  int rowno;
485  int i_nspname,
486  i_relname,
487  i_attname;
488  DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
489  PGconn *conn = connectToServer(cluster, active_db->db_name);
490 
491  /*
492  * We need the recursive CTE because the sql_identifier may be wrapped
493  * either in a domain or composite type, or both (in arbitrary order).
494  */
495  res = executeQueryOrDie(conn,
496  "WITH RECURSIVE oids AS ( "
497  /* the sql_identifier type itself */
498  " SELECT 'information_schema.sql_identifier'::pg_catalog.regtype AS oid "
499  " UNION ALL "
500  " SELECT * FROM ( "
501  /* domains on the type */
502  " WITH x AS (SELECT oid FROM oids) "
503  " SELECT t.oid FROM pg_catalog.pg_type t, x WHERE typbasetype = x.oid AND typtype = 'd' "
504  " UNION "
505  /* composite types containing the type */
506  " SELECT t.oid FROM pg_catalog.pg_type t, pg_catalog.pg_class c, pg_catalog.pg_attribute a, x "
507  " WHERE t.typtype = 'c' AND "
508  " t.oid = c.reltype AND "
509  " c.oid = a.attrelid AND "
510  " NOT a.attisdropped AND "
511  " a.atttypid = x.oid "
512  " ) foo "
513  ") "
514  "SELECT n.nspname, c.relname, a.attname "
515  "FROM pg_catalog.pg_class c, "
516  " pg_catalog.pg_namespace n, "
517  " pg_catalog.pg_attribute a "
518  "WHERE c.oid = a.attrelid AND "
519  " NOT a.attisdropped AND "
520  " a.atttypid IN (SELECT oid FROM oids) AND "
521  " c.relkind IN ("
522  CppAsString2(RELKIND_RELATION) ", "
523  CppAsString2(RELKIND_MATVIEW) ", "
524  CppAsString2(RELKIND_INDEX) ") AND "
525  " c.relnamespace = n.oid AND "
526  /* exclude possible orphaned temp tables */
527  " n.nspname !~ '^pg_temp_' AND "
528  " n.nspname !~ '^pg_toast_temp_' AND "
529  " n.nspname NOT IN ('pg_catalog', 'information_schema')");
530 
531  ntups = PQntuples(res);
532  i_nspname = PQfnumber(res, "nspname");
533  i_relname = PQfnumber(res, "relname");
534  i_attname = PQfnumber(res, "attname");
535  for (rowno = 0; rowno < ntups; rowno++)
536  {
537  found = true;
538  if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
539  pg_fatal("could not open file \"%s\": %s\n", output_path,
540  strerror(errno));
541  if (!db_used)
542  {
543  fprintf(script, "Database: %s\n", active_db->db_name);
544  db_used = true;
545  }
546  fprintf(script, " %s.%s.%s\n",
547  PQgetvalue(res, rowno, i_nspname),
548  PQgetvalue(res, rowno, i_relname),
549  PQgetvalue(res, rowno, i_attname));
550  }
551 
552  PQclear(res);
553 
554  PQfinish(conn);
555  }
556 
557  if (script)
558  fclose(script);
559 
560  if (found)
561  {
562  pg_log(PG_REPORT, "fatal\n");
563  pg_fatal("Your installation contains the \"sql_identifier\" data type in user tables\n"
564  "and/or indexes. The on-disk format for this data type has changed, so this\n"
565  "cluster cannot currently be upgraded. You can remove the problem tables or\n"
566  "change the data type to \"name\" and restart the upgrade.\n"
567  "A list of the problem columns is in the file:\n"
568  " %s\n\n", output_path);
569  }
570  else
571  check_ok();
572 }
const char * quote_identifier(const char *ident)
Definition: ruleutils.c:10628
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3164
void old_11_check_for_sql_identifier_data_type_usage(ClusterInfo *cluster)
Definition: version.c:468
void termPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:131
void old_9_6_check_for_unknown_data_type_usage(ClusterInfo *cluster)
Definition: version.c:237
PGresult * executeQueryOrDie(PGconn *conn, const char *fmt,...) pg_attribute_printf(2
#define pg_fatal(...)
Definition: pg_rewind.h:43
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4098
int PQntuples(const PGresult *res)
Definition: fe-exec.c:2770
#define fprintf
Definition: port.h:196
void appendPsqlMetaConnect(PQExpBuffer buf, const char *dbname)
Definition: string_utils.c:592
PGconn * conn
Definition: streamutil.c:56
#define MAXPGPATH
void prep_status(const char *fmt,...) pg_attribute_printf(1
void cluster(ClusterStmt *stmt, bool isTopLevel)
Definition: cluster.c:103
static void check_ok(void)
Definition: initdb.c:2079
PGconn * connectToServer(ClusterInfo *cluster, const char *db_name)
Definition: server.c:28
#define CppAsString2(x)
Definition: c.h:224
int PQfnumber(const PGresult *res, const char *field_name)
Definition: fe-exec.c:2878
void PQclear(PGresult *res)
Definition: fe-exec.c:695
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:25
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:111
void old_9_6_invalidate_hash_indexes(ClusterInfo *cluster, bool check_mode)
Definition: version.c:349