2

Given (PostgreSQL 15.3):

create table test (
    test_id int2,
    test_val int4,
    primary key (test_id)
);

If these two transactions are run in parallel at read committed isolation level:

-- Transaction 1
insert into test (select 1, 1 from pg_sleep(2));

-- Transaction 2
select coalesce(
    (select test_val from test where test_id = 1),
    (select null::int4 from pg_sleep(5)),
    (select test_val from test where test_id = 1)
);

The second transaction returns null as it should because it is a single statement.

However, if the second transaction is replaced with:

create function select_testval
    (id int2) returns int4
    language sql strict volatile -- stable function will work as previous version
return (select test_val from test where test_id = id);

-- Transaction 2.1
select coalesce(
    (select test_val from test where test_id = 1),
    (select null::int4 from pg_sleep(5)),
    select_testval(1::int2)
);

It returns 1, acting as a series of two statements each seeing a different snapshot of data.

So, my questions are:

  1. Is it a proper behavior for a function call to introduce a separate statement looking at a newer snapshot? If it is, where is it documented?
  2. Apparently, the stable function gets inlined in this case thus remaining a single statement. Is it possible for a volatile function call to get inlined as well under some circumstances thus changing concurrency semantics? If it is, how to control inlining?

Edit 1. Based on the feedback from comments edited the post to pinpoint the actual problem causing confusion. The original questions were rather trivial.

P. S. For context, I discovered this when was trying to find an idiomatic pure-SQL solution to concurrent inserts with unique constraint problem (1, 2), without resorting to plpgsql.

Taras Serduke
  • 31
  • 5
  • How are you testing the parallel transactions? I am trying to reproduce it but I only get `null` only if the insert was not committed, which is expected. – Spyros Palaiokostas Jul 25 '23 at 12:16
  • Well, I usually use Node.js for repeatable testing. When executing transactions simultaneously in two side-by-side SQL console tabs in DBeaver, I add a `commit;` statement after `insert` and use bigger delays. – Taras Serduke Jul 25 '23 at 12:28
  • By the way, which PostgreSQL version are you using? Maybe the problem is version-specific. It was tested only with 15.3. – Taras Serduke Jul 25 '23 at 12:41
  • 2
    But it is all a single statement `SELECT COALESCE(...)` isn't it? You don't expect a single statement to see a changing view of the DB during that statement do you? – Richard Huxton Jul 25 '23 at 13:20
  • Each of your transactions are a single statement. I believe there is no difference between repeatable read and read committed in such a case – jjanes Jul 25 '23 at 13:22
  • Variant 2.1 does see the changes. So I am not convinced about a "single statement" argument unless there is a clear documentation on that. – Taras Serduke Jul 25 '23 at 13:48
  • 1
    @TarasSerduke Yes, [there is](https://dba.stackexchange.com/questions/271925/are-snapshots-generated-for-each-sub-query-inside-a-postgres-read-committed-tran) [clear documentation](https://dba.stackexchange.com/questions/128269/how-do-i-set-the-isolation-level-for-select-statement) on that, so I'm actually kinda surprised that using `VOLATILE` can circumvent the snapshot. – Bergi Jul 25 '23 at 13:56
  • @Bergi Thanks, links you provided answer my questions from semantics standpoint at least. Then the problem comes to what kind of constructs PostgreSQL views as a single statement... – Taras Serduke Jul 25 '23 at 14:21
  • @Bergi Specifying `volatile` is not strictly necessary as it is the default setting. I guess, the case is that specifying `stable` makes the function body inlined thus getting back to a single statement. – Taras Serduke Jul 25 '23 at 14:29

1 Answers1

1

Okay, it seems that I really overlooked the standard documentation:

STABLE and IMMUTABLE functions use a snapshot established as of the start of the calling query, whereas VOLATILE functions obtain a fresh snapshot at the start of each query they execute.

From 38.7. Function Volatility Categories

This behavior has nothing to do with inlining.

It is worth noting that the snapshot a volatile function obtains is in line with transaction isolation level. So when isolation is repeatable read, the function would not see any fresh data from other transactions.

Taras Serduke
  • 31
  • 5