This is an automated email from the ASF dual-hosted git repository.
muhammadshoaib pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/age.git
The following commit(s) were added to refs/heads/master by this push:
new a1b749af Fix upgrade test: allow function removal (#2422)
a1b749af is described below
commit a1b749af383dfdf0958ba6c1531b99aeab9207f5
Author: John Gemignani <[email protected]>
AuthorDate: Tue May 5 14:02:19 2026 -0700
Fix upgrade test: allow function removal (#2422)
Fix upgrade test: allow function removal and detect more deficiencies.
The age_upgrade regression test (added in #2364, refined in #2377, #2397,
install and a synthetic-initial -> current upgrade. Three gaps surfaced
in practice:
1. Function removal forced permanent C stubs.
The synthetic '_initial' install is built from a fixed historical
commit. CREATE EXTENSION resolves every CREATE FUNCTION ... AS
'$libdir/age', '<symbol>' via dlsym at install time when
check_function_bodies is on (the default). If a developer retires a
C entry point in HEAD's age.so, step 10 aborts with "could not find
function ... in file age.so" -- even though the immediately-following
ALTER EXTENSION UPDATE would DROP that SQL declaration. The only way
to keep the test green was to leave a permanent error-raising stub
in age.so, and to remember to add a DROP to the upgrade template.
2. Modifications were under-detected.
The function-property-change query did not compare probin or prosrc,
so a C function whose symbol was renamed in the upgrade template, or
a SQL/plpgsql function whose body changed in either path, slipped
through.
3. Extension membership was not checked.
A template that CREATEs an object but never ALTER EXTENSION ADDs it
leaves a row in pg_proc/pg_class but no pg_depend deptype='e' link.
pg_dump --extension would diverge, but the existing per-catalog diff
queries all returned 0 rows.
Changes (regress/sql/age_upgrade.sql + regress/expected/age_upgrade.out):
* Step 10 wraps the synthetic CREATE EXTENSION in
SET check_function_bodies = off; ... RESET check_function_bodies;
Symbol resolution is deferred to call time. Step 11's ALTER EXTENSION
UPDATE then DROPs any retired functions before any plan can call them.
Step 35's fresh CREATE EXTENSION runs at the GUC default, so HEAD's
sql/ <-> HEAD's age.so consistency is still enforced on the production
install path.
* Steps 2 and 13 add probin and prosrc to the function snapshot.
Step 21 reports probin and prosrc divergences alongside the existing
property-change columns.
* Steps 7b and 18b add an extension-membership snapshot from
pg_depend deptype='e' filtered to the AGE extension OID. Every member
is labeled by stable identity (regprocedure, regtype, regoperator,
opfname+strategy+types, etc.), never by raw OID, so OID drift between
fresh and upgrade installs cannot produce false positives. Steps 33a
and 33b report MISSING / EXTRA members. Step 34 adds extmembers_match
to the summary row.
* Section-header step ranges updated to include the new sub-steps.
The change is fully self-contained: only regress/sql/age_upgrade.sql and
regress/expected/age_upgrade.out are modified. No production C, SQL,
build, or test files are touched. All 34 regression tests pass on a
clean tree.
Mutation-tested with 8 cases against the unmutated tree: baseline pass;
remove-function-with-DROP pass (no stub needed); remove-function-forget-
DROP fail; add-function-with-CREATE pass; add-function-forget-CREATE
fail; volatility-change-no-template fail; volatility-change-with-CREATE-
OR-REPLACE pass; C-symbol-rename-no-template fail. All eight expected
outcomes observed.
All 34 regression tests pass.
Co-authored-by: Claude <[email protected]>
modified: regress/expected/age_upgrade.out
modified: regress/sql/age_upgrade.sql
---
regress/expected/age_upgrade.out | 208 +++++++++++++++++++++++++++++++++++----
regress/sql/age_upgrade.sql | 194 +++++++++++++++++++++++++++++++++---
2 files changed, 367 insertions(+), 35 deletions(-)
diff --git a/regress/expected/age_upgrade.out b/regress/expected/age_upgrade.out
index edf8e602..446e0eb7 100644
--- a/regress/expected/age_upgrade.out
+++ b/regress/expected/age_upgrade.out
@@ -27,16 +27,30 @@
-- upgrade template.
--
-- Compared catalogs:
--- pg_proc — functions, aggregates, procedures (name, args, properties)
+-- pg_proc — functions, aggregates, procedures (name, args, properties
+-- including probin/prosrc to catch C-symbol renames and
+-- SQL-body changes)
-- pg_class — tables, views, sequences, indexes (name, kind)
-- pg_type — types (name, type category)
-- pg_operator — operators (name, left/right types)
-- pg_cast — casts involving AGE types (source, target, context)
-- pg_opclass — operator classes (name, access method)
-- pg_constraint — constraints (name, type, table, referenced table)
+-- pg_depend — extension membership (every AGE-owned object must be
+-- linked back to the extension via deptype='e'; catches
+-- a missing ALTER EXTENSION age ADD ... in the template)
--
-- All comparison queries should return 0 rows.
--
+-- Note on synthetic-initial install (step 10): the synthetic '*_initial'
+-- snapshot is built from a fixed historical commit, so its CREATE FUNCTION
+-- statements may reference C symbols that have since been removed from the
+-- current age.so. Step 10 disables check_function_bodies so that dlsym is
+-- deferred to call time; the immediately-following ALTER EXTENSION UPDATE
+-- (step 11) DROPs any such retired functions before any plan can call them.
+-- This lets developers cleanly remove deprecated C entry points without
+-- needing to keep error-raising stubs in age.so.
+--
LOAD 'age';
SET search_path TO ag_catalog;
-- Step 1: Clean up any graphs left by prior tests (deterministic, no output).
@@ -56,15 +70,23 @@ BEGIN
END
$$;
-- =====================================================================
--- FRESH INSTALL SNAPSHOTS (Steps 2-7)
+-- FRESH INSTALL SNAPSHOTS (Steps 2-7b)
-- Capture the catalog state from the default CREATE EXTENSION install.
-- =====================================================================
-- Step 2: Snapshot functions (includes aggregates via prokind).
+-- probin/prosrc capture the binding to the implementation:
+-- * LANGUAGE c : probin = '$libdir/age', prosrc = C symbol name
+-- (a renamed/retargeted symbol shows up here)
+-- * LANGUAGE sql/plpgsql: probin = NULL, prosrc = function body text
+-- (a body change in the upgrade template shows up
here)
+-- * LANGUAGE internal : probin = NULL, prosrc = builtin name
CREATE TEMP TABLE _fresh_funcs AS
SELECT proname::text,
pg_get_function_identity_arguments(oid) AS args,
provolatile::text, proisstrict::text, prokind::text,
- prorettype::regtype::text AS rettype, proretset::text
+ prorettype::regtype::text AS rettype, proretset::text,
+ COALESCE(probin, '') AS probin,
+ COALESCE(prosrc, '') AS prosrc
FROM pg_proc
WHERE pronamespace = 'ag_catalog'::regnamespace
ORDER BY proname, args;
@@ -113,6 +135,57 @@ SELECT conname::text, contype::text,
FROM pg_constraint
WHERE connamespace = 'ag_catalog'::regnamespace
ORDER BY conname;
+-- Step 7b: Snapshot extension membership (pg_depend deptype='e').
+-- Every object that CREATE EXTENSION owns has a row in pg_depend linking
+-- it to the extension. The upgrade template must produce the same set:
+-- if it CREATEs an object but forgets to ALTER EXTENSION ADD it, the
+-- catalog row exists (so funcs_match/rels_match would pass) but the
+-- pg_depend link is absent and pg_dump --extension would diverge.
+CREATE TEMP TABLE _fresh_extmembers AS
+SELECT
+ CASE d.classid
+ WHEN 'pg_proc'::regclass
+ THEN 'function: ' || d.objid::regprocedure::text
+ WHEN 'pg_type'::regclass
+ THEN 'type: ' || d.objid::regtype::text
+ WHEN 'pg_class'::regclass
+ THEN 'relation: ' || d.objid::regclass::text
+ || ' (' || (SELECT relkind::text FROM pg_class WHERE oid =
d.objid) || ')'
+ WHEN 'pg_operator'::regclass
+ THEN 'operator: ' || d.objid::regoperator::text
+ WHEN 'pg_cast'::regclass
+ THEN 'cast: ' || (SELECT castsource::regtype::text || ' -> '
|| casttarget::regtype::text
+ FROM pg_cast WHERE oid = d.objid)
+ WHEN 'pg_opclass'::regclass
+ THEN 'opclass: ' || (SELECT opcname || ' (' || (SELECT amname
FROM pg_am WHERE oid = opcmethod) || ')'
+ FROM pg_opclass WHERE oid = d.objid)
+ WHEN 'pg_constraint'::regclass
+ THEN 'constraint: ' || (SELECT conname || ' on ' ||
conrelid::regclass::text
+ FROM pg_constraint WHERE oid = d.objid)
+ WHEN 'pg_opfamily'::regclass
+ THEN 'opfamily: ' || (SELECT opfname || ' (' || (SELECT amname
FROM pg_am WHERE oid = opfmethod) || ')'
+ FROM pg_opfamily WHERE oid = d.objid)
+ WHEN 'pg_amop'::regclass
+ THEN 'amop: ' || (SELECT (SELECT opfname FROM pg_opfamily
WHERE oid = a.amopfamily)
+ || ' [strategy ' || a.amopstrategy ||
'] '
+ || a.amoplefttype::regtype::text || ','
+ || a.amoprighttype::regtype::text || '
op '
+ || a.amopopr::regoperator::text
+ FROM pg_amop a WHERE a.oid = d.objid)
+ WHEN 'pg_amproc'::regclass
+ THEN 'amproc: ' || (SELECT (SELECT opfname FROM pg_opfamily
WHERE oid = p.amprocfamily)
+ || ' [proc ' || p.amprocnum || '] '
+ || p.amproclefttype::regtype::text ||
','
+ || p.amprocrighttype::regtype::text ||
' fn '
+ || p.amproc::regprocedure::text
+ FROM pg_amproc p WHERE p.oid = d.objid)
+ ELSE 'unhandled[' || d.classid::regclass::text || ']'
+ END AS member
+FROM pg_depend d
+WHERE d.deptype = 'e'
+ AND d.refclassid = 'pg_extension'::regclass
+ AND d.refobjid = (SELECT oid FROM pg_extension WHERE extname = 'age')
+ORDER BY 1;
-- Step 8: Drop AGE entirely.
DROP EXTENSION age;
-- Step 9: Verify we have an upgrade path available.
@@ -124,6 +197,19 @@ FROM pg_available_extension_versions WHERE name = 'age';
(1 row)
-- Step 10: Install AGE at the synthetic initial version.
+--
+-- Disable check_function_bodies for this CREATE only. The synthetic
+-- '*_initial' SQL is pulled from a fixed historical commit and may
+-- declare C functions whose symbols have since been removed from the
+-- current age.so. With check_function_bodies=on, PostgreSQL would dlsym
+-- each such symbol at CREATE FUNCTION time and abort. Deferring the
+-- symbol probe to call time is safe because step 11 (ALTER EXTENSION
+-- UPDATE) immediately runs the upgrade template, which DROPs any
+-- removed-in-HEAD functions before the test (or any user) can call them.
+-- The fresh CREATE EXTENSION at step 35 keeps the GUC at its default,
+-- so any inconsistency between HEAD's SQL and HEAD's age.so is still
+-- caught at install time on the production code path.
+SET check_function_bodies = off;
DO $$
DECLARE init_ver text;
BEGIN
@@ -138,6 +224,7 @@ BEGIN
EXECUTE format('CREATE EXTENSION age VERSION %L', init_ver);
END;
$$;
+RESET check_function_bodies;
-- Step 11: Upgrade to the current (default) version via the stamped template.
DO $$
DECLARE curr_ver text;
@@ -160,15 +247,17 @@ FROM pg_available_extensions WHERE name = 'age';
(1 row)
-- =====================================================================
--- UPGRADED INSTALL SNAPSHOTS (Steps 13-18)
+-- UPGRADED INSTALL SNAPSHOTS (Steps 13-18b)
-- Capture the catalog state after upgrade from initial to current.
-- =====================================================================
--- Step 13: Snapshot functions.
+-- Step 13: Snapshot functions (probin/prosrc included; see step 2).
CREATE TEMP TABLE _upgraded_funcs AS
SELECT proname::text,
pg_get_function_identity_arguments(oid) AS args,
provolatile::text, proisstrict::text, prokind::text,
- prorettype::regtype::text AS rettype, proretset::text
+ prorettype::regtype::text AS rettype, proretset::text,
+ COALESCE(probin, '') AS probin,
+ COALESCE(prosrc, '') AS prosrc
FROM pg_proc
WHERE pronamespace = 'ag_catalog'::regnamespace
ORDER BY proname, args;
@@ -217,8 +306,54 @@ SELECT conname::text, contype::text,
FROM pg_constraint
WHERE connamespace = 'ag_catalog'::regnamespace
ORDER BY conname;
+-- Step 18b: Snapshot extension membership after upgrade (see step 7b).
+CREATE TEMP TABLE _upgraded_extmembers AS
+SELECT
+ CASE d.classid
+ WHEN 'pg_proc'::regclass
+ THEN 'function: ' || d.objid::regprocedure::text
+ WHEN 'pg_type'::regclass
+ THEN 'type: ' || d.objid::regtype::text
+ WHEN 'pg_class'::regclass
+ THEN 'relation: ' || d.objid::regclass::text
+ || ' (' || (SELECT relkind::text FROM pg_class WHERE oid =
d.objid) || ')'
+ WHEN 'pg_operator'::regclass
+ THEN 'operator: ' || d.objid::regoperator::text
+ WHEN 'pg_cast'::regclass
+ THEN 'cast: ' || (SELECT castsource::regtype::text || ' -> '
|| casttarget::regtype::text
+ FROM pg_cast WHERE oid = d.objid)
+ WHEN 'pg_opclass'::regclass
+ THEN 'opclass: ' || (SELECT opcname || ' (' || (SELECT amname
FROM pg_am WHERE oid = opcmethod) || ')'
+ FROM pg_opclass WHERE oid = d.objid)
+ WHEN 'pg_constraint'::regclass
+ THEN 'constraint: ' || (SELECT conname || ' on ' ||
conrelid::regclass::text
+ FROM pg_constraint WHERE oid = d.objid)
+ WHEN 'pg_opfamily'::regclass
+ THEN 'opfamily: ' || (SELECT opfname || ' (' || (SELECT amname
FROM pg_am WHERE oid = opfmethod) || ')'
+ FROM pg_opfamily WHERE oid = d.objid)
+ WHEN 'pg_amop'::regclass
+ THEN 'amop: ' || (SELECT (SELECT opfname FROM pg_opfamily
WHERE oid = a.amopfamily)
+ || ' [strategy ' || a.amopstrategy ||
'] '
+ || a.amoplefttype::regtype::text || ','
+ || a.amoprighttype::regtype::text || '
op '
+ || a.amopopr::regoperator::text
+ FROM pg_amop a WHERE a.oid = d.objid)
+ WHEN 'pg_amproc'::regclass
+ THEN 'amproc: ' || (SELECT (SELECT opfname FROM pg_opfamily
WHERE oid = p.amprocfamily)
+ || ' [proc ' || p.amprocnum || '] '
+ || p.amproclefttype::regtype::text ||
','
+ || p.amprocrighttype::regtype::text ||
' fn '
+ || p.amproc::regprocedure::text
+ FROM pg_amproc p WHERE p.oid = d.objid)
+ ELSE 'unhandled[' || d.classid::regclass::text || ']'
+ END AS member
+FROM pg_depend d
+WHERE d.deptype = 'e'
+ AND d.refclassid = 'pg_extension'::regclass
+ AND d.refobjid = (SELECT oid FROM pg_extension WHERE extname = 'age')
+ORDER BY 1;
-- =====================================================================
--- COMPARISON: Missing or extra objects (Steps 19-33)
+-- COMPARISON: Missing or extra objects (Steps 19-33b)
-- Any rows returned indicate a template deficiency.
-- =====================================================================
-- Step 19: Functions MISSING after upgrade.
@@ -241,13 +376,20 @@ ORDER BY 1;
----------------
(0 rows)
--- Step 21: Function PROPERTY changes (volatility, strictness, kind, return
type).
+-- Step 21: Function PROPERTY changes
+-- (kind, volatility, strictness, return type, return-set, binding,
+-- body/symbol). The probin/prosrc check catches:
+-- * a C function whose symbol was renamed in the upgrade template
+-- * a SQL/plpgsql function whose body was changed in either path
+-- * a language change between the fresh and upgrade installs.
SELECT f.proname || '(' || f.args || ')' AS function_name,
- CASE WHEN f.prokind <> u.prokind THEN 'prokind: ' || f.prokind
|| '->' || u.prokind END AS kind_change,
- CASE WHEN f.provolatile<> u.provolatile THEN 'volatile: ' ||
f.provolatile|| '->' || u.provolatile END AS volatility_change,
- CASE WHEN f.proisstrict<> u.proisstrict THEN 'strict: ' ||
f.proisstrict|| '->' || u.proisstrict END AS strict_change,
- CASE WHEN f.rettype <> u.rettype THEN 'rettype: ' || f.rettype
|| '->' || u.rettype END AS rettype_change,
- CASE WHEN f.proretset <> u.proretset THEN 'retset: ' ||
f.proretset || '->' || u.proretset END AS retset_change
+ CASE WHEN f.prokind <> u.prokind THEN 'prokind: ' ||
f.prokind || '->' || u.prokind END AS kind_change,
+ CASE WHEN f.provolatile <> u.provolatile THEN 'volatile: ' ||
f.provolatile || '->' || u.provolatile END AS volatility_change,
+ CASE WHEN f.proisstrict <> u.proisstrict THEN 'strict: ' ||
f.proisstrict || '->' || u.proisstrict END AS strict_change,
+ CASE WHEN f.rettype <> u.rettype THEN 'rettype: ' ||
f.rettype || '->' || u.rettype END AS rettype_change,
+ CASE WHEN f.proretset <> u.proretset THEN 'retset: ' ||
f.proretset || '->' || u.proretset END AS retset_change,
+ CASE WHEN f.probin <> u.probin THEN 'probin: ' ||
f.probin || '->' || u.probin END AS probin_change,
+ CASE WHEN f.prosrc <> u.prosrc THEN 'prosrc changed' END
AS prosrc_change
FROM _fresh_funcs f
JOIN _upgraded_funcs u USING (proname, args)
WHERE f.provolatile <> u.provolatile
@@ -255,9 +397,11 @@ WHERE f.provolatile <> u.provolatile
OR f.prokind <> u.prokind
OR f.rettype <> u.rettype
OR f.proretset <> u.proretset
+ OR f.probin <> u.probin
+ OR f.prosrc <> u.prosrc
ORDER BY 1;
- function_name | kind_change | volatility_change | strict_change |
rettype_change | retset_change
----------------+-------------+-------------------+---------------+----------------+---------------
+ function_name | kind_change | volatility_change | strict_change |
rettype_change | retset_change | probin_change | prosrc_change
+---------------+-------------+-------------------+---------------+----------------+---------------+---------------+---------------
(0 rows)
-- Step 22: Relations MISSING after upgrade.
@@ -380,6 +524,28 @@ ORDER BY 1;
------------------
(0 rows)
+-- Step 33a: Extension members MISSING after upgrade
+-- (object exists in pg_proc/pg_class/etc. but is not linked to the AGE
+-- extension via pg_depend, i.e. ALTER EXTENSION age ADD ... was forgotten).
+SELECT f.member AS missing_extension_member
+FROM _fresh_extmembers f
+LEFT JOIN _upgraded_extmembers u USING (member)
+WHERE u.member IS NULL
+ORDER BY 1;
+ missing_extension_member
+--------------------------
+(0 rows)
+
+-- Step 33b: Extension members EXTRA after upgrade.
+SELECT u.member AS extra_extension_member
+FROM _upgraded_extmembers u
+LEFT JOIN _fresh_extmembers f USING (member)
+WHERE f.member IS NULL
+ORDER BY 1;
+ extra_extension_member
+------------------------
+(0 rows)
+
-- =====================================================================
-- SUMMARY (Step 34)
-- =====================================================================
@@ -391,10 +557,11 @@ SELECT
(SELECT count(*) FROM _fresh_ops) = (SELECT count(*) FROM
_upgraded_ops) AS ops_match,
(SELECT count(*) FROM _fresh_casts) = (SELECT count(*) FROM
_upgraded_casts) AS casts_match,
(SELECT count(*) FROM _fresh_opclass) = (SELECT count(*) FROM
_upgraded_opclass) AS opclass_match,
- (SELECT count(*) FROM _fresh_constraints) = (SELECT count(*) FROM
_upgraded_constraints) AS constraints_match;
- funcs_match | rels_match | types_match | ops_match | casts_match |
opclass_match | constraints_match
--------------+------------+-------------+-----------+-------------+---------------+-------------------
- t | t | t | t | t | t
| t
+ (SELECT count(*) FROM _fresh_constraints) = (SELECT count(*) FROM
_upgraded_constraints) AS constraints_match,
+ (SELECT count(*) FROM _fresh_extmembers) = (SELECT count(*) FROM
_upgraded_extmembers) AS extmembers_match;
+ funcs_match | rels_match | types_match | ops_match | casts_match |
opclass_match | constraints_match | extmembers_match
+-------------+------------+-------------+-----------+-------------+---------------+-------------------+------------------
+ t | t | t | t | t | t
| t | t
(1 row)
-- =====================================================================
@@ -404,7 +571,8 @@ SELECT
DROP TABLE _fresh_funcs, _upgraded_funcs, _fresh_rels, _upgraded_rels,
_fresh_types, _upgraded_types, _fresh_ops, _upgraded_ops,
_fresh_casts, _upgraded_casts, _fresh_opclass, _upgraded_opclass,
- _fresh_constraints, _upgraded_constraints;
+ _fresh_constraints, _upgraded_constraints,
+ _fresh_extmembers, _upgraded_extmembers;
DROP EXTENSION age;
CREATE EXTENSION age;
-- Step 36: Remove synthetic upgrade test files from the extension directory.
diff --git a/regress/sql/age_upgrade.sql b/regress/sql/age_upgrade.sql
index f56f7ca9..98ce3e21 100644
--- a/regress/sql/age_upgrade.sql
+++ b/regress/sql/age_upgrade.sql
@@ -28,16 +28,30 @@
-- upgrade template.
--
-- Compared catalogs:
--- pg_proc — functions, aggregates, procedures (name, args, properties)
+-- pg_proc — functions, aggregates, procedures (name, args, properties
+-- including probin/prosrc to catch C-symbol renames and
+-- SQL-body changes)
-- pg_class — tables, views, sequences, indexes (name, kind)
-- pg_type — types (name, type category)
-- pg_operator — operators (name, left/right types)
-- pg_cast — casts involving AGE types (source, target, context)
-- pg_opclass — operator classes (name, access method)
-- pg_constraint — constraints (name, type, table, referenced table)
+-- pg_depend — extension membership (every AGE-owned object must be
+-- linked back to the extension via deptype='e'; catches
+-- a missing ALTER EXTENSION age ADD ... in the template)
--
-- All comparison queries should return 0 rows.
--
+-- Note on synthetic-initial install (step 10): the synthetic '*_initial'
+-- snapshot is built from a fixed historical commit, so its CREATE FUNCTION
+-- statements may reference C symbols that have since been removed from the
+-- current age.so. Step 10 disables check_function_bodies so that dlsym is
+-- deferred to call time; the immediately-following ALTER EXTENSION UPDATE
+-- (step 11) DROPs any such retired functions before any plan can call them.
+-- This lets developers cleanly remove deprecated C entry points without
+-- needing to keep error-raising stubs in age.so.
+--
LOAD 'age';
SET search_path TO ag_catalog;
@@ -60,16 +74,24 @@ END
$$;
-- =====================================================================
--- FRESH INSTALL SNAPSHOTS (Steps 2-7)
+-- FRESH INSTALL SNAPSHOTS (Steps 2-7b)
-- Capture the catalog state from the default CREATE EXTENSION install.
-- =====================================================================
-- Step 2: Snapshot functions (includes aggregates via prokind).
+-- probin/prosrc capture the binding to the implementation:
+-- * LANGUAGE c : probin = '$libdir/age', prosrc = C symbol name
+-- (a renamed/retargeted symbol shows up here)
+-- * LANGUAGE sql/plpgsql: probin = NULL, prosrc = function body text
+-- (a body change in the upgrade template shows up
here)
+-- * LANGUAGE internal : probin = NULL, prosrc = builtin name
CREATE TEMP TABLE _fresh_funcs AS
SELECT proname::text,
pg_get_function_identity_arguments(oid) AS args,
provolatile::text, proisstrict::text, prokind::text,
- prorettype::regtype::text AS rettype, proretset::text
+ prorettype::regtype::text AS rettype, proretset::text,
+ COALESCE(probin, '') AS probin,
+ COALESCE(prosrc, '') AS prosrc
FROM pg_proc
WHERE pronamespace = 'ag_catalog'::regnamespace
ORDER BY proname, args;
@@ -125,6 +147,58 @@ FROM pg_constraint
WHERE connamespace = 'ag_catalog'::regnamespace
ORDER BY conname;
+-- Step 7b: Snapshot extension membership (pg_depend deptype='e').
+-- Every object that CREATE EXTENSION owns has a row in pg_depend linking
+-- it to the extension. The upgrade template must produce the same set:
+-- if it CREATEs an object but forgets to ALTER EXTENSION ADD it, the
+-- catalog row exists (so funcs_match/rels_match would pass) but the
+-- pg_depend link is absent and pg_dump --extension would diverge.
+CREATE TEMP TABLE _fresh_extmembers AS
+SELECT
+ CASE d.classid
+ WHEN 'pg_proc'::regclass
+ THEN 'function: ' || d.objid::regprocedure::text
+ WHEN 'pg_type'::regclass
+ THEN 'type: ' || d.objid::regtype::text
+ WHEN 'pg_class'::regclass
+ THEN 'relation: ' || d.objid::regclass::text
+ || ' (' || (SELECT relkind::text FROM pg_class WHERE oid =
d.objid) || ')'
+ WHEN 'pg_operator'::regclass
+ THEN 'operator: ' || d.objid::regoperator::text
+ WHEN 'pg_cast'::regclass
+ THEN 'cast: ' || (SELECT castsource::regtype::text || ' -> '
|| casttarget::regtype::text
+ FROM pg_cast WHERE oid = d.objid)
+ WHEN 'pg_opclass'::regclass
+ THEN 'opclass: ' || (SELECT opcname || ' (' || (SELECT amname
FROM pg_am WHERE oid = opcmethod) || ')'
+ FROM pg_opclass WHERE oid = d.objid)
+ WHEN 'pg_constraint'::regclass
+ THEN 'constraint: ' || (SELECT conname || ' on ' ||
conrelid::regclass::text
+ FROM pg_constraint WHERE oid = d.objid)
+ WHEN 'pg_opfamily'::regclass
+ THEN 'opfamily: ' || (SELECT opfname || ' (' || (SELECT amname
FROM pg_am WHERE oid = opfmethod) || ')'
+ FROM pg_opfamily WHERE oid = d.objid)
+ WHEN 'pg_amop'::regclass
+ THEN 'amop: ' || (SELECT (SELECT opfname FROM pg_opfamily
WHERE oid = a.amopfamily)
+ || ' [strategy ' || a.amopstrategy ||
'] '
+ || a.amoplefttype::regtype::text || ','
+ || a.amoprighttype::regtype::text || '
op '
+ || a.amopopr::regoperator::text
+ FROM pg_amop a WHERE a.oid = d.objid)
+ WHEN 'pg_amproc'::regclass
+ THEN 'amproc: ' || (SELECT (SELECT opfname FROM pg_opfamily
WHERE oid = p.amprocfamily)
+ || ' [proc ' || p.amprocnum || '] '
+ || p.amproclefttype::regtype::text ||
','
+ || p.amprocrighttype::regtype::text ||
' fn '
+ || p.amproc::regprocedure::text
+ FROM pg_amproc p WHERE p.oid = d.objid)
+ ELSE 'unhandled[' || d.classid::regclass::text || ']'
+ END AS member
+FROM pg_depend d
+WHERE d.deptype = 'e'
+ AND d.refclassid = 'pg_extension'::regclass
+ AND d.refobjid = (SELECT oid FROM pg_extension WHERE extname = 'age')
+ORDER BY 1;
+
-- Step 8: Drop AGE entirely.
DROP EXTENSION age;
@@ -133,6 +207,19 @@ SELECT count(*) > 1 AS has_upgrade_path
FROM pg_available_extension_versions WHERE name = 'age';
-- Step 10: Install AGE at the synthetic initial version.
+--
+-- Disable check_function_bodies for this CREATE only. The synthetic
+-- '*_initial' SQL is pulled from a fixed historical commit and may
+-- declare C functions whose symbols have since been removed from the
+-- current age.so. With check_function_bodies=on, PostgreSQL would dlsym
+-- each such symbol at CREATE FUNCTION time and abort. Deferring the
+-- symbol probe to call time is safe because step 11 (ALTER EXTENSION
+-- UPDATE) immediately runs the upgrade template, which DROPs any
+-- removed-in-HEAD functions before the test (or any user) can call them.
+-- The fresh CREATE EXTENSION at step 35 keeps the GUC at its default,
+-- so any inconsistency between HEAD's SQL and HEAD's age.so is still
+-- caught at install time on the production code path.
+SET check_function_bodies = off;
DO $$
DECLARE init_ver text;
BEGIN
@@ -147,6 +234,7 @@ BEGIN
EXECUTE format('CREATE EXTENSION age VERSION %L', init_ver);
END;
$$;
+RESET check_function_bodies;
-- Step 11: Upgrade to the current (default) version via the stamped template.
DO $$
@@ -167,16 +255,18 @@ SELECT installed_version = default_version AS
upgraded_to_current
FROM pg_available_extensions WHERE name = 'age';
-- =====================================================================
--- UPGRADED INSTALL SNAPSHOTS (Steps 13-18)
+-- UPGRADED INSTALL SNAPSHOTS (Steps 13-18b)
-- Capture the catalog state after upgrade from initial to current.
-- =====================================================================
--- Step 13: Snapshot functions.
+-- Step 13: Snapshot functions (probin/prosrc included; see step 2).
CREATE TEMP TABLE _upgraded_funcs AS
SELECT proname::text,
pg_get_function_identity_arguments(oid) AS args,
provolatile::text, proisstrict::text, prokind::text,
- prorettype::regtype::text AS rettype, proretset::text
+ prorettype::regtype::text AS rettype, proretset::text,
+ COALESCE(probin, '') AS probin,
+ COALESCE(prosrc, '') AS prosrc
FROM pg_proc
WHERE pronamespace = 'ag_catalog'::regnamespace
ORDER BY proname, args;
@@ -233,8 +323,55 @@ FROM pg_constraint
WHERE connamespace = 'ag_catalog'::regnamespace
ORDER BY conname;
+-- Step 18b: Snapshot extension membership after upgrade (see step 7b).
+CREATE TEMP TABLE _upgraded_extmembers AS
+SELECT
+ CASE d.classid
+ WHEN 'pg_proc'::regclass
+ THEN 'function: ' || d.objid::regprocedure::text
+ WHEN 'pg_type'::regclass
+ THEN 'type: ' || d.objid::regtype::text
+ WHEN 'pg_class'::regclass
+ THEN 'relation: ' || d.objid::regclass::text
+ || ' (' || (SELECT relkind::text FROM pg_class WHERE oid =
d.objid) || ')'
+ WHEN 'pg_operator'::regclass
+ THEN 'operator: ' || d.objid::regoperator::text
+ WHEN 'pg_cast'::regclass
+ THEN 'cast: ' || (SELECT castsource::regtype::text || ' -> '
|| casttarget::regtype::text
+ FROM pg_cast WHERE oid = d.objid)
+ WHEN 'pg_opclass'::regclass
+ THEN 'opclass: ' || (SELECT opcname || ' (' || (SELECT amname
FROM pg_am WHERE oid = opcmethod) || ')'
+ FROM pg_opclass WHERE oid = d.objid)
+ WHEN 'pg_constraint'::regclass
+ THEN 'constraint: ' || (SELECT conname || ' on ' ||
conrelid::regclass::text
+ FROM pg_constraint WHERE oid = d.objid)
+ WHEN 'pg_opfamily'::regclass
+ THEN 'opfamily: ' || (SELECT opfname || ' (' || (SELECT amname
FROM pg_am WHERE oid = opfmethod) || ')'
+ FROM pg_opfamily WHERE oid = d.objid)
+ WHEN 'pg_amop'::regclass
+ THEN 'amop: ' || (SELECT (SELECT opfname FROM pg_opfamily
WHERE oid = a.amopfamily)
+ || ' [strategy ' || a.amopstrategy ||
'] '
+ || a.amoplefttype::regtype::text || ','
+ || a.amoprighttype::regtype::text || '
op '
+ || a.amopopr::regoperator::text
+ FROM pg_amop a WHERE a.oid = d.objid)
+ WHEN 'pg_amproc'::regclass
+ THEN 'amproc: ' || (SELECT (SELECT opfname FROM pg_opfamily
WHERE oid = p.amprocfamily)
+ || ' [proc ' || p.amprocnum || '] '
+ || p.amproclefttype::regtype::text ||
','
+ || p.amprocrighttype::regtype::text ||
' fn '
+ || p.amproc::regprocedure::text
+ FROM pg_amproc p WHERE p.oid = d.objid)
+ ELSE 'unhandled[' || d.classid::regclass::text || ']'
+ END AS member
+FROM pg_depend d
+WHERE d.deptype = 'e'
+ AND d.refclassid = 'pg_extension'::regclass
+ AND d.refobjid = (SELECT oid FROM pg_extension WHERE extname = 'age')
+ORDER BY 1;
+
-- =====================================================================
--- COMPARISON: Missing or extra objects (Steps 19-33)
+-- COMPARISON: Missing or extra objects (Steps 19-33b)
-- Any rows returned indicate a template deficiency.
-- =====================================================================
@@ -252,13 +389,20 @@ LEFT JOIN _fresh_funcs f USING (proname, args)
WHERE f.proname IS NULL
ORDER BY 1;
--- Step 21: Function PROPERTY changes (volatility, strictness, kind, return
type).
+-- Step 21: Function PROPERTY changes
+-- (kind, volatility, strictness, return type, return-set, binding,
+-- body/symbol). The probin/prosrc check catches:
+-- * a C function whose symbol was renamed in the upgrade template
+-- * a SQL/plpgsql function whose body was changed in either path
+-- * a language change between the fresh and upgrade installs.
SELECT f.proname || '(' || f.args || ')' AS function_name,
- CASE WHEN f.prokind <> u.prokind THEN 'prokind: ' || f.prokind
|| '->' || u.prokind END AS kind_change,
- CASE WHEN f.provolatile<> u.provolatile THEN 'volatile: ' ||
f.provolatile|| '->' || u.provolatile END AS volatility_change,
- CASE WHEN f.proisstrict<> u.proisstrict THEN 'strict: ' ||
f.proisstrict|| '->' || u.proisstrict END AS strict_change,
- CASE WHEN f.rettype <> u.rettype THEN 'rettype: ' || f.rettype
|| '->' || u.rettype END AS rettype_change,
- CASE WHEN f.proretset <> u.proretset THEN 'retset: ' ||
f.proretset || '->' || u.proretset END AS retset_change
+ CASE WHEN f.prokind <> u.prokind THEN 'prokind: ' ||
f.prokind || '->' || u.prokind END AS kind_change,
+ CASE WHEN f.provolatile <> u.provolatile THEN 'volatile: ' ||
f.provolatile || '->' || u.provolatile END AS volatility_change,
+ CASE WHEN f.proisstrict <> u.proisstrict THEN 'strict: ' ||
f.proisstrict || '->' || u.proisstrict END AS strict_change,
+ CASE WHEN f.rettype <> u.rettype THEN 'rettype: ' ||
f.rettype || '->' || u.rettype END AS rettype_change,
+ CASE WHEN f.proretset <> u.proretset THEN 'retset: ' ||
f.proretset || '->' || u.proretset END AS retset_change,
+ CASE WHEN f.probin <> u.probin THEN 'probin: ' ||
f.probin || '->' || u.probin END AS probin_change,
+ CASE WHEN f.prosrc <> u.prosrc THEN 'prosrc changed' END
AS prosrc_change
FROM _fresh_funcs f
JOIN _upgraded_funcs u USING (proname, args)
WHERE f.provolatile <> u.provolatile
@@ -266,6 +410,8 @@ WHERE f.provolatile <> u.provolatile
OR f.prokind <> u.prokind
OR f.rettype <> u.rettype
OR f.proretset <> u.proretset
+ OR f.probin <> u.probin
+ OR f.prosrc <> u.prosrc
ORDER BY 1;
-- Step 22: Relations MISSING after upgrade.
@@ -352,6 +498,22 @@ LEFT JOIN _fresh_constraints f USING (conname, contype,
table_name)
WHERE f.conname IS NULL
ORDER BY 1;
+-- Step 33a: Extension members MISSING after upgrade
+-- (object exists in pg_proc/pg_class/etc. but is not linked to the AGE
+-- extension via pg_depend, i.e. ALTER EXTENSION age ADD ... was forgotten).
+SELECT f.member AS missing_extension_member
+FROM _fresh_extmembers f
+LEFT JOIN _upgraded_extmembers u USING (member)
+WHERE u.member IS NULL
+ORDER BY 1;
+
+-- Step 33b: Extension members EXTRA after upgrade.
+SELECT u.member AS extra_extension_member
+FROM _upgraded_extmembers u
+LEFT JOIN _fresh_extmembers f USING (member)
+WHERE f.member IS NULL
+ORDER BY 1;
+
-- =====================================================================
-- SUMMARY (Step 34)
-- =====================================================================
@@ -364,7 +526,8 @@ SELECT
(SELECT count(*) FROM _fresh_ops) = (SELECT count(*) FROM
_upgraded_ops) AS ops_match,
(SELECT count(*) FROM _fresh_casts) = (SELECT count(*) FROM
_upgraded_casts) AS casts_match,
(SELECT count(*) FROM _fresh_opclass) = (SELECT count(*) FROM
_upgraded_opclass) AS opclass_match,
- (SELECT count(*) FROM _fresh_constraints) = (SELECT count(*) FROM
_upgraded_constraints) AS constraints_match;
+ (SELECT count(*) FROM _fresh_constraints) = (SELECT count(*) FROM
_upgraded_constraints) AS constraints_match,
+ (SELECT count(*) FROM _fresh_extmembers) = (SELECT count(*) FROM
_upgraded_extmembers) AS extmembers_match;
-- =====================================================================
-- CLEANUP (Steps 35-36)
@@ -374,7 +537,8 @@ SELECT
DROP TABLE _fresh_funcs, _upgraded_funcs, _fresh_rels, _upgraded_rels,
_fresh_types, _upgraded_types, _fresh_ops, _upgraded_ops,
_fresh_casts, _upgraded_casts, _fresh_opclass, _upgraded_opclass,
- _fresh_constraints, _upgraded_constraints;
+ _fresh_constraints, _upgraded_constraints,
+ _fresh_extmembers, _upgraded_extmembers;
DROP EXTENSION age;
CREATE EXTENSION age;
