Is there anyway to associate rows between the old transition table and new transition table for a per-statement update trigger in Postgres? docs
My use-case is that I have a "shadow" table of an existing, concrete table. I'd like to update the shadow table on every UPDATE statement on the concrete table. I know I can use a per-row trigger to accomplish this. Can I use a per-statement trigger instead?
As a concrete example on Postgres 13.3, I'd like something like:
CREATE TABLE product (id bigint, val text /* many more columns */);
CREATE TABLE shadow_product (id bigint, val text);
CREATE FUNCTION update_shadow_product() RETURNS trigger AS
$fn$
DECLARE
old_data text;
new_data text;
BEGIN
UPDATE shadow_product
SET id = t.old_id, val = t.new_val
FROM (
-- How to handle case where ID changes between old and new?
SELECT ot.id as old_id, nt.val as new_val FROM oldtab ot
INNER JOIN newtab nt USING (id)
) t
WHERE id = t.old_id;
RETURN NULL; -- per-statement triggers should always return null
END;
$fn$ LANGUAGE plpgsql;
CREATE TRIGGER update_shadow_products_trigger
AFTER UPDATE ON product
REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
FOR EACH STATEMENT
EXECUTE FUNCTION update_shadow_product();
As a test case:
-- Seed initial data.
INSERT INTO product (id, val) VALUES (1, 2), (10, 20);
INSERT INTO shadow_product SELECT id, val FROM product;
-- Update the primary key which makes it hard to match old to new rows.
UPDATE product SET id = 2 * id WHERE TRUE;
-- Show rows that differ between product and shadow_product.
-- Ideally there's no null values, indicating the tables are the same.
SELECT p.id, p.val, sp.id AS shadow_id, sp.val AS shadow_val
FROM product p
FULL OUTER JOIN shadow_product sp USING (id, val)
/*
* Returns:
*
* +----+----+---------+----------+
* |id |val |shadow_id|shadow_val|
* +----+----+---------+----------+
* |NULL|NULL|1 |2 |
* |2 |2 |NULL |NULL |
* |NULL|NULL|10 |20 |
* |20 |20 |NULL |NULL |
* +----+----+---------+----------+
*/
I'm not sure sure how to associate rows between oldtab and newtab if primary key changes. One thought I had was relying on row_number on oldtab and newtab without an order clause. Some quick tests seem to indicate the row_number order is stable but I'd prefer a more bulletproof method. Is there a better way?
