From: | Mark Scheffer <pg2401k(at)pinkwin(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #15060: Row in table not found when using pg function in an expression |
Date: | 2018-02-12 13:37:47 |
Message-ID: | 1518442667671-0.post@n3.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Marko Tiikkaja | 2018-02-12 13:56:28 | Re: BUG #15060: Row in table not found when using pg function in an expression |
Previous Message | PG Bug reporting form | 2018-02-12 12:59:04 | BUG #15060: Row in table not found when using pg function in an expression |