plv8 on Postgres 9.6: previous INSERT results not available later

216 views Asked by At

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.

0

There are 0 answers