On Mon, 28 Nov 2022 at 18:53, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> On 2022-Nov-28, Simon Riggs wrote:
>
> > A narrative description of the issue follows:
> > session1 - requests multiple nested subtransactions like this:
> > BEGIN; ...
> > SAVEPOINT subxid1; ...
> > SAVEPOINT subxid2; ...
>
> > However, if subxid2 subcommits, then the lock wait moves from subxid2
> > to the topxid.
>
> Hmm, do we really do that? Seems very strange .. it sounds to me like
> the lock should have been transferred to subxid1 (which is subxid2's
> parent), not to the top-level Xid.
Correct; that is exactly what I'm saying and why we have a bug since
3c27944fb2141.
> Maybe what the user wanted was to
> release subxid1 before establishing subxid2? Or do they want to
> continue to be able to rollback to subxid1 after establishing subxid2?
> (but why?)
This isn't a description of a user's actions, it is a script that
illustrates the bug in XactLockTableWait().
Perhaps a better example would be nested code blocks with EXCEPTION
clauses where the outer block fails...
e.g.
DO $$
BEGIN
SELECT 1;
BEGIN
SELECT 1;
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 's2';
END;
RAISE division_by_zero; -- now back in outer subxact, which now fails
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 's1';
END;$$;
Of course, debugging this is harder since there is no way to return
the current subxid in SQL.
--
Simon Riggs http://www.EnterpriseDB.com/
