Copilot commented on code in PR #2326: URL: https://github.com/apache/age/pull/2326#discussion_r2782250087
########## sql/age_pg_upgrade.sql: ########## @@ -0,0 +1,435 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. The ag_graph.namespace column uses +-- the regnamespace type which is not supported by pg_upgrade. These functions +-- temporarily convert the schema to be pg_upgrade compatible, then restore it +-- to the original state after the upgrade completes. +-- +-- Usage: +-- 1. Before pg_upgrade: SELECT age_prepare_pg_upgrade(); +-- 2. Run pg_upgrade as normal +-- 3. After pg_upgrade: SELECT age_finish_pg_upgrade(); +-- +-- To cancel an upgrade after preparation (before running pg_upgrade): +-- SELECT age_revert_pg_upgrade_changes(); +-- + +-- +-- age_prepare_pg_upgrade() +-- +-- Prepares an AGE database for pg_upgrade by converting the ag_graph.namespace +-- column from regnamespace to oid type. This is necessary because pg_upgrade +-- does not support the regnamespace type in user tables. +-- +-- This function: +-- 1. Creates a backup table with graph name to namespace name mappings +-- 2. Drops the existing namespace index +-- 3. Converts the namespace column from regnamespace to oid +-- 4. Recreates the namespace index with oid type +-- 5. Creates a view for backward-compatible namespace display +-- +-- Returns: void +-- Side effects: Modifies ag_graph table structure +-- +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + + -- Check if namespace column is already oid type (already prepared) + IF EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'Database already prepared for pg_upgrade (namespace is oid type).'; + RETURN; + END IF; + + -- Drop existing backup table if it exists (from a previous failed attempt) + DROP TABLE IF EXISTS public._age_pg_upgrade_backup; + + -- Create backup table with graph names mapped to namespace names + -- We store names (not OIDs) because names survive pg_upgrade while OIDs don't + CREATE TABLE public._age_pg_upgrade_backup AS + SELECT + g.graphid AS old_graphid, + g.name AS graph_name, + g.namespace::regnamespace::text AS namespace_name + FROM ag_catalog.ag_graph g; + + RAISE NOTICE 'Created backup table public._age_pg_upgrade_backup with % graph(s)', + (SELECT count(*) FROM public._age_pg_upgrade_backup); + + -- Drop the existing regnamespace-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column from regnamespace to oid + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE oid USING namespace::oid; + + -- Recreate the index with oid type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + -- Create a view for backward-compatible display of namespace as schema name + CREATE OR REPLACE VIEW ag_catalog.ag_graph_view AS + SELECT graphid, name, namespace::regnamespace AS namespace + FROM ag_catalog.ag_graph; + + RAISE NOTICE 'Successfully prepared database for pg_upgrade.'; + RAISE NOTICE 'The ag_graph.namespace column has been converted from regnamespace to oid.'; + RAISE NOTICE 'You can now run pg_upgrade.'; + RAISE NOTICE 'After pg_upgrade completes, run: SELECT age_finish_pg_upgrade();'; +END; +$function$; + +COMMENT ON FUNCTION ag_catalog.age_prepare_pg_upgrade() IS +'Prepares an AGE database for pg_upgrade by converting ag_graph.namespace from regnamespace to oid type. Run this before pg_upgrade.'; + +-- +-- age_finish_pg_upgrade() +-- +-- Completes the pg_upgrade process by remapping stale OIDs in ag_graph and +-- ag_label tables to their new values, then restores the original schema. +-- After pg_upgrade, the namespace OIDs stored in these tables no longer match +-- the actual pg_namespace OIDs because PostgreSQL assigns new OIDs to schemas +-- during the upgrade. +-- +-- This function: +-- 1. Reads the backup table created by age_prepare_pg_upgrade() +-- 2. Looks up new namespace OIDs by schema name +-- 3. Updates ag_label.graph references +-- 4. Updates ag_graph.graphid and ag_graph.namespace +-- 5. Restores namespace column to regnamespace type +-- 6. Cleans up the backup table and view +-- 7. Invalidates AGE caches to ensure cypher queries work immediately +-- +-- Returns: void +-- Side effects: Updates OIDs in ag_graph and ag_label tables, restores schema +-- +CREATE FUNCTION ag_catalog.age_finish_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + mapping_count integer; + updated_labels integer; + updated_graphs integer; +BEGIN + -- Check if backup table exists + IF NOT EXISTS ( + SELECT 1 FROM information_schema.tables + WHERE table_schema = 'public' + AND table_name = '_age_pg_upgrade_backup' + ) THEN + RAISE EXCEPTION 'Backup table public._age_pg_upgrade_backup not found. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Check if namespace column is oid type (was properly prepared) + IF NOT EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE EXCEPTION 'ag_graph.namespace is not oid type. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Create temporary mapping table with old and new OIDs + CREATE TEMP TABLE _graphid_mapping AS + SELECT + b.old_graphid, + b.graph_name, + n.oid AS new_graphid + FROM public._age_pg_upgrade_backup b + JOIN pg_namespace n ON n.nspname = b.namespace_name; + + GET DIAGNOSTICS mapping_count = ROW_COUNT; + + IF mapping_count = 0 THEN + RAISE EXCEPTION 'No OID mappings found. Schema names may have changed.'; + END IF; Review Comment: The join to pg_namespace uses n.nspname = b.namespace_name. Because namespace_name is stored as regnamespace::text (which may be quoted), this join can fail and silently drop mappings. Consider storing nspname in the backup (or normalizing/stripping quotes) and also validating that every backup row got a mapping (e.g., compare mapping_count to backup row count and raise if mismatched). ########## sql/age_pg_upgrade.sql: ########## @@ -0,0 +1,435 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. The ag_graph.namespace column uses +-- the regnamespace type which is not supported by pg_upgrade. These functions +-- temporarily convert the schema to be pg_upgrade compatible, then restore it +-- to the original state after the upgrade completes. +-- +-- Usage: +-- 1. Before pg_upgrade: SELECT age_prepare_pg_upgrade(); +-- 2. Run pg_upgrade as normal +-- 3. After pg_upgrade: SELECT age_finish_pg_upgrade(); +-- +-- To cancel an upgrade after preparation (before running pg_upgrade): +-- SELECT age_revert_pg_upgrade_changes(); +-- + +-- +-- age_prepare_pg_upgrade() +-- +-- Prepares an AGE database for pg_upgrade by converting the ag_graph.namespace +-- column from regnamespace to oid type. This is necessary because pg_upgrade +-- does not support the regnamespace type in user tables. +-- +-- This function: +-- 1. Creates a backup table with graph name to namespace name mappings +-- 2. Drops the existing namespace index +-- 3. Converts the namespace column from regnamespace to oid +-- 4. Recreates the namespace index with oid type +-- 5. Creates a view for backward-compatible namespace display +-- +-- Returns: void +-- Side effects: Modifies ag_graph table structure +-- +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ Review Comment: No regression coverage was added for the new pg_upgrade helper functions. Since the repo has an extensive regress suite, consider adding a small regression test that exercises: status reporting, prepare converting ag_graph.namespace to oid, revert restoring regnamespace, and idempotency/expected errors (finish without backup, etc.). ########## age--1.7.0--y.y.y.sql: ########## @@ -30,3 +30,341 @@ --* Please add all additions, deletions, and modifications to the end of this --* file. We need to keep the order of these changes. --* REMOVE ALL LINES ABOVE, and this one, that start with --* + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. +-- + +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + + -- Check if namespace column is already oid type (already prepared) + IF EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'Database already prepared for pg_upgrade (namespace is oid type).'; + RETURN; + END IF; + + -- Drop existing backup table if it exists (from a previous failed attempt) + DROP TABLE IF EXISTS public._age_pg_upgrade_backup; + + -- Create backup table with graph names mapped to namespace names + -- We store names (not OIDs) because names survive pg_upgrade while OIDs don't + CREATE TABLE public._age_pg_upgrade_backup AS + SELECT + g.graphid AS old_graphid, + g.name AS graph_name, + g.namespace::regnamespace::text AS namespace_name + FROM ag_catalog.ag_graph g; + + RAISE NOTICE 'Created backup table public._age_pg_upgrade_backup with % graph(s)', + (SELECT count(*) FROM public._age_pg_upgrade_backup); + + -- Drop the existing regnamespace-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column from regnamespace to oid + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE oid USING namespace::oid; + + -- Recreate the index with oid type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + -- Create a view for backward-compatible display of namespace as schema name + CREATE OR REPLACE VIEW ag_catalog.ag_graph_view AS + SELECT graphid, name, namespace::regnamespace AS namespace + FROM ag_catalog.ag_graph; + + RAISE NOTICE 'Successfully prepared database for pg_upgrade.'; + RAISE NOTICE 'The ag_graph.namespace column has been converted from regnamespace to oid.'; + RAISE NOTICE 'You can now run pg_upgrade.'; + RAISE NOTICE 'After pg_upgrade completes, run: SELECT age_finish_pg_upgrade();'; +END; +$function$; + +COMMENT ON FUNCTION ag_catalog.age_prepare_pg_upgrade() IS +'Prepares an AGE database for pg_upgrade by converting ag_graph.namespace from regnamespace to oid type. Run this before pg_upgrade.'; + +CREATE FUNCTION ag_catalog.age_finish_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + mapping_count integer; + updated_labels integer; + updated_graphs integer; +BEGIN + -- Check if backup table exists + IF NOT EXISTS ( + SELECT 1 FROM information_schema.tables + WHERE table_schema = 'public' + AND table_name = '_age_pg_upgrade_backup' + ) THEN + RAISE EXCEPTION 'Backup table public._age_pg_upgrade_backup not found. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Check if namespace column is oid type (was properly prepared) + IF NOT EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE EXCEPTION 'ag_graph.namespace is not oid type. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Create temporary mapping table with old and new OIDs + CREATE TEMP TABLE _graphid_mapping AS + SELECT + b.old_graphid, + b.graph_name, + n.oid AS new_graphid + FROM public._age_pg_upgrade_backup b + JOIN pg_namespace n ON n.nspname = b.namespace_name; + + GET DIAGNOSTICS mapping_count = ROW_COUNT; + + IF mapping_count = 0 THEN + RAISE EXCEPTION 'No OID mappings found. Schema names may have changed.'; + END IF; + + RAISE NOTICE 'Found % graph(s) to remap', mapping_count; + + -- Temporarily drop foreign key constraint + ALTER TABLE ag_catalog.ag_label DROP CONSTRAINT IF EXISTS fk_graph_oid; + + -- Update ag_label.graph references to use new OIDs + UPDATE ag_catalog.ag_label l + SET graph = m.new_graphid + FROM _graphid_mapping m + WHERE l.graph = m.old_graphid; + + GET DIAGNOSTICS updated_labels = ROW_COUNT; + RAISE NOTICE 'Updated % label record(s)', updated_labels; + + -- Update ag_graph.graphid and ag_graph.namespace to new OIDs + UPDATE ag_catalog.ag_graph g + SET graphid = m.new_graphid, + namespace = m.new_graphid + FROM _graphid_mapping m + WHERE g.graphid = m.old_graphid; + + RAISE NOTICE 'Updated % graph record(s)', updated_graphs; + + -- Restore foreign key constraint + ALTER TABLE ag_catalog.ag_label + ADD CONSTRAINT fk_graph_oid + FOREIGN KEY(graph) REFERENCES ag_catalog.ag_graph(graphid); + + -- Clean up temporary mapping table + DROP TABLE _graphid_mapping; + DROP TABLE public._age_pg_upgrade_backup; + + RAISE NOTICE 'Successfully completed pg_upgrade OID remapping.'; + + -- + -- Restore original schema (revert namespace to regnamespace) + -- + RAISE NOTICE 'Restoring original schema...'; + + -- Drop the view (no longer needed with regnamespace) + DROP VIEW IF EXISTS ag_catalog.ag_graph_view; + + -- Drop the existing oid-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column back to regnamespace + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE regnamespace USING namespace::regnamespace; + + -- Recreate the index with regnamespace type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + RAISE NOTICE 'Successfully restored ag_graph.namespace to regnamespace type.'; + + -- + -- Invalidate AGE's internal caches by touching each graph's namespace + -- AGE registers a syscache callback on NAMESPACEOID, so altering a schema + -- triggers cache invalidation. This ensures cypher queries work immediately + -- without requiring a session reconnect. + -- + RAISE NOTICE 'Invalidating AGE caches...'; + PERFORM pg_catalog.pg_advisory_lock(hashtext('age_finish_pg_upgrade')); + BEGIN + -- Touch each graph's namespace to invalidate caches + DECLARE + graph_rec RECORD; + BEGIN + FOR graph_rec IN SELECT namespace::text AS ns_name FROM ag_catalog.ag_graph + LOOP + EXECUTE format('ALTER SCHEMA %I OWNER TO CURRENT_USER', graph_rec.ns_name); + END LOOP; + END; + END; + PERFORM pg_catalog.pg_advisory_unlock(hashtext('age_finish_pg_upgrade')); + Review Comment: The cache invalidation uses pg_advisory_lock()/pg_advisory_unlock() (session-level). If an error occurs while looping/altering schemas, the unlock won't execute and the session can keep the lock, blocking future calls. Prefer pg_advisory_xact_lock() or wrap the block with EXCEPTION handling that always unlocks. ########## age--1.7.0--y.y.y.sql: ########## @@ -30,3 +30,341 @@ --* Please add all additions, deletions, and modifications to the end of this --* file. We need to keep the order of these changes. --* REMOVE ALL LINES ABOVE, and this one, that start with --* + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. +-- + +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + + -- Check if namespace column is already oid type (already prepared) + IF EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'Database already prepared for pg_upgrade (namespace is oid type).'; + RETURN; + END IF; + + -- Drop existing backup table if it exists (from a previous failed attempt) + DROP TABLE IF EXISTS public._age_pg_upgrade_backup; + + -- Create backup table with graph names mapped to namespace names + -- We store names (not OIDs) because names survive pg_upgrade while OIDs don't + CREATE TABLE public._age_pg_upgrade_backup AS + SELECT + g.graphid AS old_graphid, + g.name AS graph_name, + g.namespace::regnamespace::text AS namespace_name + FROM ag_catalog.ag_graph g; + + RAISE NOTICE 'Created backup table public._age_pg_upgrade_backup with % graph(s)', + (SELECT count(*) FROM public._age_pg_upgrade_backup); + + -- Drop the existing regnamespace-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column from regnamespace to oid + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE oid USING namespace::oid; + + -- Recreate the index with oid type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + -- Create a view for backward-compatible display of namespace as schema name + CREATE OR REPLACE VIEW ag_catalog.ag_graph_view AS + SELECT graphid, name, namespace::regnamespace AS namespace + FROM ag_catalog.ag_graph; + + RAISE NOTICE 'Successfully prepared database for pg_upgrade.'; + RAISE NOTICE 'The ag_graph.namespace column has been converted from regnamespace to oid.'; + RAISE NOTICE 'You can now run pg_upgrade.'; + RAISE NOTICE 'After pg_upgrade completes, run: SELECT age_finish_pg_upgrade();'; +END; +$function$; + +COMMENT ON FUNCTION ag_catalog.age_prepare_pg_upgrade() IS +'Prepares an AGE database for pg_upgrade by converting ag_graph.namespace from regnamespace to oid type. Run this before pg_upgrade.'; + +CREATE FUNCTION ag_catalog.age_finish_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + mapping_count integer; + updated_labels integer; + updated_graphs integer; +BEGIN + -- Check if backup table exists + IF NOT EXISTS ( + SELECT 1 FROM information_schema.tables + WHERE table_schema = 'public' + AND table_name = '_age_pg_upgrade_backup' + ) THEN + RAISE EXCEPTION 'Backup table public._age_pg_upgrade_backup not found. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Check if namespace column is oid type (was properly prepared) + IF NOT EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE EXCEPTION 'ag_graph.namespace is not oid type. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Create temporary mapping table with old and new OIDs + CREATE TEMP TABLE _graphid_mapping AS + SELECT + b.old_graphid, + b.graph_name, + n.oid AS new_graphid + FROM public._age_pg_upgrade_backup b + JOIN pg_namespace n ON n.nspname = b.namespace_name; + + GET DIAGNOSTICS mapping_count = ROW_COUNT; + + IF mapping_count = 0 THEN + RAISE EXCEPTION 'No OID mappings found. Schema names may have changed.'; + END IF; Review Comment: The join to pg_namespace uses n.nspname = b.namespace_name, but namespace_name was stored from regnamespace::text (potentially quoted), so this join can fail and drop mappings. Consider normalizing the stored schema name and validating that every backup row has a mapping (e.g., compare mapping_count to backup row count and raise if mismatched). ########## age--1.7.0--y.y.y.sql: ########## @@ -30,3 +30,341 @@ --* Please add all additions, deletions, and modifications to the end of this --* file. We need to keep the order of these changes. --* REMOVE ALL LINES ABOVE, and this one, that start with --* + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. +-- + +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + + -- Check if namespace column is already oid type (already prepared) + IF EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'Database already prepared for pg_upgrade (namespace is oid type).'; + RETURN; + END IF; + + -- Drop existing backup table if it exists (from a previous failed attempt) + DROP TABLE IF EXISTS public._age_pg_upgrade_backup; + + -- Create backup table with graph names mapped to namespace names + -- We store names (not OIDs) because names survive pg_upgrade while OIDs don't + CREATE TABLE public._age_pg_upgrade_backup AS + SELECT + g.graphid AS old_graphid, + g.name AS graph_name, + g.namespace::regnamespace::text AS namespace_name + FROM ag_catalog.ag_graph g; Review Comment: The backup stores namespace_name as g.namespace::regnamespace::text, which can include quoted identifiers. age_finish_pg_upgrade() later joins this to pg_namespace.nspname (unquoted), so remapping can fail for schemas that require quoting. Store pg_namespace.nspname instead (e.g., join via namespace::oid) to make the mapping stable. ```suggestion n.nspname AS namespace_name FROM ag_catalog.ag_graph g JOIN pg_namespace n ON n.oid = g.namespace::oid; ``` ########## sql/age_pg_upgrade.sql: ########## @@ -0,0 +1,435 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. The ag_graph.namespace column uses +-- the regnamespace type which is not supported by pg_upgrade. These functions +-- temporarily convert the schema to be pg_upgrade compatible, then restore it +-- to the original state after the upgrade completes. +-- +-- Usage: +-- 1. Before pg_upgrade: SELECT age_prepare_pg_upgrade(); +-- 2. Run pg_upgrade as normal +-- 3. After pg_upgrade: SELECT age_finish_pg_upgrade(); +-- +-- To cancel an upgrade after preparation (before running pg_upgrade): +-- SELECT age_revert_pg_upgrade_changes(); +-- + +-- +-- age_prepare_pg_upgrade() +-- +-- Prepares an AGE database for pg_upgrade by converting the ag_graph.namespace +-- column from regnamespace to oid type. This is necessary because pg_upgrade +-- does not support the regnamespace type in user tables. +-- +-- This function: +-- 1. Creates a backup table with graph name to namespace name mappings +-- 2. Drops the existing namespace index +-- 3. Converts the namespace column from regnamespace to oid +-- 4. Recreates the namespace index with oid type +-- 5. Creates a view for backward-compatible namespace display +-- +-- Returns: void +-- Side effects: Modifies ag_graph table structure +-- +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + + -- Check if namespace column is already oid type (already prepared) + IF EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'Database already prepared for pg_upgrade (namespace is oid type).'; + RETURN; + END IF; + + -- Drop existing backup table if it exists (from a previous failed attempt) + DROP TABLE IF EXISTS public._age_pg_upgrade_backup; + + -- Create backup table with graph names mapped to namespace names + -- We store names (not OIDs) because names survive pg_upgrade while OIDs don't + CREATE TABLE public._age_pg_upgrade_backup AS + SELECT + g.graphid AS old_graphid, + g.name AS graph_name, + g.namespace::regnamespace::text AS namespace_name + FROM ag_catalog.ag_graph g; + + RAISE NOTICE 'Created backup table public._age_pg_upgrade_backup with % graph(s)', + (SELECT count(*) FROM public._age_pg_upgrade_backup); + + -- Drop the existing regnamespace-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column from regnamespace to oid + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE oid USING namespace::oid; + + -- Recreate the index with oid type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + -- Create a view for backward-compatible display of namespace as schema name + CREATE OR REPLACE VIEW ag_catalog.ag_graph_view AS + SELECT graphid, name, namespace::regnamespace AS namespace + FROM ag_catalog.ag_graph; + + RAISE NOTICE 'Successfully prepared database for pg_upgrade.'; + RAISE NOTICE 'The ag_graph.namespace column has been converted from regnamespace to oid.'; + RAISE NOTICE 'You can now run pg_upgrade.'; + RAISE NOTICE 'After pg_upgrade completes, run: SELECT age_finish_pg_upgrade();'; +END; +$function$; + +COMMENT ON FUNCTION ag_catalog.age_prepare_pg_upgrade() IS +'Prepares an AGE database for pg_upgrade by converting ag_graph.namespace from regnamespace to oid type. Run this before pg_upgrade.'; + +-- +-- age_finish_pg_upgrade() +-- +-- Completes the pg_upgrade process by remapping stale OIDs in ag_graph and +-- ag_label tables to their new values, then restores the original schema. +-- After pg_upgrade, the namespace OIDs stored in these tables no longer match +-- the actual pg_namespace OIDs because PostgreSQL assigns new OIDs to schemas +-- during the upgrade. +-- +-- This function: +-- 1. Reads the backup table created by age_prepare_pg_upgrade() +-- 2. Looks up new namespace OIDs by schema name +-- 3. Updates ag_label.graph references +-- 4. Updates ag_graph.graphid and ag_graph.namespace +-- 5. Restores namespace column to regnamespace type +-- 6. Cleans up the backup table and view +-- 7. Invalidates AGE caches to ensure cypher queries work immediately +-- +-- Returns: void +-- Side effects: Updates OIDs in ag_graph and ag_label tables, restores schema +-- +CREATE FUNCTION ag_catalog.age_finish_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + mapping_count integer; + updated_labels integer; + updated_graphs integer; +BEGIN + -- Check if backup table exists + IF NOT EXISTS ( + SELECT 1 FROM information_schema.tables + WHERE table_schema = 'public' + AND table_name = '_age_pg_upgrade_backup' + ) THEN + RAISE EXCEPTION 'Backup table public._age_pg_upgrade_backup not found. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Check if namespace column is oid type (was properly prepared) + IF NOT EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE EXCEPTION 'ag_graph.namespace is not oid type. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Create temporary mapping table with old and new OIDs + CREATE TEMP TABLE _graphid_mapping AS + SELECT + b.old_graphid, + b.graph_name, + n.oid AS new_graphid + FROM public._age_pg_upgrade_backup b + JOIN pg_namespace n ON n.nspname = b.namespace_name; + + GET DIAGNOSTICS mapping_count = ROW_COUNT; + + IF mapping_count = 0 THEN + RAISE EXCEPTION 'No OID mappings found. Schema names may have changed.'; + END IF; + + RAISE NOTICE 'Found % graph(s) to remap', mapping_count; + + -- Temporarily drop foreign key constraint + ALTER TABLE ag_catalog.ag_label DROP CONSTRAINT IF EXISTS fk_graph_oid; + + -- Update ag_label.graph references to use new OIDs + UPDATE ag_catalog.ag_label l + SET graph = m.new_graphid + FROM _graphid_mapping m + WHERE l.graph = m.old_graphid; + + GET DIAGNOSTICS updated_labels = ROW_COUNT; + RAISE NOTICE 'Updated % label record(s)', updated_labels; + + -- Update ag_graph.graphid and ag_graph.namespace to new OIDs + UPDATE ag_catalog.ag_graph g + SET graphid = m.new_graphid, + namespace = m.new_graphid + FROM _graphid_mapping m + WHERE g.graphid = m.old_graphid; + Review Comment: updated_graphs is never assigned (missing GET DIAGNOSTICS ... = ROW_COUNT after the UPDATE of ag_graph), so the NOTICE will report NULL and you lose a key correctness check. Capture ROW_COUNT after the UPDATE and consider asserting it matches the expected number of graphs being remapped. ```suggestion GET DIAGNOSTICS updated_graphs = ROW_COUNT; ``` ########## age--1.7.0--y.y.y.sql: ########## @@ -30,3 +30,341 @@ --* Please add all additions, deletions, and modifications to the end of this --* file. We need to keep the order of these changes. --* REMOVE ALL LINES ABOVE, and this one, that start with --* + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. +-- + +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + + -- Check if namespace column is already oid type (already prepared) + IF EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'Database already prepared for pg_upgrade (namespace is oid type).'; + RETURN; + END IF; + + -- Drop existing backup table if it exists (from a previous failed attempt) + DROP TABLE IF EXISTS public._age_pg_upgrade_backup; + + -- Create backup table with graph names mapped to namespace names + -- We store names (not OIDs) because names survive pg_upgrade while OIDs don't + CREATE TABLE public._age_pg_upgrade_backup AS + SELECT + g.graphid AS old_graphid, + g.name AS graph_name, + g.namespace::regnamespace::text AS namespace_name + FROM ag_catalog.ag_graph g; + + RAISE NOTICE 'Created backup table public._age_pg_upgrade_backup with % graph(s)', + (SELECT count(*) FROM public._age_pg_upgrade_backup); + + -- Drop the existing regnamespace-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column from regnamespace to oid + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE oid USING namespace::oid; + + -- Recreate the index with oid type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + -- Create a view for backward-compatible display of namespace as schema name + CREATE OR REPLACE VIEW ag_catalog.ag_graph_view AS + SELECT graphid, name, namespace::regnamespace AS namespace + FROM ag_catalog.ag_graph; + + RAISE NOTICE 'Successfully prepared database for pg_upgrade.'; + RAISE NOTICE 'The ag_graph.namespace column has been converted from regnamespace to oid.'; + RAISE NOTICE 'You can now run pg_upgrade.'; + RAISE NOTICE 'After pg_upgrade completes, run: SELECT age_finish_pg_upgrade();'; +END; +$function$; + +COMMENT ON FUNCTION ag_catalog.age_prepare_pg_upgrade() IS +'Prepares an AGE database for pg_upgrade by converting ag_graph.namespace from regnamespace to oid type. Run this before pg_upgrade.'; + +CREATE FUNCTION ag_catalog.age_finish_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + mapping_count integer; + updated_labels integer; + updated_graphs integer; +BEGIN + -- Check if backup table exists + IF NOT EXISTS ( + SELECT 1 FROM information_schema.tables + WHERE table_schema = 'public' + AND table_name = '_age_pg_upgrade_backup' + ) THEN + RAISE EXCEPTION 'Backup table public._age_pg_upgrade_backup not found. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Check if namespace column is oid type (was properly prepared) + IF NOT EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE EXCEPTION 'ag_graph.namespace is not oid type. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Create temporary mapping table with old and new OIDs + CREATE TEMP TABLE _graphid_mapping AS + SELECT + b.old_graphid, + b.graph_name, + n.oid AS new_graphid + FROM public._age_pg_upgrade_backup b + JOIN pg_namespace n ON n.nspname = b.namespace_name; + + GET DIAGNOSTICS mapping_count = ROW_COUNT; + + IF mapping_count = 0 THEN + RAISE EXCEPTION 'No OID mappings found. Schema names may have changed.'; + END IF; + + RAISE NOTICE 'Found % graph(s) to remap', mapping_count; + + -- Temporarily drop foreign key constraint + ALTER TABLE ag_catalog.ag_label DROP CONSTRAINT IF EXISTS fk_graph_oid; + + -- Update ag_label.graph references to use new OIDs + UPDATE ag_catalog.ag_label l + SET graph = m.new_graphid + FROM _graphid_mapping m + WHERE l.graph = m.old_graphid; + + GET DIAGNOSTICS updated_labels = ROW_COUNT; + RAISE NOTICE 'Updated % label record(s)', updated_labels; + + -- Update ag_graph.graphid and ag_graph.namespace to new OIDs + UPDATE ag_catalog.ag_graph g + SET graphid = m.new_graphid, + namespace = m.new_graphid + FROM _graphid_mapping m + WHERE g.graphid = m.old_graphid; + + RAISE NOTICE 'Updated % graph record(s)', updated_graphs; + + -- Restore foreign key constraint + ALTER TABLE ag_catalog.ag_label + ADD CONSTRAINT fk_graph_oid + FOREIGN KEY(graph) REFERENCES ag_catalog.ag_graph(graphid); + + -- Clean up temporary mapping table + DROP TABLE _graphid_mapping; + DROP TABLE public._age_pg_upgrade_backup; + + RAISE NOTICE 'Successfully completed pg_upgrade OID remapping.'; + + -- + -- Restore original schema (revert namespace to regnamespace) + -- + RAISE NOTICE 'Restoring original schema...'; + + -- Drop the view (no longer needed with regnamespace) + DROP VIEW IF EXISTS ag_catalog.ag_graph_view; + + -- Drop the existing oid-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column back to regnamespace + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE regnamespace USING namespace::regnamespace; + + -- Recreate the index with regnamespace type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + RAISE NOTICE 'Successfully restored ag_graph.namespace to regnamespace type.'; + + -- + -- Invalidate AGE's internal caches by touching each graph's namespace + -- AGE registers a syscache callback on NAMESPACEOID, so altering a schema + -- triggers cache invalidation. This ensures cypher queries work immediately + -- without requiring a session reconnect. + -- + RAISE NOTICE 'Invalidating AGE caches...'; + PERFORM pg_catalog.pg_advisory_lock(hashtext('age_finish_pg_upgrade')); + BEGIN + -- Touch each graph's namespace to invalidate caches + DECLARE + graph_rec RECORD; + BEGIN + FOR graph_rec IN SELECT namespace::text AS ns_name FROM ag_catalog.ag_graph + LOOP + EXECUTE format('ALTER SCHEMA %I OWNER TO CURRENT_USER', graph_rec.ns_name); + END LOOP; + END; + END; + PERFORM pg_catalog.pg_advisory_unlock(hashtext('age_finish_pg_upgrade')); + + RAISE NOTICE ''; + RAISE NOTICE 'pg_upgrade complete. All graph data has been preserved.'; +END; +$function$; + +COMMENT ON FUNCTION ag_catalog.age_finish_pg_upgrade() IS +'Completes pg_upgrade by remapping stale OIDs and restoring the original schema. Run this after pg_upgrade.'; + +CREATE FUNCTION ag_catalog.age_revert_pg_upgrade_changes() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +BEGIN + -- Check if namespace column is oid type (needs reverting) + IF NOT EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'ag_graph.namespace is already regnamespace type. Nothing to revert.'; + RETURN; + END IF; + + -- Drop the view (no longer needed with regnamespace) + DROP VIEW IF EXISTS ag_catalog.ag_graph_view; + + -- Drop the existing oid-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column back to regnamespace + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE regnamespace USING namespace::regnamespace; + + -- Recreate the index with regnamespace type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + -- + -- Invalidate AGE's internal caches by touching each graph's namespace + -- + PERFORM pg_catalog.pg_advisory_lock(hashtext('age_revert_pg_upgrade')); + BEGIN + DECLARE + graph_rec RECORD; + BEGIN + FOR graph_rec IN SELECT namespace::text AS ns_name FROM ag_catalog.ag_graph + LOOP + EXECUTE format('ALTER SCHEMA %I OWNER TO CURRENT_USER', graph_rec.ns_name); + END LOOP; + END; + END; + PERFORM pg_catalog.pg_advisory_unlock(hashtext('age_revert_pg_upgrade')); + Review Comment: age_revert_pg_upgrade_changes() uses session-level advisory locks without exception safety (lock may remain held on error) and alters schema ownership to CURRENT_USER (persistent side effect / possible privilege failure). Prefer xact-level advisory locks and an invalidation method that doesn't leave owners changed (and avoid identifier double-quoting). ```suggestion PERFORM pg_catalog.pg_advisory_xact_lock(hashtext('age_revert_pg_upgrade')); <<invalidate_caches>> DECLARE graph_rec RECORD; ns_owner text; BEGIN FOR graph_rec IN SELECT namespace::text AS ns_name FROM ag_catalog.ag_graph LOOP SELECT n.nspowner::regrole::text INTO ns_owner FROM pg_namespace n WHERE n.nspname = graph_rec.ns_name; IF ns_owner IS NOT NULL THEN EXECUTE format( 'ALTER SCHEMA %s OWNER TO %s', pg_catalog.quote_ident(graph_rec.ns_name), pg_catalog.quote_ident(ns_owner) ); END IF; END LOOP; END invalidate_caches; ``` ########## age--1.7.0--y.y.y.sql: ########## @@ -30,3 +30,341 @@ --* Please add all additions, deletions, and modifications to the end of this --* file. We need to keep the order of these changes. --* REMOVE ALL LINES ABOVE, and this one, that start with --* + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. +-- + +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + + -- Check if namespace column is already oid type (already prepared) + IF EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'Database already prepared for pg_upgrade (namespace is oid type).'; + RETURN; + END IF; + + -- Drop existing backup table if it exists (from a previous failed attempt) + DROP TABLE IF EXISTS public._age_pg_upgrade_backup; + + -- Create backup table with graph names mapped to namespace names + -- We store names (not OIDs) because names survive pg_upgrade while OIDs don't + CREATE TABLE public._age_pg_upgrade_backup AS + SELECT + g.graphid AS old_graphid, + g.name AS graph_name, + g.namespace::regnamespace::text AS namespace_name + FROM ag_catalog.ag_graph g; + + RAISE NOTICE 'Created backup table public._age_pg_upgrade_backup with % graph(s)', + (SELECT count(*) FROM public._age_pg_upgrade_backup); + + -- Drop the existing regnamespace-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column from regnamespace to oid + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE oid USING namespace::oid; + + -- Recreate the index with oid type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + -- Create a view for backward-compatible display of namespace as schema name + CREATE OR REPLACE VIEW ag_catalog.ag_graph_view AS + SELECT graphid, name, namespace::regnamespace AS namespace + FROM ag_catalog.ag_graph; + + RAISE NOTICE 'Successfully prepared database for pg_upgrade.'; + RAISE NOTICE 'The ag_graph.namespace column has been converted from regnamespace to oid.'; + RAISE NOTICE 'You can now run pg_upgrade.'; + RAISE NOTICE 'After pg_upgrade completes, run: SELECT age_finish_pg_upgrade();'; +END; +$function$; + +COMMENT ON FUNCTION ag_catalog.age_prepare_pg_upgrade() IS +'Prepares an AGE database for pg_upgrade by converting ag_graph.namespace from regnamespace to oid type. Run this before pg_upgrade.'; + +CREATE FUNCTION ag_catalog.age_finish_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + mapping_count integer; + updated_labels integer; + updated_graphs integer; +BEGIN + -- Check if backup table exists + IF NOT EXISTS ( + SELECT 1 FROM information_schema.tables + WHERE table_schema = 'public' + AND table_name = '_age_pg_upgrade_backup' + ) THEN + RAISE EXCEPTION 'Backup table public._age_pg_upgrade_backup not found. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Check if namespace column is oid type (was properly prepared) + IF NOT EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE EXCEPTION 'ag_graph.namespace is not oid type. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Create temporary mapping table with old and new OIDs + CREATE TEMP TABLE _graphid_mapping AS + SELECT + b.old_graphid, + b.graph_name, + n.oid AS new_graphid + FROM public._age_pg_upgrade_backup b + JOIN pg_namespace n ON n.nspname = b.namespace_name; + + GET DIAGNOSTICS mapping_count = ROW_COUNT; + + IF mapping_count = 0 THEN + RAISE EXCEPTION 'No OID mappings found. Schema names may have changed.'; + END IF; + + RAISE NOTICE 'Found % graph(s) to remap', mapping_count; + + -- Temporarily drop foreign key constraint + ALTER TABLE ag_catalog.ag_label DROP CONSTRAINT IF EXISTS fk_graph_oid; + + -- Update ag_label.graph references to use new OIDs + UPDATE ag_catalog.ag_label l + SET graph = m.new_graphid + FROM _graphid_mapping m + WHERE l.graph = m.old_graphid; + + GET DIAGNOSTICS updated_labels = ROW_COUNT; + RAISE NOTICE 'Updated % label record(s)', updated_labels; + + -- Update ag_graph.graphid and ag_graph.namespace to new OIDs + UPDATE ag_catalog.ag_graph g + SET graphid = m.new_graphid, + namespace = m.new_graphid + FROM _graphid_mapping m + WHERE g.graphid = m.old_graphid; + + RAISE NOTICE 'Updated % graph record(s)', updated_graphs; + + -- Restore foreign key constraint + ALTER TABLE ag_catalog.ag_label + ADD CONSTRAINT fk_graph_oid + FOREIGN KEY(graph) REFERENCES ag_catalog.ag_graph(graphid); + + -- Clean up temporary mapping table + DROP TABLE _graphid_mapping; + DROP TABLE public._age_pg_upgrade_backup; + + RAISE NOTICE 'Successfully completed pg_upgrade OID remapping.'; + + -- + -- Restore original schema (revert namespace to regnamespace) + -- + RAISE NOTICE 'Restoring original schema...'; + + -- Drop the view (no longer needed with regnamespace) + DROP VIEW IF EXISTS ag_catalog.ag_graph_view; + + -- Drop the existing oid-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column back to regnamespace + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE regnamespace USING namespace::regnamespace; + + -- Recreate the index with regnamespace type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + RAISE NOTICE 'Successfully restored ag_graph.namespace to regnamespace type.'; + + -- + -- Invalidate AGE's internal caches by touching each graph's namespace + -- AGE registers a syscache callback on NAMESPACEOID, so altering a schema + -- triggers cache invalidation. This ensures cypher queries work immediately + -- without requiring a session reconnect. + -- + RAISE NOTICE 'Invalidating AGE caches...'; + PERFORM pg_catalog.pg_advisory_lock(hashtext('age_finish_pg_upgrade')); + BEGIN + -- Touch each graph's namespace to invalidate caches + DECLARE + graph_rec RECORD; + BEGIN + FOR graph_rec IN SELECT namespace::text AS ns_name FROM ag_catalog.ag_graph + LOOP + EXECUTE format('ALTER SCHEMA %I OWNER TO CURRENT_USER', graph_rec.ns_name); Review Comment: ALTER SCHEMA ... OWNER TO CURRENT_USER is not a harmless "touch": it can permanently change schema ownership and may fail depending on privileges. Also, namespace::text (from regnamespace) is already quoted, so format('%I', ns_name) can over-quote and fail for schemas needing quotes. Consider looking up pg_namespace.nspname and current owner and using an invalidation approach that preserves ownership and quotes identifiers exactly once. ```suggestion -- We re-assign ownership to the current owner to avoid changing -- schema ownership while still modifying pg_namespace rows. DECLARE graph_rec RECORD; BEGIN FOR graph_rec IN SELECT DISTINCT n.nspname AS ns_name, r.rolname AS owner_name FROM ag_catalog.ag_graph g JOIN pg_namespace n ON n.oid = g.namespace JOIN pg_roles r ON r.oid = n.nspowner LOOP EXECUTE format( 'ALTER SCHEMA %I OWNER TO %I', graph_rec.ns_name, graph_rec.owner_name ); ``` ########## age--1.7.0--y.y.y.sql: ########## @@ -30,3 +30,341 @@ --* Please add all additions, deletions, and modifications to the end of this --* file. We need to keep the order of these changes. --* REMOVE ALL LINES ABOVE, and this one, that start with --* + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. +-- + +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + + -- Check if namespace column is already oid type (already prepared) + IF EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'Database already prepared for pg_upgrade (namespace is oid type).'; + RETURN; + END IF; + + -- Drop existing backup table if it exists (from a previous failed attempt) + DROP TABLE IF EXISTS public._age_pg_upgrade_backup; + + -- Create backup table with graph names mapped to namespace names + -- We store names (not OIDs) because names survive pg_upgrade while OIDs don't + CREATE TABLE public._age_pg_upgrade_backup AS + SELECT + g.graphid AS old_graphid, + g.name AS graph_name, + g.namespace::regnamespace::text AS namespace_name + FROM ag_catalog.ag_graph g; + + RAISE NOTICE 'Created backup table public._age_pg_upgrade_backup with % graph(s)', + (SELECT count(*) FROM public._age_pg_upgrade_backup); + + -- Drop the existing regnamespace-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column from regnamespace to oid + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE oid USING namespace::oid; + + -- Recreate the index with oid type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + -- Create a view for backward-compatible display of namespace as schema name + CREATE OR REPLACE VIEW ag_catalog.ag_graph_view AS + SELECT graphid, name, namespace::regnamespace AS namespace + FROM ag_catalog.ag_graph; + + RAISE NOTICE 'Successfully prepared database for pg_upgrade.'; + RAISE NOTICE 'The ag_graph.namespace column has been converted from regnamespace to oid.'; + RAISE NOTICE 'You can now run pg_upgrade.'; + RAISE NOTICE 'After pg_upgrade completes, run: SELECT age_finish_pg_upgrade();'; +END; +$function$; + +COMMENT ON FUNCTION ag_catalog.age_prepare_pg_upgrade() IS +'Prepares an AGE database for pg_upgrade by converting ag_graph.namespace from regnamespace to oid type. Run this before pg_upgrade.'; + +CREATE FUNCTION ag_catalog.age_finish_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + mapping_count integer; + updated_labels integer; + updated_graphs integer; +BEGIN + -- Check if backup table exists + IF NOT EXISTS ( + SELECT 1 FROM information_schema.tables + WHERE table_schema = 'public' + AND table_name = '_age_pg_upgrade_backup' + ) THEN + RAISE EXCEPTION 'Backup table public._age_pg_upgrade_backup not found. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Check if namespace column is oid type (was properly prepared) + IF NOT EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE EXCEPTION 'ag_graph.namespace is not oid type. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Create temporary mapping table with old and new OIDs + CREATE TEMP TABLE _graphid_mapping AS + SELECT + b.old_graphid, + b.graph_name, + n.oid AS new_graphid + FROM public._age_pg_upgrade_backup b + JOIN pg_namespace n ON n.nspname = b.namespace_name; + + GET DIAGNOSTICS mapping_count = ROW_COUNT; + + IF mapping_count = 0 THEN + RAISE EXCEPTION 'No OID mappings found. Schema names may have changed.'; + END IF; + + RAISE NOTICE 'Found % graph(s) to remap', mapping_count; + + -- Temporarily drop foreign key constraint + ALTER TABLE ag_catalog.ag_label DROP CONSTRAINT IF EXISTS fk_graph_oid; + + -- Update ag_label.graph references to use new OIDs + UPDATE ag_catalog.ag_label l + SET graph = m.new_graphid + FROM _graphid_mapping m + WHERE l.graph = m.old_graphid; + + GET DIAGNOSTICS updated_labels = ROW_COUNT; + RAISE NOTICE 'Updated % label record(s)', updated_labels; + + -- Update ag_graph.graphid and ag_graph.namespace to new OIDs + UPDATE ag_catalog.ag_graph g + SET graphid = m.new_graphid, + namespace = m.new_graphid + FROM _graphid_mapping m + WHERE g.graphid = m.old_graphid; + + RAISE NOTICE 'Updated % graph record(s)', updated_graphs; + + -- Restore foreign key constraint + ALTER TABLE ag_catalog.ag_label + ADD CONSTRAINT fk_graph_oid + FOREIGN KEY(graph) REFERENCES ag_catalog.ag_graph(graphid); + + -- Clean up temporary mapping table + DROP TABLE _graphid_mapping; + DROP TABLE public._age_pg_upgrade_backup; + Review Comment: age_finish_pg_upgrade() drops public._age_pg_upgrade_backup before the schema restoration + cache invalidation steps run. If a later step fails, the backup is gone and recovery/reruns are harder. Consider dropping the backup only after all steps complete successfully. ########## sql/age_pg_upgrade.sql: ########## @@ -0,0 +1,435 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. The ag_graph.namespace column uses +-- the regnamespace type which is not supported by pg_upgrade. These functions +-- temporarily convert the schema to be pg_upgrade compatible, then restore it +-- to the original state after the upgrade completes. +-- +-- Usage: +-- 1. Before pg_upgrade: SELECT age_prepare_pg_upgrade(); +-- 2. Run pg_upgrade as normal +-- 3. After pg_upgrade: SELECT age_finish_pg_upgrade(); +-- +-- To cancel an upgrade after preparation (before running pg_upgrade): +-- SELECT age_revert_pg_upgrade_changes(); +-- + +-- +-- age_prepare_pg_upgrade() +-- +-- Prepares an AGE database for pg_upgrade by converting the ag_graph.namespace +-- column from regnamespace to oid type. This is necessary because pg_upgrade +-- does not support the regnamespace type in user tables. +-- +-- This function: +-- 1. Creates a backup table with graph name to namespace name mappings +-- 2. Drops the existing namespace index +-- 3. Converts the namespace column from regnamespace to oid +-- 4. Recreates the namespace index with oid type +-- 5. Creates a view for backward-compatible namespace display +-- +-- Returns: void +-- Side effects: Modifies ag_graph table structure +-- +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + + -- Check if namespace column is already oid type (already prepared) + IF EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'Database already prepared for pg_upgrade (namespace is oid type).'; + RETURN; + END IF; + + -- Drop existing backup table if it exists (from a previous failed attempt) + DROP TABLE IF EXISTS public._age_pg_upgrade_backup; + + -- Create backup table with graph names mapped to namespace names + -- We store names (not OIDs) because names survive pg_upgrade while OIDs don't + CREATE TABLE public._age_pg_upgrade_backup AS + SELECT + g.graphid AS old_graphid, + g.name AS graph_name, + g.namespace::regnamespace::text AS namespace_name + FROM ag_catalog.ag_graph g; Review Comment: The backup stores namespace_name as g.namespace::regnamespace::text, which can include quoted identifiers (e.g., "MySchema"). age_finish_pg_upgrade() later joins this value to pg_namespace.nspname (unquoted), so remapping can fail for schemas requiring quoting. Store the raw nspname (e.g., join via namespace::oid to pg_namespace) instead of regnamespace text output. ```suggestion n.nspname AS namespace_name FROM ag_catalog.ag_graph g JOIN pg_namespace n ON n.oid = g.namespace::oid; ``` ########## age--1.7.0--y.y.y.sql: ########## @@ -30,3 +30,341 @@ --* Please add all additions, deletions, and modifications to the end of this --* file. We need to keep the order of these changes. --* REMOVE ALL LINES ABOVE, and this one, that start with --* + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. +-- + +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + Review Comment: age_prepare_pg_upgrade() returns early when there are no rows in ag_catalog.ag_graph, but pg_upgrade compatibility depends on the presence of the regnamespace-typed column, not on row count. Consider still converting ag_graph.namespace to oid (backup can be empty) so pg_upgrade succeeds even with zero graphs. ```suggestion BEGIN ``` ########## sql/age_pg_upgrade.sql: ########## @@ -0,0 +1,435 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. The ag_graph.namespace column uses +-- the regnamespace type which is not supported by pg_upgrade. These functions +-- temporarily convert the schema to be pg_upgrade compatible, then restore it +-- to the original state after the upgrade completes. +-- +-- Usage: +-- 1. Before pg_upgrade: SELECT age_prepare_pg_upgrade(); +-- 2. Run pg_upgrade as normal +-- 3. After pg_upgrade: SELECT age_finish_pg_upgrade(); +-- +-- To cancel an upgrade after preparation (before running pg_upgrade): +-- SELECT age_revert_pg_upgrade_changes(); +-- + +-- +-- age_prepare_pg_upgrade() +-- +-- Prepares an AGE database for pg_upgrade by converting the ag_graph.namespace +-- column from regnamespace to oid type. This is necessary because pg_upgrade +-- does not support the regnamespace type in user tables. +-- +-- This function: +-- 1. Creates a backup table with graph name to namespace name mappings +-- 2. Drops the existing namespace index +-- 3. Converts the namespace column from regnamespace to oid +-- 4. Recreates the namespace index with oid type +-- 5. Creates a view for backward-compatible namespace display +-- +-- Returns: void +-- Side effects: Modifies ag_graph table structure +-- +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + + -- Check if namespace column is already oid type (already prepared) + IF EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'Database already prepared for pg_upgrade (namespace is oid type).'; + RETURN; + END IF; + + -- Drop existing backup table if it exists (from a previous failed attempt) + DROP TABLE IF EXISTS public._age_pg_upgrade_backup; + + -- Create backup table with graph names mapped to namespace names + -- We store names (not OIDs) because names survive pg_upgrade while OIDs don't + CREATE TABLE public._age_pg_upgrade_backup AS + SELECT + g.graphid AS old_graphid, + g.name AS graph_name, + g.namespace::regnamespace::text AS namespace_name + FROM ag_catalog.ag_graph g; + + RAISE NOTICE 'Created backup table public._age_pg_upgrade_backup with % graph(s)', + (SELECT count(*) FROM public._age_pg_upgrade_backup); + + -- Drop the existing regnamespace-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column from regnamespace to oid + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE oid USING namespace::oid; + + -- Recreate the index with oid type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + -- Create a view for backward-compatible display of namespace as schema name + CREATE OR REPLACE VIEW ag_catalog.ag_graph_view AS + SELECT graphid, name, namespace::regnamespace AS namespace + FROM ag_catalog.ag_graph; + + RAISE NOTICE 'Successfully prepared database for pg_upgrade.'; + RAISE NOTICE 'The ag_graph.namespace column has been converted from regnamespace to oid.'; + RAISE NOTICE 'You can now run pg_upgrade.'; + RAISE NOTICE 'After pg_upgrade completes, run: SELECT age_finish_pg_upgrade();'; +END; +$function$; + +COMMENT ON FUNCTION ag_catalog.age_prepare_pg_upgrade() IS +'Prepares an AGE database for pg_upgrade by converting ag_graph.namespace from regnamespace to oid type. Run this before pg_upgrade.'; + +-- +-- age_finish_pg_upgrade() +-- +-- Completes the pg_upgrade process by remapping stale OIDs in ag_graph and +-- ag_label tables to their new values, then restores the original schema. +-- After pg_upgrade, the namespace OIDs stored in these tables no longer match +-- the actual pg_namespace OIDs because PostgreSQL assigns new OIDs to schemas +-- during the upgrade. +-- +-- This function: +-- 1. Reads the backup table created by age_prepare_pg_upgrade() +-- 2. Looks up new namespace OIDs by schema name +-- 3. Updates ag_label.graph references +-- 4. Updates ag_graph.graphid and ag_graph.namespace +-- 5. Restores namespace column to regnamespace type +-- 6. Cleans up the backup table and view +-- 7. Invalidates AGE caches to ensure cypher queries work immediately +-- +-- Returns: void +-- Side effects: Updates OIDs in ag_graph and ag_label tables, restores schema +-- +CREATE FUNCTION ag_catalog.age_finish_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + mapping_count integer; + updated_labels integer; + updated_graphs integer; +BEGIN + -- Check if backup table exists + IF NOT EXISTS ( + SELECT 1 FROM information_schema.tables + WHERE table_schema = 'public' + AND table_name = '_age_pg_upgrade_backup' + ) THEN + RAISE EXCEPTION 'Backup table public._age_pg_upgrade_backup not found. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Check if namespace column is oid type (was properly prepared) + IF NOT EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE EXCEPTION 'ag_graph.namespace is not oid type. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Create temporary mapping table with old and new OIDs + CREATE TEMP TABLE _graphid_mapping AS + SELECT + b.old_graphid, + b.graph_name, + n.oid AS new_graphid + FROM public._age_pg_upgrade_backup b + JOIN pg_namespace n ON n.nspname = b.namespace_name; + + GET DIAGNOSTICS mapping_count = ROW_COUNT; + + IF mapping_count = 0 THEN + RAISE EXCEPTION 'No OID mappings found. Schema names may have changed.'; + END IF; + + RAISE NOTICE 'Found % graph(s) to remap', mapping_count; + + -- Temporarily drop foreign key constraint + ALTER TABLE ag_catalog.ag_label DROP CONSTRAINT IF EXISTS fk_graph_oid; + + -- Update ag_label.graph references to use new OIDs + UPDATE ag_catalog.ag_label l + SET graph = m.new_graphid + FROM _graphid_mapping m + WHERE l.graph = m.old_graphid; + + GET DIAGNOSTICS updated_labels = ROW_COUNT; + RAISE NOTICE 'Updated % label record(s)', updated_labels; + + -- Update ag_graph.graphid and ag_graph.namespace to new OIDs + UPDATE ag_catalog.ag_graph g + SET graphid = m.new_graphid, + namespace = m.new_graphid + FROM _graphid_mapping m + WHERE g.graphid = m.old_graphid; + + RAISE NOTICE 'Updated % graph record(s)', updated_graphs; + + -- Restore foreign key constraint + ALTER TABLE ag_catalog.ag_label + ADD CONSTRAINT fk_graph_oid + FOREIGN KEY(graph) REFERENCES ag_catalog.ag_graph(graphid); + + -- Clean up temporary mapping table + DROP TABLE _graphid_mapping; + DROP TABLE public._age_pg_upgrade_backup; + Review Comment: age_finish_pg_upgrade() drops public._age_pg_upgrade_backup before the schema restoration + cache invalidation steps complete. If a later step fails (e.g., ALTER COLUMN back to regnamespace), the backup is lost and rerunning/repairing becomes harder. Consider deferring deletion of the backup table until the very end after all steps succeed. ########## age--1.7.0--y.y.y.sql: ########## @@ -30,3 +30,341 @@ --* Please add all additions, deletions, and modifications to the end of this --* file. We need to keep the order of these changes. --* REMOVE ALL LINES ABOVE, and this one, that start with --* + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. +-- + +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + + -- Check if namespace column is already oid type (already prepared) + IF EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'Database already prepared for pg_upgrade (namespace is oid type).'; + RETURN; + END IF; + + -- Drop existing backup table if it exists (from a previous failed attempt) + DROP TABLE IF EXISTS public._age_pg_upgrade_backup; + + -- Create backup table with graph names mapped to namespace names + -- We store names (not OIDs) because names survive pg_upgrade while OIDs don't + CREATE TABLE public._age_pg_upgrade_backup AS + SELECT + g.graphid AS old_graphid, + g.name AS graph_name, + g.namespace::regnamespace::text AS namespace_name + FROM ag_catalog.ag_graph g; + + RAISE NOTICE 'Created backup table public._age_pg_upgrade_backup with % graph(s)', + (SELECT count(*) FROM public._age_pg_upgrade_backup); + + -- Drop the existing regnamespace-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column from regnamespace to oid + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE oid USING namespace::oid; + + -- Recreate the index with oid type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + -- Create a view for backward-compatible display of namespace as schema name + CREATE OR REPLACE VIEW ag_catalog.ag_graph_view AS + SELECT graphid, name, namespace::regnamespace AS namespace + FROM ag_catalog.ag_graph; + + RAISE NOTICE 'Successfully prepared database for pg_upgrade.'; + RAISE NOTICE 'The ag_graph.namespace column has been converted from regnamespace to oid.'; + RAISE NOTICE 'You can now run pg_upgrade.'; + RAISE NOTICE 'After pg_upgrade completes, run: SELECT age_finish_pg_upgrade();'; +END; +$function$; + +COMMENT ON FUNCTION ag_catalog.age_prepare_pg_upgrade() IS +'Prepares an AGE database for pg_upgrade by converting ag_graph.namespace from regnamespace to oid type. Run this before pg_upgrade.'; + +CREATE FUNCTION ag_catalog.age_finish_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + mapping_count integer; + updated_labels integer; + updated_graphs integer; +BEGIN + -- Check if backup table exists + IF NOT EXISTS ( + SELECT 1 FROM information_schema.tables + WHERE table_schema = 'public' + AND table_name = '_age_pg_upgrade_backup' + ) THEN + RAISE EXCEPTION 'Backup table public._age_pg_upgrade_backup not found. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Check if namespace column is oid type (was properly prepared) + IF NOT EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE EXCEPTION 'ag_graph.namespace is not oid type. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Create temporary mapping table with old and new OIDs + CREATE TEMP TABLE _graphid_mapping AS + SELECT + b.old_graphid, + b.graph_name, + n.oid AS new_graphid + FROM public._age_pg_upgrade_backup b + JOIN pg_namespace n ON n.nspname = b.namespace_name; + + GET DIAGNOSTICS mapping_count = ROW_COUNT; + + IF mapping_count = 0 THEN + RAISE EXCEPTION 'No OID mappings found. Schema names may have changed.'; + END IF; + + RAISE NOTICE 'Found % graph(s) to remap', mapping_count; + + -- Temporarily drop foreign key constraint + ALTER TABLE ag_catalog.ag_label DROP CONSTRAINT IF EXISTS fk_graph_oid; + + -- Update ag_label.graph references to use new OIDs + UPDATE ag_catalog.ag_label l + SET graph = m.new_graphid + FROM _graphid_mapping m + WHERE l.graph = m.old_graphid; + + GET DIAGNOSTICS updated_labels = ROW_COUNT; + RAISE NOTICE 'Updated % label record(s)', updated_labels; + + -- Update ag_graph.graphid and ag_graph.namespace to new OIDs + UPDATE ag_catalog.ag_graph g + SET graphid = m.new_graphid, + namespace = m.new_graphid + FROM _graphid_mapping m + WHERE g.graphid = m.old_graphid; + Review Comment: updated_graphs is never set (missing GET DIAGNOSTICS ... = ROW_COUNT after updating ag_graph), so the NOTICE will report NULL and you lose an important verification point. Capture ROW_COUNT after the UPDATE and consider asserting it matches expected mappings. ```suggestion GET DIAGNOSTICS updated_graphs = ROW_COUNT; ``` ########## sql/age_pg_upgrade.sql: ########## @@ -0,0 +1,435 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. The ag_graph.namespace column uses +-- the regnamespace type which is not supported by pg_upgrade. These functions +-- temporarily convert the schema to be pg_upgrade compatible, then restore it +-- to the original state after the upgrade completes. +-- +-- Usage: +-- 1. Before pg_upgrade: SELECT age_prepare_pg_upgrade(); +-- 2. Run pg_upgrade as normal +-- 3. After pg_upgrade: SELECT age_finish_pg_upgrade(); +-- +-- To cancel an upgrade after preparation (before running pg_upgrade): +-- SELECT age_revert_pg_upgrade_changes(); +-- + +-- +-- age_prepare_pg_upgrade() +-- +-- Prepares an AGE database for pg_upgrade by converting the ag_graph.namespace +-- column from regnamespace to oid type. This is necessary because pg_upgrade +-- does not support the regnamespace type in user tables. +-- +-- This function: +-- 1. Creates a backup table with graph name to namespace name mappings +-- 2. Drops the existing namespace index +-- 3. Converts the namespace column from regnamespace to oid +-- 4. Recreates the namespace index with oid type +-- 5. Creates a view for backward-compatible namespace display +-- +-- Returns: void +-- Side effects: Modifies ag_graph table structure +-- +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + Review Comment: age_prepare_pg_upgrade() returns early when there are no rows in ag_catalog.ag_graph, but the stated goal is to make the ag_graph.namespace column pg_upgrade-compatible; pg_upgrade will still see the regnamespace-typed column even if the table is empty. Consider performing the type conversion/index changes regardless of graph count (backup table can be empty). ```suggestion BEGIN ``` ########## sql/age_pg_upgrade.sql: ########## @@ -0,0 +1,435 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. The ag_graph.namespace column uses +-- the regnamespace type which is not supported by pg_upgrade. These functions +-- temporarily convert the schema to be pg_upgrade compatible, then restore it +-- to the original state after the upgrade completes. +-- +-- Usage: +-- 1. Before pg_upgrade: SELECT age_prepare_pg_upgrade(); +-- 2. Run pg_upgrade as normal +-- 3. After pg_upgrade: SELECT age_finish_pg_upgrade(); +-- +-- To cancel an upgrade after preparation (before running pg_upgrade): +-- SELECT age_revert_pg_upgrade_changes(); +-- + +-- +-- age_prepare_pg_upgrade() +-- +-- Prepares an AGE database for pg_upgrade by converting the ag_graph.namespace +-- column from regnamespace to oid type. This is necessary because pg_upgrade +-- does not support the regnamespace type in user tables. +-- +-- This function: +-- 1. Creates a backup table with graph name to namespace name mappings +-- 2. Drops the existing namespace index +-- 3. Converts the namespace column from regnamespace to oid +-- 4. Recreates the namespace index with oid type +-- 5. Creates a view for backward-compatible namespace display +-- +-- Returns: void +-- Side effects: Modifies ag_graph table structure +-- +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + + -- Check if namespace column is already oid type (already prepared) + IF EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'Database already prepared for pg_upgrade (namespace is oid type).'; + RETURN; + END IF; + + -- Drop existing backup table if it exists (from a previous failed attempt) + DROP TABLE IF EXISTS public._age_pg_upgrade_backup; + + -- Create backup table with graph names mapped to namespace names + -- We store names (not OIDs) because names survive pg_upgrade while OIDs don't + CREATE TABLE public._age_pg_upgrade_backup AS + SELECT + g.graphid AS old_graphid, + g.name AS graph_name, + g.namespace::regnamespace::text AS namespace_name + FROM ag_catalog.ag_graph g; + + RAISE NOTICE 'Created backup table public._age_pg_upgrade_backup with % graph(s)', + (SELECT count(*) FROM public._age_pg_upgrade_backup); + + -- Drop the existing regnamespace-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column from regnamespace to oid + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE oid USING namespace::oid; + + -- Recreate the index with oid type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + -- Create a view for backward-compatible display of namespace as schema name + CREATE OR REPLACE VIEW ag_catalog.ag_graph_view AS + SELECT graphid, name, namespace::regnamespace AS namespace + FROM ag_catalog.ag_graph; + + RAISE NOTICE 'Successfully prepared database for pg_upgrade.'; + RAISE NOTICE 'The ag_graph.namespace column has been converted from regnamespace to oid.'; + RAISE NOTICE 'You can now run pg_upgrade.'; + RAISE NOTICE 'After pg_upgrade completes, run: SELECT age_finish_pg_upgrade();'; +END; +$function$; + +COMMENT ON FUNCTION ag_catalog.age_prepare_pg_upgrade() IS +'Prepares an AGE database for pg_upgrade by converting ag_graph.namespace from regnamespace to oid type. Run this before pg_upgrade.'; + +-- +-- age_finish_pg_upgrade() +-- +-- Completes the pg_upgrade process by remapping stale OIDs in ag_graph and +-- ag_label tables to their new values, then restores the original schema. +-- After pg_upgrade, the namespace OIDs stored in these tables no longer match +-- the actual pg_namespace OIDs because PostgreSQL assigns new OIDs to schemas +-- during the upgrade. +-- +-- This function: +-- 1. Reads the backup table created by age_prepare_pg_upgrade() +-- 2. Looks up new namespace OIDs by schema name +-- 3. Updates ag_label.graph references +-- 4. Updates ag_graph.graphid and ag_graph.namespace +-- 5. Restores namespace column to regnamespace type +-- 6. Cleans up the backup table and view +-- 7. Invalidates AGE caches to ensure cypher queries work immediately +-- +-- Returns: void +-- Side effects: Updates OIDs in ag_graph and ag_label tables, restores schema +-- +CREATE FUNCTION ag_catalog.age_finish_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + mapping_count integer; + updated_labels integer; + updated_graphs integer; +BEGIN + -- Check if backup table exists + IF NOT EXISTS ( + SELECT 1 FROM information_schema.tables + WHERE table_schema = 'public' + AND table_name = '_age_pg_upgrade_backup' + ) THEN + RAISE EXCEPTION 'Backup table public._age_pg_upgrade_backup not found. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Check if namespace column is oid type (was properly prepared) + IF NOT EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE EXCEPTION 'ag_graph.namespace is not oid type. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Create temporary mapping table with old and new OIDs + CREATE TEMP TABLE _graphid_mapping AS + SELECT + b.old_graphid, + b.graph_name, + n.oid AS new_graphid + FROM public._age_pg_upgrade_backup b + JOIN pg_namespace n ON n.nspname = b.namespace_name; + + GET DIAGNOSTICS mapping_count = ROW_COUNT; + + IF mapping_count = 0 THEN + RAISE EXCEPTION 'No OID mappings found. Schema names may have changed.'; + END IF; + + RAISE NOTICE 'Found % graph(s) to remap', mapping_count; + + -- Temporarily drop foreign key constraint + ALTER TABLE ag_catalog.ag_label DROP CONSTRAINT IF EXISTS fk_graph_oid; + + -- Update ag_label.graph references to use new OIDs + UPDATE ag_catalog.ag_label l + SET graph = m.new_graphid + FROM _graphid_mapping m + WHERE l.graph = m.old_graphid; + + GET DIAGNOSTICS updated_labels = ROW_COUNT; + RAISE NOTICE 'Updated % label record(s)', updated_labels; + + -- Update ag_graph.graphid and ag_graph.namespace to new OIDs + UPDATE ag_catalog.ag_graph g + SET graphid = m.new_graphid, + namespace = m.new_graphid + FROM _graphid_mapping m + WHERE g.graphid = m.old_graphid; + + RAISE NOTICE 'Updated % graph record(s)', updated_graphs; + + -- Restore foreign key constraint + ALTER TABLE ag_catalog.ag_label + ADD CONSTRAINT fk_graph_oid + FOREIGN KEY(graph) REFERENCES ag_catalog.ag_graph(graphid); + + -- Clean up temporary mapping table + DROP TABLE _graphid_mapping; + DROP TABLE public._age_pg_upgrade_backup; + + RAISE NOTICE 'Successfully completed pg_upgrade OID remapping.'; + + -- + -- Restore original schema (revert namespace to regnamespace) + -- + RAISE NOTICE 'Restoring original schema...'; + + -- Drop the view (no longer needed with regnamespace) + DROP VIEW IF EXISTS ag_catalog.ag_graph_view; + + -- Drop the existing oid-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column back to regnamespace + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE regnamespace USING namespace::regnamespace; + + -- Recreate the index with regnamespace type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + RAISE NOTICE 'Successfully restored ag_graph.namespace to regnamespace type.'; + + -- + -- Invalidate AGE's internal caches by touching each graph's namespace + -- AGE registers a syscache callback on NAMESPACEOID, so altering a schema + -- triggers cache invalidation. This ensures cypher queries work immediately + -- without requiring a session reconnect. + -- + RAISE NOTICE 'Invalidating AGE caches...'; + PERFORM pg_catalog.pg_advisory_lock(hashtext('age_finish_pg_upgrade')); + BEGIN + -- Touch each graph's namespace to invalidate caches + DECLARE + graph_rec RECORD; + BEGIN + FOR graph_rec IN SELECT namespace::text AS ns_name FROM ag_catalog.ag_graph + LOOP + EXECUTE format('ALTER SCHEMA %I OWNER TO CURRENT_USER', graph_rec.ns_name); + END LOOP; + END; + END; + PERFORM pg_catalog.pg_advisory_unlock(hashtext('age_finish_pg_upgrade')); + + RAISE NOTICE ''; + RAISE NOTICE 'pg_upgrade complete. All graph data has been preserved.'; +END; +$function$; + +COMMENT ON FUNCTION ag_catalog.age_finish_pg_upgrade() IS +'Completes pg_upgrade by remapping stale OIDs and restoring the original schema. Run this after pg_upgrade.'; + +-- +-- age_revert_pg_upgrade_changes() +-- +-- Reverts the schema changes made by age_prepare_pg_upgrade() if you need to +-- cancel the upgrade process before running pg_upgrade. This restores the +-- namespace column to its original regnamespace type. +-- +-- NOTE: This function is NOT needed after age_finish_pg_upgrade(), which +-- automatically restores the original schema. Use this only if you called +-- age_prepare_pg_upgrade() but decided not to proceed with pg_upgrade. +-- +-- This function: +-- 1. Drops the ag_graph_view (no longer needed) +-- 2. Drops the oid-based namespace index +-- 3. Converts namespace column back to regnamespace +-- 4. Recreates the namespace index with regnamespace type +-- 5. Invalidates AGE caches to ensure cypher queries work immediately +-- 6. Does NOT clean up the backup table (manual cleanup may be needed) +-- +-- Returns: void +-- Side effects: Modifies ag_graph table structure +-- +CREATE FUNCTION ag_catalog.age_revert_pg_upgrade_changes() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +BEGIN + -- Check if namespace column is oid type (needs reverting) + IF NOT EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'ag_graph.namespace is already regnamespace type. Nothing to revert.'; + RETURN; + END IF; + + -- Drop the view (no longer needed with regnamespace) + DROP VIEW IF EXISTS ag_catalog.ag_graph_view; + + -- Drop the existing oid-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column back to regnamespace + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE regnamespace USING namespace::regnamespace; + + -- Recreate the index with regnamespace type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + -- + -- Invalidate AGE's internal caches by touching each graph's namespace + -- + PERFORM pg_catalog.pg_advisory_lock(hashtext('age_revert_pg_upgrade')); + BEGIN + DECLARE + graph_rec RECORD; + BEGIN + FOR graph_rec IN SELECT namespace::text AS ns_name FROM ag_catalog.ag_graph + LOOP + EXECUTE format('ALTER SCHEMA %I OWNER TO CURRENT_USER', graph_rec.ns_name); + END LOOP; + END; + END; + PERFORM pg_catalog.pg_advisory_unlock(hashtext('age_revert_pg_upgrade')); Review Comment: age_revert_pg_upgrade_changes() uses pg_advisory_lock()/unlock without exception safety (session-level lock can remain held on error) and uses ALTER SCHEMA ... OWNER TO CURRENT_USER which can change schema ownership and may fail. Prefer xact-level advisory locks and an invalidation approach that doesn't leave ownership changed (and avoid double-quoting namespace identifiers). ```suggestion PERFORM pg_catalog.pg_advisory_xact_lock(hashtext('age_revert_pg_upgrade')); BEGIN DECLARE graph_rec RECORD; BEGIN FOR graph_rec IN SELECT namespace::text AS ns_name FROM ag_catalog.ag_graph LOOP -- Touch the corresponding pg_namespace row to induce cache activity PERFORM 1 FROM pg_catalog.pg_namespace n WHERE n.nspname = graph_rec.ns_name FOR SHARE; END LOOP; END; END; ``` ########## sql/age_pg_upgrade.sql: ########## @@ -0,0 +1,435 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. The ag_graph.namespace column uses +-- the regnamespace type which is not supported by pg_upgrade. These functions +-- temporarily convert the schema to be pg_upgrade compatible, then restore it +-- to the original state after the upgrade completes. +-- +-- Usage: +-- 1. Before pg_upgrade: SELECT age_prepare_pg_upgrade(); +-- 2. Run pg_upgrade as normal +-- 3. After pg_upgrade: SELECT age_finish_pg_upgrade(); +-- +-- To cancel an upgrade after preparation (before running pg_upgrade): +-- SELECT age_revert_pg_upgrade_changes(); +-- + +-- +-- age_prepare_pg_upgrade() +-- +-- Prepares an AGE database for pg_upgrade by converting the ag_graph.namespace +-- column from regnamespace to oid type. This is necessary because pg_upgrade +-- does not support the regnamespace type in user tables. +-- +-- This function: +-- 1. Creates a backup table with graph name to namespace name mappings +-- 2. Drops the existing namespace index +-- 3. Converts the namespace column from regnamespace to oid +-- 4. Recreates the namespace index with oid type +-- 5. Creates a view for backward-compatible namespace display +-- +-- Returns: void +-- Side effects: Modifies ag_graph table structure +-- +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + + -- Check if namespace column is already oid type (already prepared) + IF EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'Database already prepared for pg_upgrade (namespace is oid type).'; + RETURN; + END IF; + + -- Drop existing backup table if it exists (from a previous failed attempt) + DROP TABLE IF EXISTS public._age_pg_upgrade_backup; + + -- Create backup table with graph names mapped to namespace names + -- We store names (not OIDs) because names survive pg_upgrade while OIDs don't + CREATE TABLE public._age_pg_upgrade_backup AS + SELECT + g.graphid AS old_graphid, + g.name AS graph_name, + g.namespace::regnamespace::text AS namespace_name + FROM ag_catalog.ag_graph g; + + RAISE NOTICE 'Created backup table public._age_pg_upgrade_backup with % graph(s)', + (SELECT count(*) FROM public._age_pg_upgrade_backup); + + -- Drop the existing regnamespace-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column from regnamespace to oid + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE oid USING namespace::oid; + + -- Recreate the index with oid type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + -- Create a view for backward-compatible display of namespace as schema name + CREATE OR REPLACE VIEW ag_catalog.ag_graph_view AS + SELECT graphid, name, namespace::regnamespace AS namespace + FROM ag_catalog.ag_graph; + + RAISE NOTICE 'Successfully prepared database for pg_upgrade.'; + RAISE NOTICE 'The ag_graph.namespace column has been converted from regnamespace to oid.'; + RAISE NOTICE 'You can now run pg_upgrade.'; + RAISE NOTICE 'After pg_upgrade completes, run: SELECT age_finish_pg_upgrade();'; +END; +$function$; + +COMMENT ON FUNCTION ag_catalog.age_prepare_pg_upgrade() IS +'Prepares an AGE database for pg_upgrade by converting ag_graph.namespace from regnamespace to oid type. Run this before pg_upgrade.'; + +-- +-- age_finish_pg_upgrade() +-- +-- Completes the pg_upgrade process by remapping stale OIDs in ag_graph and +-- ag_label tables to their new values, then restores the original schema. +-- After pg_upgrade, the namespace OIDs stored in these tables no longer match +-- the actual pg_namespace OIDs because PostgreSQL assigns new OIDs to schemas +-- during the upgrade. +-- +-- This function: +-- 1. Reads the backup table created by age_prepare_pg_upgrade() +-- 2. Looks up new namespace OIDs by schema name +-- 3. Updates ag_label.graph references +-- 4. Updates ag_graph.graphid and ag_graph.namespace +-- 5. Restores namespace column to regnamespace type +-- 6. Cleans up the backup table and view +-- 7. Invalidates AGE caches to ensure cypher queries work immediately +-- +-- Returns: void +-- Side effects: Updates OIDs in ag_graph and ag_label tables, restores schema +-- +CREATE FUNCTION ag_catalog.age_finish_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + mapping_count integer; + updated_labels integer; + updated_graphs integer; +BEGIN + -- Check if backup table exists + IF NOT EXISTS ( + SELECT 1 FROM information_schema.tables + WHERE table_schema = 'public' + AND table_name = '_age_pg_upgrade_backup' + ) THEN + RAISE EXCEPTION 'Backup table public._age_pg_upgrade_backup not found. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Check if namespace column is oid type (was properly prepared) + IF NOT EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE EXCEPTION 'ag_graph.namespace is not oid type. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Create temporary mapping table with old and new OIDs + CREATE TEMP TABLE _graphid_mapping AS + SELECT + b.old_graphid, + b.graph_name, + n.oid AS new_graphid + FROM public._age_pg_upgrade_backup b + JOIN pg_namespace n ON n.nspname = b.namespace_name; + + GET DIAGNOSTICS mapping_count = ROW_COUNT; + + IF mapping_count = 0 THEN + RAISE EXCEPTION 'No OID mappings found. Schema names may have changed.'; + END IF; + + RAISE NOTICE 'Found % graph(s) to remap', mapping_count; + + -- Temporarily drop foreign key constraint + ALTER TABLE ag_catalog.ag_label DROP CONSTRAINT IF EXISTS fk_graph_oid; + + -- Update ag_label.graph references to use new OIDs + UPDATE ag_catalog.ag_label l + SET graph = m.new_graphid + FROM _graphid_mapping m + WHERE l.graph = m.old_graphid; + + GET DIAGNOSTICS updated_labels = ROW_COUNT; + RAISE NOTICE 'Updated % label record(s)', updated_labels; + + -- Update ag_graph.graphid and ag_graph.namespace to new OIDs + UPDATE ag_catalog.ag_graph g + SET graphid = m.new_graphid, + namespace = m.new_graphid + FROM _graphid_mapping m + WHERE g.graphid = m.old_graphid; + + RAISE NOTICE 'Updated % graph record(s)', updated_graphs; + + -- Restore foreign key constraint + ALTER TABLE ag_catalog.ag_label + ADD CONSTRAINT fk_graph_oid + FOREIGN KEY(graph) REFERENCES ag_catalog.ag_graph(graphid); + + -- Clean up temporary mapping table + DROP TABLE _graphid_mapping; + DROP TABLE public._age_pg_upgrade_backup; + + RAISE NOTICE 'Successfully completed pg_upgrade OID remapping.'; + + -- + -- Restore original schema (revert namespace to regnamespace) + -- + RAISE NOTICE 'Restoring original schema...'; + + -- Drop the view (no longer needed with regnamespace) + DROP VIEW IF EXISTS ag_catalog.ag_graph_view; + + -- Drop the existing oid-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column back to regnamespace + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE regnamespace USING namespace::regnamespace; + + -- Recreate the index with regnamespace type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + RAISE NOTICE 'Successfully restored ag_graph.namespace to regnamespace type.'; + + -- + -- Invalidate AGE's internal caches by touching each graph's namespace + -- AGE registers a syscache callback on NAMESPACEOID, so altering a schema + -- triggers cache invalidation. This ensures cypher queries work immediately + -- without requiring a session reconnect. + -- + RAISE NOTICE 'Invalidating AGE caches...'; + PERFORM pg_catalog.pg_advisory_lock(hashtext('age_finish_pg_upgrade')); + BEGIN + -- Touch each graph's namespace to invalidate caches + DECLARE + graph_rec RECORD; + BEGIN + FOR graph_rec IN SELECT namespace::text AS ns_name FROM ag_catalog.ag_graph + LOOP + EXECUTE format('ALTER SCHEMA %I OWNER TO CURRENT_USER', graph_rec.ns_name); Review Comment: Using ALTER SCHEMA ... OWNER TO CURRENT_USER as a "touch" is not side-effect free: it can permanently change schema ownership and may fail if CURRENT_USER isn't allowed to change the owner. Also, namespace::text for regnamespace is already quoted, so using format('%I', ns_name) can over-quote and break for schemas needing quotes. Consider fetching pg_namespace.nspname and current owner, then toggling owner and restoring it (or another approach that preserves ownership), and ensure identifiers are quoted exactly once. ```suggestion FOR graph_rec IN SELECT n.nspname AS ns_name, r.rolname AS owner_name FROM ag_catalog.ag_graph g JOIN pg_catalog.pg_namespace n ON n.oid = g.namespace JOIN pg_catalog.pg_roles r ON r.oid = n.nspowner LOOP -- Temporarily change owner to CURRENT_USER to trigger cache invalidation, -- then restore the original owner to preserve permissions. EXECUTE format( 'ALTER SCHEMA %I OWNER TO %I', graph_rec.ns_name, current_user ); EXECUTE format( 'ALTER SCHEMA %I OWNER TO %I', graph_rec.ns_name, graph_rec.owner_name ); ``` ########## sql/age_pg_upgrade.sql: ########## @@ -0,0 +1,435 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +-- +-- pg_upgrade support functions +-- +-- These functions help users upgrade PostgreSQL major versions using pg_upgrade +-- while preserving Apache AGE graph data. The ag_graph.namespace column uses +-- the regnamespace type which is not supported by pg_upgrade. These functions +-- temporarily convert the schema to be pg_upgrade compatible, then restore it +-- to the original state after the upgrade completes. +-- +-- Usage: +-- 1. Before pg_upgrade: SELECT age_prepare_pg_upgrade(); +-- 2. Run pg_upgrade as normal +-- 3. After pg_upgrade: SELECT age_finish_pg_upgrade(); +-- +-- To cancel an upgrade after preparation (before running pg_upgrade): +-- SELECT age_revert_pg_upgrade_changes(); +-- + +-- +-- age_prepare_pg_upgrade() +-- +-- Prepares an AGE database for pg_upgrade by converting the ag_graph.namespace +-- column from regnamespace to oid type. This is necessary because pg_upgrade +-- does not support the regnamespace type in user tables. +-- +-- This function: +-- 1. Creates a backup table with graph name to namespace name mappings +-- 2. Drops the existing namespace index +-- 3. Converts the namespace column from regnamespace to oid +-- 4. Recreates the namespace index with oid type +-- 5. Creates a view for backward-compatible namespace display +-- +-- Returns: void +-- Side effects: Modifies ag_graph table structure +-- +CREATE FUNCTION ag_catalog.age_prepare_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + has_graphs boolean; +BEGIN + -- Check if there are any graphs to process + SELECT EXISTS(SELECT 1 FROM ag_catalog.ag_graph) INTO has_graphs; + + IF NOT has_graphs THEN + RAISE NOTICE 'No graphs found. Nothing to prepare for pg_upgrade.'; + RETURN; + END IF; + + -- Check if namespace column is already oid type (already prepared) + IF EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE NOTICE 'Database already prepared for pg_upgrade (namespace is oid type).'; + RETURN; + END IF; + + -- Drop existing backup table if it exists (from a previous failed attempt) + DROP TABLE IF EXISTS public._age_pg_upgrade_backup; + + -- Create backup table with graph names mapped to namespace names + -- We store names (not OIDs) because names survive pg_upgrade while OIDs don't + CREATE TABLE public._age_pg_upgrade_backup AS + SELECT + g.graphid AS old_graphid, + g.name AS graph_name, + g.namespace::regnamespace::text AS namespace_name + FROM ag_catalog.ag_graph g; + + RAISE NOTICE 'Created backup table public._age_pg_upgrade_backup with % graph(s)', + (SELECT count(*) FROM public._age_pg_upgrade_backup); + + -- Drop the existing regnamespace-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column from regnamespace to oid + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE oid USING namespace::oid; + + -- Recreate the index with oid type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + -- Create a view for backward-compatible display of namespace as schema name + CREATE OR REPLACE VIEW ag_catalog.ag_graph_view AS + SELECT graphid, name, namespace::regnamespace AS namespace + FROM ag_catalog.ag_graph; + + RAISE NOTICE 'Successfully prepared database for pg_upgrade.'; + RAISE NOTICE 'The ag_graph.namespace column has been converted from regnamespace to oid.'; + RAISE NOTICE 'You can now run pg_upgrade.'; + RAISE NOTICE 'After pg_upgrade completes, run: SELECT age_finish_pg_upgrade();'; +END; +$function$; + +COMMENT ON FUNCTION ag_catalog.age_prepare_pg_upgrade() IS +'Prepares an AGE database for pg_upgrade by converting ag_graph.namespace from regnamespace to oid type. Run this before pg_upgrade.'; + +-- +-- age_finish_pg_upgrade() +-- +-- Completes the pg_upgrade process by remapping stale OIDs in ag_graph and +-- ag_label tables to their new values, then restores the original schema. +-- After pg_upgrade, the namespace OIDs stored in these tables no longer match +-- the actual pg_namespace OIDs because PostgreSQL assigns new OIDs to schemas +-- during the upgrade. +-- +-- This function: +-- 1. Reads the backup table created by age_prepare_pg_upgrade() +-- 2. Looks up new namespace OIDs by schema name +-- 3. Updates ag_label.graph references +-- 4. Updates ag_graph.graphid and ag_graph.namespace +-- 5. Restores namespace column to regnamespace type +-- 6. Cleans up the backup table and view +-- 7. Invalidates AGE caches to ensure cypher queries work immediately +-- +-- Returns: void +-- Side effects: Updates OIDs in ag_graph and ag_label tables, restores schema +-- +CREATE FUNCTION ag_catalog.age_finish_pg_upgrade() + RETURNS void + LANGUAGE plpgsql + SET search_path = ag_catalog, pg_catalog + AS $function$ +DECLARE + mapping_count integer; + updated_labels integer; + updated_graphs integer; +BEGIN + -- Check if backup table exists + IF NOT EXISTS ( + SELECT 1 FROM information_schema.tables + WHERE table_schema = 'public' + AND table_name = '_age_pg_upgrade_backup' + ) THEN + RAISE EXCEPTION 'Backup table public._age_pg_upgrade_backup not found. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Check if namespace column is oid type (was properly prepared) + IF NOT EXISTS ( + SELECT 1 FROM information_schema.columns + WHERE table_schema = 'ag_catalog' + AND table_name = 'ag_graph' + AND column_name = 'namespace' + AND data_type = 'oid' + ) THEN + RAISE EXCEPTION 'ag_graph.namespace is not oid type. ' + 'Did you run age_prepare_pg_upgrade() before pg_upgrade?'; + END IF; + + -- Create temporary mapping table with old and new OIDs + CREATE TEMP TABLE _graphid_mapping AS + SELECT + b.old_graphid, + b.graph_name, + n.oid AS new_graphid + FROM public._age_pg_upgrade_backup b + JOIN pg_namespace n ON n.nspname = b.namespace_name; + + GET DIAGNOSTICS mapping_count = ROW_COUNT; + + IF mapping_count = 0 THEN + RAISE EXCEPTION 'No OID mappings found. Schema names may have changed.'; + END IF; + + RAISE NOTICE 'Found % graph(s) to remap', mapping_count; + + -- Temporarily drop foreign key constraint + ALTER TABLE ag_catalog.ag_label DROP CONSTRAINT IF EXISTS fk_graph_oid; + + -- Update ag_label.graph references to use new OIDs + UPDATE ag_catalog.ag_label l + SET graph = m.new_graphid + FROM _graphid_mapping m + WHERE l.graph = m.old_graphid; + + GET DIAGNOSTICS updated_labels = ROW_COUNT; + RAISE NOTICE 'Updated % label record(s)', updated_labels; + + -- Update ag_graph.graphid and ag_graph.namespace to new OIDs + UPDATE ag_catalog.ag_graph g + SET graphid = m.new_graphid, + namespace = m.new_graphid + FROM _graphid_mapping m + WHERE g.graphid = m.old_graphid; + + RAISE NOTICE 'Updated % graph record(s)', updated_graphs; + + -- Restore foreign key constraint + ALTER TABLE ag_catalog.ag_label + ADD CONSTRAINT fk_graph_oid + FOREIGN KEY(graph) REFERENCES ag_catalog.ag_graph(graphid); + + -- Clean up temporary mapping table + DROP TABLE _graphid_mapping; + DROP TABLE public._age_pg_upgrade_backup; + + RAISE NOTICE 'Successfully completed pg_upgrade OID remapping.'; + + -- + -- Restore original schema (revert namespace to regnamespace) + -- + RAISE NOTICE 'Restoring original schema...'; + + -- Drop the view (no longer needed with regnamespace) + DROP VIEW IF EXISTS ag_catalog.ag_graph_view; + + -- Drop the existing oid-based index + DROP INDEX IF EXISTS ag_catalog.ag_graph_namespace_index; + + -- Convert namespace column back to regnamespace + ALTER TABLE ag_catalog.ag_graph + ALTER COLUMN namespace TYPE regnamespace USING namespace::regnamespace; + + -- Recreate the index with regnamespace type + CREATE UNIQUE INDEX ag_graph_namespace_index + ON ag_catalog.ag_graph USING btree (namespace); + + RAISE NOTICE 'Successfully restored ag_graph.namespace to regnamespace type.'; + + -- + -- Invalidate AGE's internal caches by touching each graph's namespace + -- AGE registers a syscache callback on NAMESPACEOID, so altering a schema + -- triggers cache invalidation. This ensures cypher queries work immediately + -- without requiring a session reconnect. + -- + RAISE NOTICE 'Invalidating AGE caches...'; + PERFORM pg_catalog.pg_advisory_lock(hashtext('age_finish_pg_upgrade')); + BEGIN + -- Touch each graph's namespace to invalidate caches + DECLARE + graph_rec RECORD; + BEGIN + FOR graph_rec IN SELECT namespace::text AS ns_name FROM ag_catalog.ag_graph + LOOP + EXECUTE format('ALTER SCHEMA %I OWNER TO CURRENT_USER', graph_rec.ns_name); + END LOOP; + END; + END; + PERFORM pg_catalog.pg_advisory_unlock(hashtext('age_finish_pg_upgrade')); + Review Comment: The cache invalidation uses pg_advisory_lock()/pg_advisory_unlock() (session-level locks). If an error occurs inside the BEGIN block, the unlock won't execute and the session can retain the lock, blocking future runs. Prefer pg_advisory_xact_lock() (auto-released at transaction end) or add an EXCEPTION handler that always unlocks. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
