-- ALTER EXTENSION UPDATE from 1.0 (through 1.1) gives the same catalog, -- functions, triggers and views as a fresh install of the latest version, -- and keeps the progress made under 1.0. SET pg_achievements.notify = off; -- tier as text: a column of the extension's type would block DROP EXTENSION CREATE TABLE fresh_catalog AS SELECT code, title, description, tier::text, points, secret, counter_key, threshold, enabled FROM achievements.catalog WHERE builtin; CREATE TABLE fresh_functions AS SELECT p.proname, pg_get_functiondef(p.oid) AS def FROM pg_proc p WHERE p.pronamespace = 'achievements'::regnamespace; CREATE TABLE fresh_views AS SELECT c.relname, pg_get_viewdef(c.oid) AS def FROM pg_class c WHERE c.relnamespace = 'achievements'::regnamespace AND c.relkind = 'v'; DROP EXTENSION pg_achievements; CREATE EXTENSION pg_achievements VERSION '1.0'; SELECT achievements.sync(); sync ------ t (1 row) SELECT achievements.version(); version ----------------------------------------------------------------- pg_achievements 1.0 (library 1.2), catalog 1.0, 46 achievements (1 row) -- progress under 1.0: a platinum achievement and the old meta one SELECT pga_test_role('t_upgrade'); pga_test_role --------------- (1 row) SELECT achievements.grant('t_upgrade', 'ten_joins'); grant ------- t (1 row) SELECT achievements.grant('t_upgrade', 'platinum_collection'); grant ------- t (1 row) -- a custom platinum achievement is allowed in 1.0 and survives the update SELECT achievements.define('old_shiny', 'Old Shiny', 'Shiny.', 'platinum', 'truncate', 1); define -------- (1 row) ALTER EXTENSION pg_achievements UPDATE TO '1.2'; SELECT achievements.sync(); sync ------ t (1 row) SELECT achievements.version(); version ----------------------------------------------------------------- pg_achievements 1.2 (library 1.2), catalog 1.1, 69 achievements (1 row) -- the builtin catalog and the functions match a fresh install (TABLE fresh_catalog EXCEPT SELECT code, title, description, tier::text, points, secret, counter_key, threshold, enabled FROM achievements.catalog WHERE builtin) UNION ALL (SELECT code, title, description, tier::text, points, secret, counter_key, threshold, enabled FROM achievements.catalog WHERE builtin EXCEPT TABLE fresh_catalog); code | title | description | tier | points | secret | counter_key | threshold | enabled ------+-------+-------------+------+--------+--------+-------------+-----------+--------- (0 rows) SELECT proname FROM ( (TABLE fresh_functions EXCEPT SELECT p.proname, pg_get_functiondef(p.oid) FROM pg_proc p WHERE p.pronamespace = 'achievements'::regnamespace) UNION ALL (SELECT p.proname, pg_get_functiondef(p.oid) FROM pg_proc p WHERE p.pronamespace = 'achievements'::regnamespace EXCEPT TABLE fresh_functions)) d; proname --------- (0 rows) SELECT relname FROM ( (TABLE fresh_views EXCEPT SELECT c.relname, pg_get_viewdef(c.oid) FROM pg_class c WHERE c.relnamespace = 'achievements'::regnamespace AND c.relkind = 'v') UNION ALL (SELECT c.relname, pg_get_viewdef(c.oid) FROM pg_class c WHERE c.relnamespace = 'achievements'::regnamespace AND c.relkind = 'v' EXCEPT TABLE fresh_views)) d; relname --------- (0 rows) -- the merge and announce triggers exist after the update too SELECT tgrelid::regclass AS "table", tgname FROM pg_trigger WHERE tgrelid IN ('achievements.counters'::regclass, 'achievements.awarded'::regclass) AND NOT tgisinternal ORDER BY 1, 2; table | tgname -----------------------+---------------- achievements.counters | merge_existing achievements.awarded | announce achievements.awarded | merge_existing (3 rows) -- awards are kept, with the new tiers and points SET ROLE t_upgrade; SELECT code, tier, title, points FROM achievements.my ORDER BY code; code | tier | title | points ---------------------+----------+-----------------+-------- platinum_collection | platinum | Slonik's Chosen | 300 ten_joins | gold | Ten JOINs | 50 (2 rows) RESET ROLE; SELECT code, tier, points FROM achievements.catalog WHERE NOT builtin; code | tier | points -----------+----------+-------- old_shiny | platinum | 100 (1 row) -- the update refuses to run over a custom achievement with a new code DROP EXTENSION pg_achievements; CREATE EXTENSION pg_achievements VERSION '1.0'; SELECT achievements.define('streak', 'Mine', 'Mine.', 'bronze', 'connect', 1); define -------- (1 row) \set VERBOSITY terse ALTER EXTENSION pg_achievements UPDATE TO '1.1'; ERROR: custom achievements use codes of new builtin achievements: streak \set VERBOSITY default SELECT achievements.version(); version ----------------------------------------------------------------- pg_achievements 1.0 (library 1.2), catalog 1.0, 47 achievements (1 row) -- without it the update goes through DELETE FROM achievements.catalog WHERE code = 'streak'; ALTER EXTENSION pg_achievements UPDATE TO '1.1'; SELECT achievements.version(); version ----------------------------------------------------------------- pg_achievements 1.1 (library 1.2), catalog 1.1, 68 achievements (1 row)