Обсуждение: MVCC: Using function doing INSERT and returning PK in WHERE clause of SELECT statement

Hi. So for business rule reason,
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.

The work-around is easy, just make two statements.
But of course that's two round-trips.

I looked at DO blocks, to perform both statements in the same rtrip.
But DO block don't return anything. I guess I could have two functions,
the existing one just returning the ID, and another returning the table
row type, and the latter calling the former? That's just more code to
maintain.

Is my analysis correct? In terms of the problem?
And the solutions / work-arounds?

Thanks for any insights. --DD

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