Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita524f50

Browse files
committed
Improve the check for pg_catalog.unknown data type in pg_upgrade
The pg_upgrade check for pg_catalog.unknown type when upgrading from 9.6had a couple of issues with domains and composite types - it detectedeven composite types unused in objects with storage. So for example thiswas enough to trigger an unnecessary pg_upgrade failure: CREATE TYPE unknown_composite AS (u pg_catalog.unknown)On the other hand, this only happened with composite types directly onthe pg_catalog.unknown data type, but not with a domain. So this was notdetected CREATE DOMAIN unknown_domain AS pg_catalog.unknown; CREATE TYPE unknown_composite_2 AS (u unknown_domain);unlike the first example. These false positives and inconsistencies areunfortunate, but what's worse we've failed to detected objects using thepg_catalog.unknown type through a domain. So we missed cases like this CREATE TABLE t (u unknown_composite_2);The consequence is clusters broken after a pg_upgrade.This fixes these false positives and false negatives by using the samerecursive CTE introduced byeaf900e for sql_identifier. Backpatch allthe way to 10, where the of pg_catalog.unknown data type was restricted.Author: Tomas VondraBackpatch-to: 10-Discussion:https://postgr.es/m/16045-673e8fa6b5ace196%40postgresql.org
1 parent8d48e6a commita524f50

File tree

1 file changed

+27
-3
lines changed

1 file changed

+27
-3
lines changed

‎src/bin/pg_upgrade/version.c

Lines changed: 27 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -229,7 +229,8 @@ old_9_3_check_for_line_data_type_usage(ClusterInfo *cluster)
229229
*mid-upgrade. Worse, if there's a matview with such a column, the
230230
*DDL reload will silently change it to "text" which won't match the
231231
*on-disk storage (which is like "cstring"). So we *must* reject that.
232-
*Also check composite types, in case they are used for table columns.
232+
*Also check composite types and domains on the "unknwown" type (even
233+
*combinations of both), in case they are used for table columns.
233234
*We needn't check indexes, because "unknown" has no opclasses.
234235
*/
235236
void
@@ -256,17 +257,40 @@ old_9_6_check_for_unknown_data_type_usage(ClusterInfo *cluster)
256257
DbInfo*active_db=&cluster->dbarr.dbs[dbnum];
257258
PGconn*conn=connectToServer(cluster,active_db->db_name);
258259

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+
*/
259266
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+
") "
260285
"SELECT n.nspname, c.relname, a.attname "
261286
"FROMpg_catalog.pg_class c, "
262287
"pg_catalog.pg_namespace n, "
263288
"pg_catalog.pg_attribute a "
264289
"WHEREc.oid = a.attrelid AND "
265290
"NOT a.attisdropped AND "
266-
"a.atttypid= 'pg_catalog.unknown'::pg_catalog.regtype AND "
291+
"a.atttypidIN (SELECT oid FROM oids) AND "
267292
"c.relkind IN ("
268293
CppAsString2(RELKIND_RELATION) ", "
269-
CppAsString2(RELKIND_COMPOSITE_TYPE)", "
270294
CppAsString2(RELKIND_MATVIEW)") AND "
271295
"c.relnamespace = n.oid AND "
272296
/* exclude possible orphaned temp tables */

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp