-- SQL API and settings. SELECT pga_test_role('t_fn'); pga_test_role --------------- (1 row) SELECT pga_test_role('t_other'); pga_test_role --------------- (1 row) GRANT CREATE ON SCHEMA public TO t_fn; SET ROLE t_fn; SELECT 1 AS first; NOTICE: [BRONZE] Achievement unlocked: Hello, World (+10) DETAIL: Run your first SELECT. Total score: 10. Unlocked by 7 of 7 players (100%). first ------- 1 (1 row) SELECT achievements.counter('stmt_select'); counter --------- 1 (1 row) SELECT achievements.counter('no_such_counter'); ERROR: unknown achievement counter "no_such_counter" -- opt-out: nothing is counted SET pg_achievements.opt_out = on; SELECT 1; ?column? ---------- 1 (1 row) SELECT 1; ?column? ---------- 1 (1 row) RESET pg_achievements.opt_out; SELECT achievements.counter('stmt_select'); counter --------- 2 (1 row) -- nested statements are not counted unless track_nested (superuser only) DO $$ BEGIN EXECUTE 'CREATE TABLE fn_nested (id int)'; END $$; SELECT achievements.counter('create_table'); counter --------- 0 (1 row) SET pg_achievements.track_nested = on; ERROR: permission denied to set parameter "pg_achievements.track_nested" RESET ROLE; SET pg_achievements.track_nested = on; SET ROLE t_fn; DO $$ BEGIN EXECUTE 'DROP TABLE fn_nested'; EXECUTE 'CREATE TABLE fn_nested (id int)'; END $$; NOTICE: [BRONZE] Achievement unlocked: First Table (+10) DETAIL: Create your first table. Total score: 20. Unlocked by 3 of 7 players (43%). SELECT achievements.counter('create_table'); counter --------- 1 (1 row) RESET ROLE; RESET pg_achievements.track_nested; SET ROLE t_fn; -- notify = off: the achievement is still awarded, silently SET pg_achievements.notify = off; WITH x AS (SELECT 1) SELECT * FROM x; ?column? ---------- 1 (1 row) RESET pg_achievements.notify; SELECT achievements.sync(); sync ------ t (1 row) SELECT code FROM achievements.my ORDER BY code; code ------------- first_cte first_table hello_world (3 rows) -- emoji style (the database and the client use UTF8) SET pg_achievements.notice_style = emoji; SELECT id, rank() OVER (ORDER BY id) FROM (VALUES (1)) v(id); NOTICE: 🥈 [SILVER] Achievement unlocked: Open Window (+25) DETAIL: Use a window function. Total score: 55. Unlocked by 2 of 7 players (29%). id | rank ----+------ 1 | 1 (1 row) RESET pg_achievements.notice_style; -- reset: own progress yes, someone else's no SELECT achievements.reset('t_other'); ERROR: only a superuser can reset achievements of another role CONTEXT: SQL statement "SELECT achievements._reset(role)" PL/pgSQL function achievements.reset(name) line 3 at PERFORM -- the SELECT calling reset() is itself the first statement after the reset, -- so Hello, World is earned again right away SELECT achievements.reset(); NOTICE: [BRONZE] Achievement unlocked: Hello, World (+10) DETAIL: Run your first SELECT. Total score: 10. Unlocked by 7 of 7 players (100%). reset ------- (1 row) SELECT achievements.sync(); sync ------ t (1 row) SELECT code FROM achievements.my ORDER BY code; code ------------- hello_world (1 row) SELECT achievements.counter('stmt_select'), achievements.counter('cte_any'); counter | counter ---------+--------- 3 | 0 (1 row) -- administrative functions are not for everyone SELECT achievements.grant('t_fn', 'yolo'); ERROR: permission denied for function grant SELECT achievements.define('x', 'X', 'X.', 'bronze', 'stmt_select', 1); ERROR: permission denied for function define SELECT achievements.forget_role('t_fn'); ERROR: permission denied for function forget_role RESET ROLE; -- grant by hand (no notice: the granting session is not the receiver) SELECT achievements.grant('t_fn', 'yolo'); grant ------- t (1 row) SELECT achievements.grant('t_fn', 'yolo'); grant ------- f (1 row) SELECT achievements.grant('t_fn', 'no_such_code'); ERROR: unknown achievement "no_such_code" CONTEXT: PL/pgSQL function achievements."grant"(name,text) line 7 at RAISE SET ROLE t_fn; SELECT code FROM achievements.my ORDER BY code; code ------------- hello_world yolo (2 rows) RESET ROLE; -- define a custom achievement on an existing counter SELECT achievements.define('truncator', 'Truncator', 'TRUNCATE 2 tables.', 'silver', 'truncate', 2); define -------- (1 row) SELECT achievements.define('bad', 'Bad', 'Bad.', 'silver', 'no_such_counter', 2); ERROR: unknown counter "no_such_counter" HINT: See SELECT * FROM achievements._counter_keys(). CONTEXT: PL/pgSQL function achievements.define(text,text,text,achievements.tier,text,bigint) line 11 at RAISE -- platinum is reserved for the builtin "collect everything" achievement SELECT achievements.define('shiny', 'Shiny', 'Shiny.', 'platinum', 'truncate', 1); ERROR: the platinum tier is reserved for the builtin achievement "platinum_collection" HINT: Use the bronze, silver or gold tier. CONTEXT: PL/pgSQL function achievements.define(text,text,text,achievements.tier,text,bigint) line 4 at RAISE SELECT achievements.sync(); sync ------ t (1 row) SELECT code, points, builtin FROM achievements.catalog WHERE NOT builtin; code | points | builtin -----------+--------+--------- truncator | 25 | f (1 row) SET ROLE t_fn; CREATE TABLE fn_t (id int); NOTICE: [BRONZE] Achievement unlocked: First Table (+10) DETAIL: Create your first table. Total score: 70. Unlocked by 3 of 7 players (43%). TRUNCATE fn_t; NOTICE: [GOLD] Achievement unlocked: Clean Slate (+50) DETAIL: TRUNCATE a table. Total score: 120. Unlocked by 2 of 7 players (29%). TRUNCATE fn_t; NOTICE: [SILVER] Achievement unlocked: Truncator (+25) DETAIL: TRUNCATE 2 tables. Total score: 145. You are the first of 7 players to unlock it. RESET ROLE; -- custom achievements do not change the meta ones SELECT count(*) FROM achievements.catalog WHERE builtin; count ------- 68 (1 row) DELETE FROM achievements.catalog WHERE code = 'truncator'; -- forget_role refuses existing roles, then cleans up a dropped one SELECT achievements.forget_role('t_other'); ERROR: role "t_other" still exists HINT: Use achievements.reset() for existing roles. CONTEXT: PL/pgSQL function achievements.forget_role(name) line 4 at RAISE SELECT pga_test_role('t_gone'); pga_test_role --------------- (1 row) SET ROLE t_gone; SELECT 1 AS soon_gone; NOTICE: [BRONZE] Achievement unlocked: Hello, World (+10) DETAIL: Run your first SELECT. Total score: 10. Unlocked by 8 of 8 players (100%). soon_gone ----------- 1 (1 row) RESET ROLE; SELECT achievements.sync(); sync ------ t (1 row) DROP OWNED BY t_gone; DROP ROLE t_gone; SELECT count(*) FROM achievements.counters WHERE role_name = 't_gone'; count ------- 4 (1 row) SELECT achievements.forget_role('t_gone'); forget_role ------------- (1 row) SELECT count(*) FROM achievements.counters WHERE role_name = 't_gone'; count ------- 0 (1 row) SELECT count(*) FROM achievements.awarded WHERE role_name = 't_gone'; count ------- 0 (1 row) -- Rows restored by pg_dump while the server runs meet the rows the worker -- has written: they are merged (sums add up, maxima and the earlier award -- time win), and the worker keeps adding to them instead of overwriting. SELECT pga_test_role('t_merge'); pga_test_role --------------- (1 row) SET ROLE t_merge; \set QUIET on SET pg_achievements.notify = off; SELECT 1 FROM generate_series(1, 3); ?column? ---------- 1 1 1 (3 rows) SELECT achievements.sync(); sync ------ t (1 row) \set QUIET off RESET ROLE; RESET -- the last sync() of t_merge counts once it has finished: write it too SELECT achievements.sync(); sync ------ t (1 row) SELECT counter_key, value FROM achievements.counters WHERE role_name = 't_merge' AND counter_key IN ('stmt_select', 'rows_processed') ORDER BY counter_key; counter_key | value ----------------+------- rows_processed | 4 stmt_select | 2 (2 rows) -- what COPY of a dump does: plain inserts, some of them for existing keys INSERT INTO achievements.counters (role_name, counter_key, value) VALUES ('t_merge', 'stmt_select', 100), ('t_merge', 'rows_processed', 1000), ('t_merge', 'join_count_max', 5), ('t_merge', 'cte_any', 7); INSERT 0 2 INSERT INTO achievements.awarded (role_name, code, awarded_at) VALUES ('t_merge', 'hello_world', '2000-01-01 00:00:00+00'); INSERT 0 0 SET ROLE t_merge; SET \set QUIET on -- one more SELECT with a CTE and a JOIN (join_count_max 1 < 5) WITH x AS (SELECT 1 AS id) SELECT count(*) FROM x JOIN x y USING (id); count ------- 1 (1 row) SELECT achievements.sync(); sync ------ t (1 row) RESET pg_achievements.notify; \set QUIET off RESET ROLE; RESET SELECT achievements.sync(); sync ------ t (1 row) SELECT counter_key, value FROM achievements.counters WHERE role_name = 't_merge' AND counter_key IN ('stmt_select', 'rows_processed', 'join_count_max', 'cte_any') ORDER BY counter_key; counter_key | value ----------------+------- cte_any | 8 join_count_max | 5 rows_processed | 1006 stmt_select | 104 (4 rows) SELECT code, awarded_at AT TIME ZONE 'UTC' AS awarded_at_utc FROM achievements.awarded WHERE role_name = 't_merge' AND code = 'hello_world'; code | awarded_at_utc -------------+-------------------------- hello_world | Sat Jan 01 00:00:00 2000 (1 row)