-- create() / create_unlogged() on a queue that already exists: no DDL and no catalog writes when -- everything is in place, and anything that is missing still gets (re)created. CREATE EXTENSION IF NOT EXISTS pgmq; CREATE EXTENSION IF NOT EXISTS pg_partman; -- xmin of every catalog row create() could write for a queue: the queue/archive tables, the msg_id -- sequence, both indexes, and the vt/enqueued_at columns that carry the pg_monitor grants. CREATE TEMP VIEW queue_catalog_rows AS SELECT c.relname AS name, c.xmin::text AS row_xmin FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'pgmq' UNION ALL SELECT c.relname || '.' || a.attname, a.xmin::text FROM pg_attribute a JOIN pg_class c ON c.oid = a.attrelid JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'pgmq' AND a.attname IN ('vt', 'enqueued_at'); -- test_create_existing_queue_writes_nothing SELECT pgmq.create('existing_q'); create -------- (1 row) CREATE TEMP TABLE rows_before AS SELECT * FROM queue_catalog_rows WHERE name LIKE '%existing_q%'; SELECT pgmq.create('existing_q'); NOTICE: relation "q_existing_q" already exists, skipping NOTICE: relation "a_existing_q" already exists, skipping create -------- (1 row) SELECT b.name, b.row_xmin = r.row_xmin AS unchanged FROM rows_before b JOIN queue_catalog_rows r USING (name) ORDER BY b.name; name | unchanged ----------------------------+----------- a_existing_q | t a_existing_q.enqueued_at | t a_existing_q.vt | t a_existing_q_pkey | t archived_at_idx_existing_q | t q_existing_q | t q_existing_q.enqueued_at | t q_existing_q.vt | t q_existing_q_msg_id_seq | t q_existing_q_pkey | t q_existing_q_vt_idx | t q_existing_q_vt_idx.vt | t (12 rows) SELECT count(*) AS indexes FROM pg_indexes WHERE schemaname = 'pgmq' AND tablename = 'q_existing_q'; indexes --------- 2 (1 row) SELECT count(*) AS indexes FROM pg_indexes WHERE schemaname = 'pgmq' AND tablename = 'a_existing_q'; indexes --------- 2 (1 row) DROP TABLE rows_before; -- test_create_existing_queue_repairs_missing_indexes DROP INDEX pgmq.q_existing_q_vt_idx; DROP INDEX pgmq.archived_at_idx_existing_q; SELECT pgmq.create('existing_q'); NOTICE: relation "q_existing_q" already exists, skipping NOTICE: relation "a_existing_q" already exists, skipping create -------- (1 row) SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = 'pgmq' AND indexname IN ('q_existing_q_vt_idx', 'archived_at_idx_existing_q') ORDER BY indexname; indexname | indexdef ----------------------------+---------------------------------------------------------------------------------------- archived_at_idx_existing_q | CREATE INDEX archived_at_idx_existing_q ON pgmq.a_existing_q USING btree (archived_at) q_existing_q_vt_idx | CREATE INDEX q_existing_q_vt_idx ON pgmq.q_existing_q USING btree (vt) (2 rows) -- test_create_existing_queue_repairs_archive_index_for_mixed_case_name -- the archive index name keeps the queue name as given, while the tables are lowercased SELECT pgmq.create('ExistingMixedQ'); create -------- (1 row) DROP INDEX pgmq."archived_at_idx_ExistingMixedQ"; SELECT pgmq.create('ExistingMixedQ'); NOTICE: relation "q_existingmixedq" already exists, skipping NOTICE: relation "a_existingmixedq" already exists, skipping create -------- (1 row) SELECT to_regclass('pgmq."archived_at_idx_ExistingMixedQ"') IS NOT NULL AS archive_index_restored; archive_index_restored ------------------------ t (1 row) SELECT count(*) AS indexes FROM pg_indexes WHERE schemaname = 'pgmq' AND tablename = 'a_existingmixedq'; indexes --------- 2 (1 row) -- test_create_existing_queue_restores_pg_monitor_grants REVOKE SELECT (vt, enqueued_at) ON pgmq.q_existing_q FROM pg_monitor; REVOKE SELECT ON SEQUENCE pgmq.q_existing_q_msg_id_seq FROM pg_monitor; SELECT has_column_privilege('pg_monitor', 'pgmq.q_existing_q', 'vt', 'SELECT') AS vt, has_column_privilege('pg_monitor', 'pgmq.q_existing_q', 'enqueued_at', 'SELECT') AS enqueued_at, has_sequence_privilege('pg_monitor', 'pgmq.q_existing_q_msg_id_seq', 'SELECT') AS seq; vt | enqueued_at | seq ----+-------------+----- f | f | f (1 row) SELECT pgmq.create('existing_q'); NOTICE: relation "q_existing_q" already exists, skipping NOTICE: relation "a_existing_q" already exists, skipping create -------- (1 row) SELECT has_column_privilege('pg_monitor', 'pgmq.q_existing_q', 'vt', 'SELECT') AS vt, has_column_privilege('pg_monitor', 'pgmq.q_existing_q', 'enqueued_at', 'SELECT') AS enqueued_at, has_sequence_privilege('pg_monitor', 'pgmq.q_existing_q_msg_id_seq', 'SELECT') AS seq; vt | enqueued_at | seq ----+-------------+----- t | t | t (1 row) -- a single missing column grant is restored too REVOKE SELECT (enqueued_at) ON pgmq.q_existing_q FROM pg_monitor; SELECT pgmq.create('existing_q'); NOTICE: relation "q_existing_q" already exists, skipping NOTICE: relation "a_existing_q" already exists, skipping create -------- (1 row) SELECT has_column_privilege('pg_monitor', 'pgmq.q_existing_q', 'enqueued_at', 'SELECT') AS enqueued_at; enqueued_at ------------- t (1 row) -- the grants stay column-level: pg_monitor still can't read message payloads SELECT has_column_privilege('pg_monitor', 'pgmq.q_existing_q', 'message', 'SELECT') AS message; message --------- f (1 row) -- test_create_unlogged_existing_queue_writes_nothing SELECT pgmq.create_unlogged('existing_unlogged_q'); create_unlogged ----------------- (1 row) CREATE TEMP TABLE rows_before AS SELECT * FROM queue_catalog_rows WHERE name LIKE '%existing_unlogged_q%'; SELECT pgmq.create_unlogged('existing_unlogged_q'); NOTICE: relation "q_existing_unlogged_q" already exists, skipping NOTICE: relation "a_existing_unlogged_q" already exists, skipping create_unlogged ----------------- (1 row) SELECT b.name, b.row_xmin = r.row_xmin AS unchanged FROM rows_before b JOIN queue_catalog_rows r USING (name) ORDER BY b.name; name | unchanged -------------------------------------+----------- a_existing_unlogged_q | t a_existing_unlogged_q.enqueued_at | t a_existing_unlogged_q.vt | t a_existing_unlogged_q_pkey | t archived_at_idx_existing_unlogged_q | t q_existing_unlogged_q | t q_existing_unlogged_q.enqueued_at | t q_existing_unlogged_q.vt | t q_existing_unlogged_q_msg_id_seq | t q_existing_unlogged_q_pkey | t q_existing_unlogged_q_vt_idx | t q_existing_unlogged_q_vt_idx.vt | t (12 rows) DROP TABLE rows_before; -- test_create_unlogged_existing_queue_repairs_indexes_and_grants DROP INDEX pgmq.q_existing_unlogged_q_vt_idx; DROP INDEX pgmq.archived_at_idx_existing_unlogged_q; REVOKE SELECT (vt, enqueued_at) ON pgmq.q_existing_unlogged_q FROM pg_monitor; REVOKE SELECT ON SEQUENCE pgmq.q_existing_unlogged_q_msg_id_seq FROM pg_monitor; SELECT pgmq.create_unlogged('existing_unlogged_q'); NOTICE: relation "q_existing_unlogged_q" already exists, skipping NOTICE: relation "a_existing_unlogged_q" already exists, skipping create_unlogged ----------------- (1 row) SELECT indexname FROM pg_indexes WHERE schemaname = 'pgmq' AND indexname IN ('q_existing_unlogged_q_vt_idx', 'archived_at_idx_existing_unlogged_q') ORDER BY indexname; indexname ------------------------------------- archived_at_idx_existing_unlogged_q q_existing_unlogged_q_vt_idx (2 rows) SELECT has_column_privilege('pg_monitor', 'pgmq.q_existing_unlogged_q', 'vt', 'SELECT') AS vt, has_column_privilege('pg_monitor', 'pgmq.q_existing_unlogged_q', 'enqueued_at', 'SELECT') AS enqueued_at, has_sequence_privilege('pg_monitor', 'pgmq.q_existing_unlogged_q_msg_id_seq', 'SELECT') AS seq; vt | enqueued_at | seq ----+-------------+----- t | t | t (1 row) -- the queues keep working after all of the above SELECT pgmq.send('existing_q', '{"ok": true}') > 0 AS sent; sent ------ t (1 row) SELECT message FROM pgmq.read('existing_q', 0, 1); message -------------- {"ok": true} (1 row) SELECT pgmq.send('existing_unlogged_q', '{"ok": true}') > 0 AS sent; sent ------ t (1 row) SELECT message FROM pgmq.read('existing_unlogged_q', 0, 1); message -------------- {"ok": true} (1 row) -- cleanup SELECT pgmq.drop_queue('existing_q'); drop_queue ------------ t (1 row) SELECT pgmq.drop_queue('ExistingMixedQ'); drop_queue ------------ t (1 row) SELECT pgmq.drop_queue('existing_unlogged_q'); drop_queue ------------ t (1 row) DROP VIEW queue_catalog_rows;