On Fri, May 6, 2022 at 6:20 PM hubert depesz lubaczewski
<depesz@depesz.com> 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.