Re: [HACKERS] Transaction control in procedures

On Wed, Nov 8, 2017 at 5:48 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 31 October 2017 at 15:38, Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>> Here is a patch that implements transaction control in PL/Python
>> procedures. (This patch goes on top of "SQL procedures" patch v1.)
>
> The patch is incredibly short for such a feature, which is probably a
> good indication that it is feasible.
>
> Amazing!

I have to agree with that. I'm really excited about this...

Some questions:
*) Will it be possible to do operations like this in pl/pgsql?

BEGIN
SELECT INTO r * FROM foo;

START TRANSACTION; -- perhaps we ought to have a special function
for this instead (BEGIN is reserved, etc).
SET transaction_isololation TO serializable;
...

*) Will there be any negative consequences to a procedure running
with an unbounded run time? For example, something like:

LOOP
SELECT check_for_stuff_to_do();

IF stuff_to_do
THEN
do_stuff();
ELSE
PERFORM pg_sleep(1);
END IF;
END LOOP;

*) Will pg_cancel_backend() cancel the currently executing statement
or the procedure? (I guess probably the procedure but I'm curious)

*) Will long running procedures be subject to statement timeout (and
does it apply to the entire procedure)? Will they be able to control
statement_timeout from within the procedure itself?

*) Will pg_stat_activity show the invoking CALL or the currently
executing statement? I see a strong argument for showing both of
these things. although I understand that's out of scope here.

If these questions (especially the first two) come down the correct
way, then it will mean that I can stop coding in other languages
(primarily bash) for a fairly large number of cases that I really
think belong in the database itself. This would really simplify
coding, some things in bash are really awkward to get right such as a
mutex to guarantee single script invocation. My only real dependency
on the operation system environment at that point would be cron to
step in to the backround daemon process (which would immediately set
an advisory lock).

I'm somewhat surprised that SPI is the point of attack for this
functionality, but if it works that's really the best case scenario
(the only downside I can see is that the various out of core pl/s have
to implement the interface individually).

merlin