@@ -320,71 +320,75 @@ get_rel_infos(ClusterInfo *cluster, DbInfo *dbinfo)
320320 */
321321
322322snprintf (query ,sizeof (query ),
323- "CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid "
324- "FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n "
325- " ON c.relnamespace = n.oid "
326- "LEFT OUTER JOIN pg_catalog.pg_index i "
327- " ON c.oid = i.indexrelid "
328- "WHERE relkind IN ('r', 'm', 'i', 'S') AND "
329-
330- /*
331- * pg_dump only dumps valid indexes; testing indisready is necessary in
332- * 9.2, and harmless in earlier/later versions.
333- */
334- " i.indisvalid IS DISTINCT FROM false AND "
335- " i.indisready IS DISTINCT FROM false AND "
336- /* exclude possible orphaned temp tables */
337- " ((n.nspname !~ '^pg_temp_' AND "
338- " n.nspname !~ '^pg_toast_temp_' AND "
339- /* skip pg_toast because toast index have relkind == 'i', not 't' */
340- " n.nspname NOT IN ('pg_catalog', 'information_schema', "
341- "'binary_upgrade', 'pg_toast') AND "
342- " c.oid >= %u) "
343- " OR (n.nspname = 'pg_catalog' AND "
344- " relname IN ('pg_largeobject', 'pg_largeobject_loid_pn_index'%s) ));" ,
345- FirstNormalObjectId ,
346- /* does pg_largeobject_metadata need to be migrated? */
347- (GET_MAJOR_VERSION (old_cluster .major_version ) <=804 ) ?
348- "" :", 'pg_largeobject_metadata', 'pg_largeobject_metadata_oid_index'" );
349-
350- PQclear (executeQueryOrDie (conn ,"%s" ,query ));
351-
352- /*
353- * Get TOAST tables and indexes; we have to gather the TOAST tables in
354- * later steps because we can't schema-qualify TOAST tables.
355- */
356- PQclear (executeQueryOrDie (conn ,
357- "INSERT INTO info_rels "
358- "SELECT reltoastrelid "
359- "FROM info_rels i JOIN pg_catalog.pg_class c "
360- "ON i.reloid = c.oid "
361- "AND c.reltoastrelid != %u" ,InvalidOid ));
362- PQclear (executeQueryOrDie (conn ,
363- "INSERT INTO info_rels "
364- "SELECT indexrelid "
365- "FROM pg_index "
366- "WHERE indisvalid "
367- " AND indrelid IN (SELECT reltoastrelid "
368- " FROM info_rels i "
369- " JOIN pg_catalog.pg_class c "
370- " ON i.reloid = c.oid "
371- " AND c.reltoastrelid != %u)" ,
372- InvalidOid ));
373-
374- snprintf (query ,sizeof (query ),
375- "SELECT c.oid, n.nspname, c.relname, "
376- "c.relfilenode, c.reltablespace, %s "
377- "FROM info_rels i JOIN pg_catalog.pg_class c "
378- "ON i.reloid = c.oid "
379- " JOIN pg_catalog.pg_namespace n "
380- " ON c.relnamespace = n.oid "
381- " LEFT OUTER JOIN pg_catalog.pg_tablespace t "
382- " ON c.reltablespace = t.oid "
323+ /* get regular heap */
324+ "WITH regular_heap (reloid) AS ( "
325+ "SELECT c.oid "
326+ "FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n "
327+ " ON c.relnamespace = n.oid "
328+ "LEFT OUTER JOIN pg_catalog.pg_index i "
329+ " ON c.oid = i.indexrelid "
330+ "WHERE relkind IN ('r', 'm', 'i', 'S') AND "
331+ /*
332+ * pg_dump only dumps valid indexes; testing indisready is necessary in
333+ * 9.2, and harmless in earlier/later versions.
334+ */
335+ " i.indisvalid IS DISTINCT FROM false AND "
336+ " i.indisready IS DISTINCT FROM false AND "
337+ /* exclude possible orphaned temp tables */
338+ " ((n.nspname !~ '^pg_temp_' AND "
339+ " n.nspname !~ '^pg_toast_temp_' AND "
340+ /* skip pg_toast because toast index have relkind == 'i', not 't' */
341+ " n.nspname NOT IN ('pg_catalog', 'information_schema', "
342+ "'binary_upgrade', 'pg_toast') AND "
343+ " c.oid >= %u) OR "
344+ " (n.nspname = 'pg_catalog' AND "
345+ " relname IN ('pg_largeobject', 'pg_largeobject_loid_pn_index'%s) ))), "
346+ /*
347+ * We have to gather the TOAST tables in later steps because we
348+ * can't schema-qualify TOAST tables.
349+ */
350+ /* get TOAST heap */
351+ "toast_heap (reloid) AS ( "
352+ "SELECT reltoastrelid "
353+ "FROM regular_heap JOIN pg_catalog.pg_class c "
354+ "ON regular_heap.reloid = c.oid "
355+ "AND c.reltoastrelid != %u), "
356+ /* get indexes on regular and TOAST heap */
357+ "all_index (reloid) AS ( "
358+ "SELECT indexrelid "
359+ "FROM pg_index "
360+ "WHERE indisvalid "
361+ " AND indrelid IN (SELECT reltoastrelid "
362+ " FROM (SELECT reloid FROM regular_heap "
363+ " UNION ALL "
364+ " SELECT reloid FROM toast_heap) all_heap "
365+ " JOIN pg_catalog.pg_class c "
366+ " ON all_heap.reloid = c.oid "
367+ " AND c.reltoastrelid != %u)) "
368+ /* get all rels */
369+ "SELECT c.oid, n.nspname, c.relname, "
370+ "c.relfilenode, c.reltablespace, %s "
371+ "FROM (SELECT reloid FROM regular_heap "
372+ " UNION ALL "
373+ " SELECT reloid FROM toast_heap "
374+ " UNION ALL "
375+ " SELECT reloid FROM all_index) all_rels "
376+ " JOIN pg_catalog.pg_class c "
377+ "ON all_rels.reloid = c.oid "
378+ " JOIN pg_catalog.pg_namespace n "
379+ " ON c.relnamespace = n.oid "
380+ " LEFT OUTER JOIN pg_catalog.pg_tablespace t "
381+ " ON c.reltablespace = t.oid "
383382/* we preserve pg_class.oid so we sort by it to match old/new */
384- "ORDER BY 1;" ,
383+ "ORDER BY 1;" ,
384+ FirstNormalObjectId ,
385+ /* does pg_largeobject_metadata need to be migrated? */
386+ (GET_MAJOR_VERSION (old_cluster .major_version ) <=804 ) ?
387+ "" :", 'pg_largeobject_metadata', 'pg_largeobject_metadata_oid_index'" ,
388+ InvalidOid ,InvalidOid ,
385389/* 9.2 removed the spclocation column */
386- GET_MAJOR_VERSION (cluster -> major_version ) <=901 ) ?
387- "t.spclocation" :"pg_catalog.pg_tablespace_location(t.oid) AS spclocation" );
390+ (GET_MAJOR_VERSION (cluster -> major_version ) <=901 ) ?
391+ "t.spclocation" :"pg_catalog.pg_tablespace_location(t.oid) AS spclocation" );
388392
389393res = executeQueryOrDie (conn ,"%s" ,query );
390394