Re: autonomous transactions

> On Aug 31, 2016, at 6:46 AM, Greg Stark <stark(at)mit(dot)edu> wrote:
>
> Using a background worker mean that the autonomous transaction can't
> access any state from the process memory. Parameters in plpgsql are a
> symptom of this but I suspect there will be others. What happens if a
> statement timeout occurs during an autonomous transaction? What
> happens if you use a pl language in the autonomous transaction and if
> it tries to use non-transactional information such as prepared
> statements?
>
+1 on this.
The proposed solution loosely matches what was done in DB2 9.7 and it runs into the same
complexity. Passing local variable or session level variables back and forth became a source of grief.

At SFDC PG we have taken a different tack:
1. Gather up all the transaction state that is scattered across global variables into a struct
2. backup/restore transaction state when an autonomous transaction is invoked.

This allows full access to all non-transactional state.

The downside is that full access also includes uncommitted DDL (shared recache).
So we had to restrict DDL in the parent transaction prior to the spawning of the child.

If there is interest in exploring this kind of solution as an alternative I can elaborate.

Cheers
Serge