The following rules govern the visibility of data changes in
functions that use SPI (or any other C function): During the
execution of an SQL command, any data changes made by the
command are invisible to the command itself. For example, in:
INSERT INTO a SELECT * FROM a; the inserted rows are invisible
to the SELECT part. Changes made by a command C are visible to
all commands that are started after C, no matter whether they
are started inside C (during the execution of C) or after C is
done. Commands executed via SPI inside a function called by an
SQL command (either an ordinary function or a trigger) follow
one or the other of the above rules depending on the read/write
flag passed to SPI. Commands executed in read-only mode follow
the first rule: they cannot see changes of the calling command.
Commands executed in read-write mode follow the second rule:
they can see all changes made so far. All standard procedural
languages set the SPI read-write mode depending on the
volatility attribute of the function.