Our database in PostgreSQL 9.6 consists of a very complicated structure of hundreds of PostgreSQL functions of which most are written in plv8/JavaScript (if that structure makes sense, is a different point. But a complete rewrite is out of the question right now.)
The problem is: A record written to a table is sometimes not available when trying to access it later.
Consider the code snippet below:
DROP TABLE IF EXISTS my_temp;
CREATE TABLE my_temp(id bigint, foo text);
DO $$
plv8.execute("INSERT INTO my_temp(id,foo) VALUES($1,$2)",[1,'foo1']);
var fetchRow = plv8.execute("SELECT * FROM my_temp WHERE id = $1",[1]);
plv8.elog(INFO,fetchRow[0].foo);
$$ LANGUAGE plv8;
That works as expected, INFO: foo1is put out.
However, imagine, that this doesn't happen in one statement (and especially not in a DO block, that's just for demonstration of the principle) but instead, consider there are dozens of functions involved, function A calls function B and so on.
At a certain point, in function Z I want to retrieve a record which I had inserted in function A (the ID being passed via parameters) - and it is not there.
Same happens sometimes with UPDATE: I run an update in function A and in function Z I want to fetch the new content - However, the content is the old one - as if the UPDATE statement in function A had never been executed.
Everything works fine if I split up the whole thing into to separate statements: If function B is not called from within function A, but called like
SELECT function_A();
SELECT function_B();
then everything is always OK, but that approach is not feasible in my case.
However, I cannot isolate that phenomenon. It sometimes just happens at certain spots. If it happens, it's reproducible at that point.
plv8 version used is 1.4.8.
