1 /*
2 * version.c
3 *
4 * Postgres-version-specific routines
5 *
6 * Copyright (c) 2010-2016, 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.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 permission 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 invalid \"line\" user columns");
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 * report_extension_updates()
296 * Report extensions that should be updated.
297 */
298 void
report_extension_updates(ClusterInfo * cluster)299 report_extension_updates(ClusterInfo *cluster)
300 {
301 int dbnum;
302 FILE *script = NULL;
303 bool found = false;
304 char *output_path = "update_extensions.sql";
305
306 prep_status("Checking for extension updates");
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_name;
315 DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
316 PGconn *conn = connectToServer(cluster, active_db->db_name);
317
318 /* find extensions needing updates */
319 res = executeQueryOrDie(conn,
320 "SELECT name "
321 "FROM pg_available_extensions "
322 "WHERE installed_version != default_version"
323 );
324
325 ntups = PQntuples(res);
326 i_name = PQfnumber(res, "name");
327 for (rowno = 0; rowno < ntups; rowno++)
328 {
329 found = true;
330
331 if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)
332 pg_fatal("could not open file \"%s\": %s\n", output_path,
333 strerror(errno));
334 if (!db_used)
335 {
336 PQExpBufferData connectbuf;
337
338 initPQExpBuffer(&connectbuf);
339 appendPsqlMetaConnect(&connectbuf, active_db->db_name);
340 fputs(connectbuf.data, script);
341 termPQExpBuffer(&connectbuf);
342 db_used = true;
343 }
344 fprintf(script, "ALTER EXTENSION %s UPDATE;\n",
345 quote_identifier(PQgetvalue(res, rowno, i_name)));
346 }
347
348 PQclear(res);
349
350 PQfinish(conn);
351 }
352
353 if (script)
354 fclose(script);
355
356 if (found)
357 {
358 report_status(PG_REPORT, "notice");
359 pg_log(PG_REPORT, "\n"
360 "Your installation contains extensions that should be updated\n"
361 "with the ALTER EXTENSION command. The file\n"
362 " %s\n"
363 "when executed by psql by the database superuser will update\n"
364 "these extensions.\n\n",
365 output_path);
366 }
367 else
368 check_ok();
369 }
370