autonomous transactions (was Re: TODO note)

Robert Haas wrote:
> On Wed, Sep 15, 2010 at 3:37 AM, Colin 't Hart <colinthart(at)gmail(dot)com> wrote:
>> I note that the implementation of tab completion for SET TRANSACTION in PSQL
>> could benefit from the implementation of autonomous transactions (also
>> TODO).
>
> I think it's safe to say that if we ever manage to get autonomous
> transactions working, there are a GREAT MANY things which will benefit
> from that. There's probably an easier way to get at that Todo item,
> though, if someone feels like beating on it.
>
> One problem with autonomous transactions is that you have to figure
> out where to store all the state associated with the autonomous
> transaction and its subtransactions. Another is that you have to
> avoid an unacceptable slowdown in the tuple-visibility checks in the
> process.

As I understand it, in many ways, autonomous transactions are like distinct
database client sessions, but that the client in this case is another database
session, especially if the autonomous transaction can make a commit that
persists even if the initial session afterwards does a rollback.

Similarly, using autonomous transactions is akin to multi-processing. Normal
distinct database client sessions are like distinct processes, but usually are
started externally to the DBMS, but autonomous transactions are like processes
started within the DBMS.

Also, under the assumption that everything in a DBMS session should be subject
to transactions, so that both data-manipulation and data-definition can be
rolled back, autonomous transactions are like a generalization of supporting
sequence generators that remember their incremented state even when the action
that incremented it is rolled back; the sequence generator update is effectively
an autonomous transaction, in that case.

The point being, the answer to how to implement autonomous transactions could be
as simple as, do the same thing as how you manage multiple concurrent client
sessions, more or less. If each client gets its own Postgres OS process, then
an autonomous transaction just farms out to another one of those which does the
work. Or maybe there could be a lighter weight version of this.

Does this design principle seem reasonable?

If autonomous transactions could be used a lot, then maybe the other process
could be kept connected and be fed other subsequent autonomous actions, such as
if it is being used to implement an activity log, so some kind of IPC would be
going on.

-- Darren Duncan