From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Dominique Devienne <ddevienne(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: MVCC: Using function doing INSERT and returning PK in WHERE clause of SELECT statement |
Date: | 2024-07-01 12:29:48 |
Message-ID: | e250fb6fc01f97f4de4341bdf6746bd690664aa9.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | yudhi s | 2024-07-02 06:40:44 | Question on partman extension while relation exist |
Previous Message | Dominique Devienne | 2024-07-01 08:33:09 | MVCC: Using function doing INSERT and returning PK in WHERE clause of SELECT statement |