Re: [HACKERS] Transaction control in procedures - Mailing list pgsql-hackers

On 31 October 2017 at 15:38, Peter Eisentraut
<peter.eisentraut@2ndquadrant.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!

> So you can do this:
>
> CREATE PROCEDURE transaction_test1()
> LANGUAGE plpythonu
> AS $$
> for i in range(0, 10):
>     plpy.execute("INSERT INTO test1 (a) VALUES (%d)" % i)
>     if i % 2 == 0:
>          plpy.commit()
>     else:
>          plpy.rollback()
> $$;
>
> CALL transaction_test1();
>
> I started with PL/Python because the internal structures there are more
> manageable.

AFAICS we execute 10 INSERTs and each one runs in a new top-level xid.
The INSERTs succeed in all cases but we then abort odd-numbered ones
and commit even numbered ones.

What would happen if some of the INSERTs failed? Where would control
go to? (Maybe this is just "no change" in this particular proc)

What happens if the procedure is updated during execution? Presumably
it keeps executing the original version as seen in the initial
snapshot?

Does the xmin of this session advance after each transaction, or do we
hold the snapshot used for the procedure body open, causing us to hold
back xmin and prevent vacuuming from being effective?

What would happen if a procedure recursively called itself? And yet it
was updated half-way through? Would that throw an error (I think it
should).

> Obviously, people will want this for PL/pgSQL as well, and
> I have that in the works.  It's not in a usable state, but I have found
> that the work needed is essentially the same as in PL/Python for example.
>
> I have discovered three groups of obstacles that needed addressing to
> make this work.  At this point, the patch is more of a demo of what
> these issues are, and if we come to satisfactory solutions for each of
> them, things should fall into place more easily afterwards.
>
> 1) While calling CommitTransactionCommand() in the middle of a utility
> command works just fine (several utility commands do this, of course),
> calling AbortCurrentTransaction() in a similar way does not.  There are
> a few pieces of code that think that a transaction abort will always
> result in a return to the main control loop, and so they will just clean
> away everything.  This is what the changes in portalmem.c are about.
> Some comments there already hint about the issue.  No doubt this will
> need further refinement.  I think it would be desirable in general to
> separate the control flow concerns from the transaction management
> concerns more cleanly.

+1

> 2) SPI needs some work.  It thinks that it can clean everything away at
> transaction end.  I have found that instead of TopTransactionContext one
> can use PortalContext and get a more suitable life cycle for the memory.
>  I have played with some variants to make this configurable (e.g.,
> argument to SPI_connect()), but that didn't seem very useful.  There are
> some comments indicating that there might not always be a PortalContext,
> but the existing tests don't seem to mind.  (There was a thread recently
> about making a fake PortalContext for autovacuum, so maybe the current
> idea is that we make sure there always is a PortalContext.)  Maybe we
> need another context like StatementContext or ProcedureContext.
>
> There also needs to be a way via SPI to end transactions and allowing
> *some* cleanup to happen but leaving the memory around.  I have done
> that via additional SPI API functions like SPI_commit(), which are then
> available to PL implementations.  I also tried making it possible
> calling transaction statements directly via SPI_exec() or similar, but
> that ended up a total disaster.  So from the API perspective, I like the
> current implementation, but the details will no doubt need refinement.
>
> 3) The PL implementations themselves allocate memory in
> transaction-bound contexts for convenience as well.  This is usually
> easy to fix by switching to PortalContext as well.  As you see, the
> PL/Python code part of the patch is actually very small.  Changes in
> other PLs would be similar.

Is there some kind of interlock to prevent dropping the portal half way thru?

Will the SPI transaction control functions fail if called from a
normal function?

> Two issues have not been addressed yet:
>
> A) It would be desirable to be able to run commands such as VACUUM and
> CREATE INDEX CONCURRENTLY in a procedure.  This needs a bit of thinking
> and standards-lawyering about the semantics, like where exactly do
> transactions begin and end in various combinations.  It will probably
> also need a different entry point into SPI, because SPI_exec cannot
> handle statements ending transactions.  But so far my assessment is that
> this can be added in a mostly independent way later on.

Sounds like a separate commit, but perhaps it influences the design?

> B) There needs to be some kind of call stack for procedure and function
> invocations, so that we can track when we are allowed to make
> transaction controlling calls.  The key term in the SQL standard is
> "non-atomic execution context", which seems specifically devised to
> cover this scenario.  So for example, if you have CALL -> CALL -> CALL,
> the third call can issue a transaction statement.  But if you have CALL
> -> SELECT -> CALL, then the last call cannot, because the SELECT
> introduces an atomic execution context.  I don't know if we have such a
> thing yet or something that we could easily latch on to.

Yeh. The internal_xact flag is only set at EoXact, so its not really
set as described in the .h
It would certainly be useful to have some state that allows sanity
checking on weird state transitions.

What we would benefit from is a README that gives the theory of
operation, so everyone can read and agree.

Presumably we would need to contact authors of main PL languages to
get them to comment on the API requirements for their languages.

-- 
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers