Bulk Insert and Update — Five Records

Insert five records

INSERT INTO records (data) VALUES
    ('{"name": "Gadget B", "qty": 5}'),
    ('{"name": "Gadget C", "qty": 12}'),
    ('{"name": "Gadget D", "qty": 3}'),
    ('{"name": "Gadget E", "qty": 99}'),
    ('{"name": "Gadget F", "qty": 7}');

Each insert queues two events (one json_record, one audit_action). As the Processor drains them, it creates five more audit_action events from the records_json insert triggers — fifteen events in total for five inserts.

Then:

SELECT count(*) FROM records_json;   -- should be 6 (1 from earlier + 5 new)
SELECT count(*) FROM records_audit;  -- should be 14 (4 from earlier + 10 new)

Update all records in one statement

UPDATE records SET data = data || '{"reviewed": true}';

This fires the update trigger once per row — all six records, including record 1 from the previous chapter — so six json_record events and six RECORD_UPDATED audit events. Each json_record update fires the records_json update trigger in turn — six more UPDATED_JSON_RECORD events. Eighteen more events in total.

Then:

SELECT count(*) FROM records_audit;  -- should be 26 (14 + 12)

View the event queue

By now, every event should have been dispatched:

SELECT id, channel, queued_at, dispatched_at
FROM pgrelay.queue
ORDER BY id
LIMIT 20;

Rows with dispatched_at set have already been processed. Rows with dispatched_at NULL are still waiting (there should be none).

View the event log

SELECT id, channel, status, elapsed_ms, actioned_at
FROM pgrelay.log
ORDER BY id;

Every row here is one processed event. Status ok means the action ran without error. You should have around 39 log entries at this point — 3 events per operation, across 6 inserts (1 in the previous chapter plus 5 here) and 7 updates (1 in the previous chapter plus 6 from the bulk update above).

-- See the outcome breakdown:
SELECT channel, status, count(*)
FROM pgrelay.log
GROUP BY channel, status
ORDER BY channel, status;

---

Continue to Scheduling a Purge to see pg_relay schedule its own future work.