MIME-Version: 1.0
From: Dominique Devienne <ddevienne@gmail.com>
Date: Mon, 1 Jul 2024 10:33:09 +0200
Message-ID: <CAFCRh-_R8Q2_=jeWpe+RWt798hhwAQLoPF=YG+nUnJXTg80qhA@mail.gmail.com>
Subject: MVCC: Using function doing INSERT and returning PK in WHERE clause of
 SELECT statement
To: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-_R8Q2_%3DjeWpe%2BRWt798hhwAQLoPF%3DYG%2BnUnJXTg80qhA%40mail.gmail.com>
Precedence: bulk

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