autonomous transactions (was Re: TODO note)

Robert Haas wrote:
> On Wed, Sep 15, 2010 at 3:37 AM, Colin 't Hart <colinthart@gmail.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