hash_extension(text)

Hi,

When developing extensions, I find myself spending time on manually checking
if my update scripts (invoked via ALTER EXTENSION ... UPDATE) gives the same result as
CREATE EXTENSION ... would do if installing the latest version from scratch without any previous version.

I thought it would be efficient if one could quickly detect such a difference.

If there would be a way to concatenate all extension objects and their definitions in a deterministic order,
a hash could be created, which could then be used to detect differences.

Below is my attempt to implement this idea.

The strategy is fragile as it doesn't handle all the regclasses,
so false negatives are possible, but I don't think false positives should be possible,
if implemented correctly.

Is there a better way to solve my problem already?

Feedback welcome.

Best regards,

Joel

SELECT extversion FROM pg_extension WHERE extname = 'uniphant';
extversion
------------
1.0
(1 row)

SELECT hash_extension('uniphant');
hash_extension
----------------
-1425682867
(1 row)

ALTER EXTENSION uniphant UPDATE;

SELECT hash_extension('uniphant');
hash_extension
----------------
-1615520783
(1 row)

DROP EXTENSION uniphant;

CREATE EXTENSION uniphant;

SELECT hash_extension('uniphant');
hash_extension
----------------
-1615520783
(1 row)

CREATE OR REPLACE FUNCTION hash_extension(text)
RETURNS integer
STABLE
LANGUAGE sql
AS $$
--
-- Constructs a text string containing most of all the extension objects
-- and their create definitions.
--
-- This is useful to detect a diff between the result of
--
-- ALTER EXTENSION ... UPDATE;
-- SELECT hash_extension(...);
--
-- compared to if one would install the latest version
-- of the extension from scratch using
--
-- CREATE EXTENSION ...;
-- SELECT hash_extension(...);
--
-- This could happen if the author of the extension
-- made a mistake in the update scripts.
--
-- This function is meant to be useful to check
-- the correctness of such update scripts.
--
SELECT hashtext(jsonb_agg(jsonb_build_array(
pg_describe_object,
CASE classid
WHEN 'pg_namespace'::regclass THEN (
SELECT jsonb_build_array(pg_roles.rolname, pg_namespace.nspacl)
FROM pg_namespace
JOIN pg_roles ON pg_roles.oid = pg_namespace.nspowner
WHERE pg_namespace.oid = q.objid
)
WHEN 'pg_proc'::regclass THEN jsonb_build_array(pg_get_functiondef(objid))
WHEN 'pg_class'::regclass THEN (
SELECT jsonb_agg(jsonb_build_array(
a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull,
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid
AND a.attcollation <> t.typcollation),
a.attidentity,
a.attgenerated
) ORDER BY a.attnum)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = q.objid
AND a.attnum > 0
AND NOT a.attisdropped
)
END,
classid::regclass
) ORDER BY pg_describe_object)::text)
FROM (
SELECT pg_describe_object(classid, objid, 0), classid::regclass, objid
FROM pg_depend
WHERE refclassid = 'pg_extension'::regclass
AND refobjid = (SELECT oid FROM pg_extension WHERE extname = $1)
) AS q
$$;