postgresql – Postgres per-statement update trigger relationship between old and new transition tables

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?