0

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?

1

The way to identity a table row is the primary key, and you can use that here if both tables have the same primary key. If the primary keys are automatically generated and different, use another unique constraint. After all, there should be a way to relate rows between those two tables.

4
  • The primary keys are the same so I can relate product to shadow_product using the id column. From there I can figure out the set of shadow_product rows to update but I don't know what to update them to because there's no way to map between oldtab and newtab (if the PK is updated). I suppose the mapping doesn't matter; I can just blindly update each old row in shadow_product with values from the newtab. That should work as long as count(oldtab) always equals count(newtab). – Joe 2 days ago
  • Do you really need to change primary keys? If not, forbid it, then you can associate rows using the primary key. – Laurenz Albe 2 days ago
  • It's unlikely but yes, the primary keys might change. Another option: if the primary keys do change, give up with an update and delete all rows from oldtab and then insert all rows from newtab. – Joe 23 hours ago
  • That's a good idea. Perhaps transactions that modify a primary key (I would call this "surgery") don't modify many rows at once, then it wouldn't matter. – Laurenz Albe 21 hours ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.