Re: MVCC: Using function doing INSERT and returning PK in WHERE clause of SELECT statement

On Mon, 2024-07-01 at 10:33 +0200, Dominique Devienne wrote:
> INSERT'ing a new row is wrapped in a DEFINER function,
> that returns the newly inserted row's OK (and integral ID).
>
> And in the code calling that function, I was
>
>     SELECT * FROM table WHERE ID = insert_row_via_func(....)
>
> to "save a round trip", combining the insertion and the select.
> But of course, it didn't work, and in hindsight, the SCN for the
> SELECT is chosen before the INSERT done inside the function,
> so the new row is not seen, and the select returns nothing.
>
> Is my analysis correct? In terms of the problem?
> And the solutions / work-arounds?

That looks corrent.

Just define the function as RETURNS "table"
and use INSERT ... RETURNING *

Yours,
Laurenz Albe