Re: Autonomous Transaction is back

On Thu, Sep 03, 2015 at 04:21:55PM -0400, Robert Haas wrote:
> On Sat, Aug 22, 2015 at 2:23 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> >> > Can you get away with only looking at tuples though? For example,
> >> > what about advisory locks? Table locks?
> >>
> >> Well, that's an interesting question. Can we get away with regarding
> >> those things as non-conflicting, as between the parent and child
> >> transactions?
> >
> > For system lock types, no. While one could define advisory locks to work
> > differently, we should assume that today's advisory lockers have expectations
> > like those of system lockers. An autonomous transaction should not bypass any
> > lock that a transaction of another backend could not bypass.
>
> Why?
>
> Suppose you do this:
>
> BEGIN;
> DECLARE CURSOR foo FOR SELECT * FROM foo;
> BEGIN AUTONOMOUS TRANSACTION;
> ALTER TABLE foo ALTER bar TYPE int;
>
> This has got to fail for safety reasons, but CheckTableNotInUse() is
> on it. Suppose you do this:
>
> BEGIN;
> LOCK foo;
> BEGIN AUTONOMOUS TRANSACTION;
> INSERT INTO foo VALUES ('spelunk');
>
> How will making this fail improve anything?

Core PostgreSQL doesn't care. This is a user interface design decision to be
made in light of current SQL expectations and future SQL author wishes. The
LOCK reference page, our contract with users, says nothing to constrain the
choice. LOCK is exceptional in that we never get much insight into the
caller's expectations. I think LOCK should follow closely the built-in
commands that take the same locks. This variation of your examples must fail
in order to avoid a paradox if the first transaction aborts:

BEGIN;
ALTER TABLE foo ALTER bar TYPE frob;
BEGIN AUTONOMOUS TRANSACTION;
INSERT INTO foo VALUES ('spelunk');

If we made that fail and made your second example succeed, that would imply
"LOCK foo" acquires a special kind of AccessExclusiveLock differing from what
ALTER TABLE acquires. That's incompatible with my sense of the LOCK command's
role in the system. An ability to procure an option to acquire, without
waiting, a lock and delegate that option to another transaction would have
applications. It's a different feature calling for distinct syntax.

My comments have flowed out of a principle that autonomous transactions shall
have precisely the same semantics as using another backend via dblink. They
should have less overhead. They may give different error messages. They
shall not permit sequences of commands that fail in a dblink implementation of
the same multi-transaction sequence. I chose this principle because it fits
my intuitive notion of transaction "autonomy" and because everything I've
heard about other implementations suggests that they work in that way. If
some RDBMS implementation does otherwise, I would find that persuasive.

What design principle(s) have you been using to decide how autonomous
transactions should behave?