On Fri, May 6, 2022 at 6:20 PM hubert depesz lubaczewski <depesz@xxxxxxxxxx> wrote: > when defining statement triggers on update I can use: > > REFERENCING OLD TABLE AS xxx NEW TABLE as YYY > > these "pseudo" tables contain rows that were before and after. > > Is the order guaranteed? > > Can I assume that "first" row returned by select from xxx, will be older > version of first row returned by select from yyy? Good question, and one I've wondered about before. I *think* that is currently true, due to implementation details, but it could change. The trigger code fills up both tuplestores (= memory/disk containers for transition tables) in sync with each other in AfterTriggerSaveEvent(), and then NamedTuplestoreScan node emits tuples in insertion order. We already banned the use of transition tables when there is "ON UPDATE OR INSERT" etc so you can't get mixed INSERT/UPDATE/DELETE results which would desynchronise the old and new tuples, and I also wondered if something tricky like FK ON DELETE CASCADE/SET NULL in a self-referencing table could mix some old-with-no-new into UPDATE results, but I can't see how to do that, and I can't think of any other way off the top of my head. Of course, joins etc could destroy the order higher in your query plan. While reading about proposed incremental materialized view ideas, I once idly wondered if it'd be useful, as an extension to the standard, to be able to use WITH ORDINALITY for transition tables (normally only used for unnest(), and in PG also any SRF) and then you could use that + ORDER BY to explicitly state your desired order (and possibly teach the planner that ORDINALITY attributes are path keys by definition so it doesn't need to insert sort nodes in simple cases). That is, instead of relying on scan order. In any case, an in-core IMV feature is allowed to peek deeper into the implementation and doesn't even need SQL here, so I didn't follow that thought very far... I am not sure about this, but I wonder if any user-level portable-across-SQL-implementation user-level scheme for replication/materialization built on top of trigger transition tables would need to require immutable unique keys in the rows in order to be able match up before/after tuples.
