I wrote a function that creates a row in the table mytable
accordingly to the parameters and returns the id of the created row.
Unfortunately, if I use the following statement, the SELECT
returns no row, as if the transaction of the function was different from the one of the SELECT
.
SELECT * FROM mytable WHERE id = createComplexRow(...);
My understanding was that I was running the same transaction for the select and the function and then should be able to read uncommitted rows.
I am trying with postgres 9.6
Any clue how to make it works properly ?
Let's look at what is going on.
The function is implicitly
VOLATILE
, as it has to be, since it modifies the database.Let's insert a few rows:
Now let's try your statement:
Indeed, no result!
But there are new values in the table:
To see what happens,
EXPLAIN
the query:PostgreSQL scans the table, and for each row found, it calls the function and compares the result to the
id
of the row.When it scans the row with
id = 1
, the function will return 3 (and insert a row). So the row withid = 1
is skipped.Similarly, the row with
id = 2
is skipped, and a new row withid = 4
is created.Now why does execution stop here rather than proceed to scan the two newly created rows?
These lines from the documentation explain it somewhat:
(emphasis mine)
The statement doesn't see the effect of the function because the function is not executed in a previous update, but in the same statement as the
SELECT
.(The same happens in data modifying
WITH
queries; you may find reading that part of the documentation enlightening.)Actually, you should be glad that it is handled that way, otherwise you'd end up with an endless loop that continues inserting rows into the table.