PG Bug reporting form wrote
> I tested this in postgresql versions 9.1, 10.1 and 10.2 on centOS.
>
> In short this is what happens (in a plpgsql function):
> 1.) An insert is done into 'bug' table
> 2.) A SELECT is done to make sure the INSERT was successful
> 3.) Another function (get_bug_id) is called which returns id based on
> value.
> When the function is called directly, it returns the id correctly. When
> it's
> called in an expression, it does not find the inserted row and an
> exception
> is raised.
Significant is that function get_bug_id() being STABLE is necessary for the
bug to occur.
It appears to be not a bug, since documentation reads:
> For functions written in SQL or in any of the standard procedural
> languages, there is a second important property determined by the
> volatility category,
*
> namely the visibility of any data changes that have been made by the SQL
> command that is calling the function. A VOLATILE function will see such
> changes, a STABLE or IMMUTABLE function will not
*
> . This behavior is implemented using the snapshotting behavior of MVCC
> (see Chapter 13): 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.
Although i.m.o. it is strange that one produces the proper result and the
other call does not.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html