Skip to content

Commit 56c0699

Browse files
tglsfdctglsfdc
committed
Handle arrays and ranges in pg_upgrade's test for non-upgradable types.
pg_upgrade needs to check whether certain non-upgradable data types appear anywhere on-disk in the source cluster. It knew that it has to check for these types being contained inside domains and composite types; but it somehow overlooked that they could be contained in arrays and ranges, too. Extend the existing recursive-containment query to handle those cases. We probably should have noticed this oversight while working on commit 0ccfc28 and follow-ups, but we failed to :-(. The whole thing's possibly a bit overdesigned, since we don't really expect that any of these types will appear on disk; but if we're going to the effort of doing a recursive search then it's silly not to cover all the possibilities. While at it, refactor so that we have only one copy of the search logic, not three-and-counting. Also, to keep the branches looking more alike, back-patch the output wording change of commit 1634d36. Back-patch to all supported branches. Discussion: https://postgr.es/m/31473.1573412838@sss.pgh.pa.us
1 parent 6cd1549 commit 56c0699

File tree

1 file changed

+98
-57
lines changed

1 file changed

+98
-57
lines changed

‎contrib/pg_upgrade/version.c

Lines changed: 98 additions & 57 deletions
Original file line numberDiff line numberDiff line change
@@ -98,78 +98,96 @@ new_9_0_populate_pg_largeobject_metadata(ClusterInfo *cluster, bool check_mode)
9898

9999

100100
/*
101-
* old_9_3_check_for_line_data_type_usage()
102-
* 9.3 -> 9.4
103-
* Fully implement the 'line' data type in 9.4, which previously returned
104-
* "not enabled" by default and was only functionally enabled with a
105-
* compile-time switch; 9.4 "line" has different binary and text
106-
* representation formats; checks tables and indexes.
101+
* check_for_data_type_usage
102+
* Detect whether there are any stored columns depending on the given type
103+
*
104+
* If so, write a report to the given file name, and return true.
105+
*
106+
* We check for the type in tables, matviews, and indexes, but not views;
107+
* there's no storage involved in a view.
107108
*/
108-
void
109-
old_9_3_check_for_line_data_type_usage(ClusterInfo *cluster)
109+
static bool
110+
check_for_data_type_usage(ClusterInfo *cluster, const char *typename,
111+
char *output_path)
110112
{
111-
int dbnum;
112-
FILE *script = NULL;
113113
bool found = false;
114-
char output_path[MAXPGPATH];
115-
116-
prep_status("Checking for invalid \"line\" user columns");
117-
118-
snprintf(output_path, sizeof(output_path), "tables_using_line.txt");
114+
FILE *script = NULL;
115+
int dbnum;
119116

120117
for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
121118
{
119+
DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
120+
PGconn *conn = connectToServer(cluster, active_db->db_name);
121+
PQExpBufferData querybuf;
122122
PGresult *res;
123123
bool db_used = false;
124124
int ntups;
125125
int rowno;
126126
int i_nspname,
127127
i_relname,
128128
i_attname;
129-
DbInfo *active_db = &cluster->dbarr.dbs[dbnum];
130-
PGconn *conn = connectToServer(cluster, active_db->db_name);
131129

132130
/*
133-
* The pg_catalog.line type may be wrapped in a domain or composite
134-
* type, or both (9.3 did not allow domains on composite types, but
135-
* there may be multi-level composite type). To detect these cases
136-
* we need a recursive CTE.
131+
* The type of interest might be wrapped in a domain, array,
132+
* composite, or range, and these container types can be nested (to
133+
* varying extents depending on server version, but that's not of
134+
* concern here). To handle all these cases we need a recursive CTE.
137135
*/
138-
res = executeQueryOrDie(conn,
139-
"WITH RECURSIVE oids AS ( "
140-
/* the pg_catalog.line type itself */
141-
" SELECT 'pg_catalog.line'::pg_catalog.regtype AS oid "
142-
" UNION ALL "
143-
" SELECT * FROM ( "
144-
/* domains on the type */
145-
" WITH x AS (SELECT oid FROM oids) "
146-
" SELECT t.oid FROM pg_catalog.pg_type t, x WHERE typbasetype = x.oid AND typtype = 'd' "
147-
" UNION "
148-
/* composite types containing the type */
149-
" SELECT t.oid FROM pg_catalog.pg_type t, pg_catalog.pg_class c, pg_catalog.pg_attribute a, x "
150-
" WHERE t.typtype = 'c' AND "
151-
" t.oid = c.reltype AND "
152-
" c.oid = a.attrelid AND "
153-
" NOT a.attisdropped AND "
154-
" a.atttypid = x.oid "
155-
" ) foo "
156-
") "
157-
"SELECT n.nspname, c.relname, a.attname "
158-
"FROM pg_catalog.pg_class c, "
159-
" pg_catalog.pg_namespace n, "
160-
" pg_catalog.pg_attribute a "
161-
"WHERE c.oid = a.attrelid AND "
162-
" NOT a.attisdropped AND "
163-
" a.atttypid IN (SELECT oid FROM oids) AND "
164-
" c.relkind IN ("
165-
CppAsString2(RELKIND_RELATION) ", "
166-
CppAsString2(RELKIND_MATVIEW) ", "
167-
CppAsString2(RELKIND_INDEX) ") AND "
168-
" c.relnamespace = n.oid AND "
136+
initPQExpBuffer(&querybuf);
137+
appendPQExpBuffer(&querybuf,
138+
"WITH RECURSIVE oids AS ( "
139+
/* the target type itself */
140+
" SELECT '%s'::pg_catalog.regtype AS oid "
141+
" UNION ALL "
142+
" SELECT * FROM ( "
143+
/* inner WITH because we can only reference the CTE once */
144+
" WITH x AS (SELECT oid FROM oids) "
145+
/* domains on any type selected so far */
146+
" SELECT t.oid FROM pg_catalog.pg_type t, x WHERE typbasetype = x.oid AND typtype = 'd' "
147+
" UNION ALL "
148+
/* arrays over any type selected so far */
149+
" SELECT t.oid FROM pg_catalog.pg_type t, x WHERE typelem = x.oid AND typtype = 'b' "
150+
" UNION ALL "
151+
/* composite types containing any type selected so far */
152+
" SELECT t.oid FROM pg_catalog.pg_type t, pg_catalog.pg_class c, pg_catalog.pg_attribute a, x "
153+
" WHERE t.typtype = 'c' AND "
154+
" t.oid = c.reltype AND "
155+
" c.oid = a.attrelid AND "
156+
" NOT a.attisdropped AND "
157+
" a.atttypid = x.oid ",
158+
typename);
159+
160+
/* Ranges came in in 9.2 */
161+
if (GET_MAJOR_VERSION(cluster->major_version) >= 902)
162+
appendPQExpBuffer(&querybuf,
163+
" UNION ALL "
164+
/* ranges containing any type selected so far */
165+
" SELECT t.oid FROM pg_catalog.pg_type t, pg_catalog.pg_range r, x "
166+
" WHERE t.typtype = 'r' AND r.rngtypid = t.oid AND r.rngsubtype = x.oid");
167+
168+
appendPQExpBuffer(&querybuf,
169+
" ) foo "
170+
") "
171+
/* now look for stored columns of any such type */
172+
"SELECT n.nspname, c.relname, a.attname "
173+
"FROM pg_catalog.pg_class c, "
174+
" pg_catalog.pg_namespace n, "
175+
" pg_catalog.pg_attribute a "
176+
"WHERE c.oid = a.attrelid AND "
177+
" NOT a.attisdropped AND "
178+
" a.atttypid IN (SELECT oid FROM oids) AND "
179+
" c.relkind IN ("
180+
CppAsString2(RELKIND_RELATION) ", "
181+
CppAsString2(RELKIND_MATVIEW) ", "
182+
CppAsString2(RELKIND_INDEX) ") AND "
183+
" c.relnamespace = n.oid AND "
169184
/* exclude possible orphaned temp tables */
170-
" n.nspname !~ '^pg_temp_' AND "
171-
" n.nspname !~ '^pg_toast_temp_' AND "
172-
" n.nspname NOT IN ('pg_catalog', 'information_schema')");
185+
" n.nspname !~ '^pg_temp_' AND "
186+
" n.nspname !~ '^pg_toast_temp_' AND "
187+
/* exclude system catalogs, too */
188+
" n.nspname NOT IN ('pg_catalog', 'information_schema')");
189+
190+
res = executeQueryOrDie(conn, "%s", querybuf.data);
173191

174192
ntups = PQntuples(res);
175193
i_nspname = PQfnumber(res, "nspname");
@@ -182,7 +200,7 @@ old_9_3_check_for_line_data_type_usage(ClusterInfo *cluster)
182200
pg_fatal("could not open file \"%s\": %s\n", output_path, getErrorText());
183201
if (!db_used)
184202
{
185-
fprintf(script, "Database: %s\n", active_db->db_name);
203+
fprintf(script, "In database: %s\n", active_db->db_name);
186204
db_used = true;
187205
}
188206
fprintf(script, " %s.%s.%s\n",
@@ -193,13 +211,36 @@ old_9_3_check_for_line_data_type_usage(ClusterInfo *cluster)
193211

194212
PQclear(res);
195213

214+
termPQExpBuffer(&querybuf);
215+
196216
PQfinish(conn);
197217
}
198218

199219
if (script)
200220
fclose(script);
201221

202-
if (found)
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
235+
old_9_3_check_for_line_data_type_usage(ClusterInfo *cluster)
236+
{
237+
char output_path[MAXPGPATH];
238+
239+
prep_status("Checking for invalid \"line\" user columns");
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))
203244
{
204245
pg_log(PG_REPORT, "fatal\n");
205246
pg_fatal("Your installation contains the \"line\" data type in user tables. This\n"

0 commit comments

Comments
 (0)