Read uncommited rows created by postgresql function?

Question

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 ?

Answer 1

Let's look at what is going on.

CREATE TABLE mytable(
   id serial PRIMARY KEY,
   val timestamp with time zone NOT NULL
);

CREATE FUNCTION createcomplexrow() RETURNS integer
   LANGUAGE SQL AS
'INSERT INTO mytable (val) VALUES (current_timestamp) RETURNING id';

The function is implicitly VOLATILE, as it has to be, since it modifies the database.

Let's insert a few rows:

SELECT createcomplexrow();
SELECT createcomplexrow();

Now let's try your statement:

SELECT * FROM mytable WHERE id = createcomplexrow();

 id | val
----+-----
(0 rows)

Indeed, no result!

But there are new values in the table:

SELECT * FROM mytable;

 id |              val
----+-------------------------------
  1 | 2017-07-18 11:50:22.031922+02
  2 | 2017-07-18 11:50:23.640775+02
  3 | 2017-07-18 11:50:31.392773+02
  4 | 2017-07-18 11:50:31.392773+02
(4 rows)

To see what happens, EXPLAIN the query:

EXPLAIN (COSTS off)
   SELECT * FROM mytable WHERE id = createcomplexrow();

             QUERY PLAN
-------------------------------------
 Seq Scan on mytable
   Filter: (id = createcomplexrow())
(2 rows)

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 with id = 1 is skipped.

Similarly, the row with id = 2 is skipped, and a new row with id = 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:

In effect, a SELECT query sees a snapshot of the database as of the instant the query begins to run. However, SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed.

(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.