Re: Autonomous Transaction is back

On Wed, Sep 9, 2015 at 9:04 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sun, Sep 6, 2015 at 1:56 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
>> What design principle(s) have you been using to decide how autonomous
>> transactions should behave?
>
> I have to admit to a complete lack of principle. I'm quite frightened
> of what this is going to need from the lock manager, and I'm trying to
> wriggle out of having to do things there that are going to be nastily
> hard. My wriggling isn't going very well, though.

Hm. Here is current dblink behavior:

postgres=# create table l (id int);
CREATE TABLE
postgres=# insert into l values(1);
INSERT 0 1
postgres=# update l set id =2 where id = 1;
UPDATE 1
Time: 0.595 ms
postgres=# select dblink('', 'update l set id = 3 where id = 1');
<hangs forever due to deadlock of client lock and parent execution point>

Does the lock manager really needs to be extended to address this
case? pg_locks pretty clearly explains what's happening, via:
postgres=# select locktype, transactionid, pid, granted from pg_locks
where not granted;
locktype │ transactionid │ pid │ granted
───────────────┼───────────────┼───────┼─────────
transactionid │ 88380 │ 20543 │ f

and

postgres=# select locktype, transactionid, pid, granted from pg_locks
where transactionid = 88380;
locktype │ transactionid │ pid │ granted
───────────────┼───────────────┼───────┼─────────
transactionid │ 88380 │ 20543 │ f
transactionid │ 88380 │ 19022 │ t

If pg_locks and/or pg_stat_activity were extended with a 'parent_pid'
column, a userland query could terminate affected backends with a join
against that column where any ungranted. Naturally there's a lot more
to it than that; you'd want to issue an appropriate cancellation
message and various other things. I suppose I'm probably missing
something, but I'm not clear on why the lock manager needs to be
overhauled to deal with this case when we can just scan current
strictures assuming we can a) manage child pid independently of parent
pid and b) easily figure out who is parent of who.

merlin