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

[Laurenz Albe <laurenz.albe@xxxxxxxxxxx>]

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