Re: Transactions involving multiple postgres foreign servers, take 2

On Thu, 11 Jun 2020 at 22:21, Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Fri, Jun 5, 2020 at 3:16 PM Masahiko Sawada
> <masahiko.sawada@2ndquadrant.com> wrote:
> >
> > On Thu, 4 Jun 2020 at 12:46, Amit Kapila <amit.kapila16@gmail.com> wrote:
> > >
> > >
> > > +        <para>
> > > +         This parameter can be changed at any time; the behavior for any one
> > > +         transaction is determined by the setting in effect when it commits.
> > > +        </para>
> > >
> > > This is written w.r.t foreign_twophase_commit.  If one changes this
> > > between prepare and commit, will it have any impact?
> >
> > Since the distributed transaction commit automatically uses 2pc when
> > executing COMMIT, it's not possible to change foreign_twophase_commit
> > between prepare and commit. So I'd like to explain the case where a
> > user executes PREPARE and then COMMIT PREPARED while changing
> > foreign_twophase_commit.
> >
> > PREPARE can run only when foreign_twophase_commit is 'required' (or
> > 'prefer') and all foreign servers involved with the transaction
> > support 2pc. We prepare all foreign transactions no matter what the
> > number of servers and modified or not. If either
> > foreign_twophase_commit is 'disabled' or the transaction modifies data
> > on a foreign server that doesn't support 2pc, it raises an error. At
> > COMMIT (or ROLLBACK) PREPARED, similarly foreign_twophase_commit needs
> > to be set to 'required'. It raises an error if the distributed
> > transaction has a foreign transaction and foreign_twophase_commit is
> > 'disabled'.
> >
>
> So, IIUC, it will raise an error if foreign_twophase_commit is
> 'disabled' (or one of the foreign server involved doesn't support 2PC)
> and the error can be raised both when user issues PREPARE or COMMIT
> (or ROLLBACK) PREPARED.  If so, isn't it strange that we raise such an
> error after PREPARE?  What kind of use-case required this?
>

I don’t concrete use-case but the reason why it raises an error when a
user setting foreign_twophase_commit to 'disabled' executes COMMIT (or
ROLLBACK) PREPARED within the transaction involving at least one
foreign server is that I wanted to make it behaves in a similar way of
COMMIT case. I mean, if a user executes just COMMIT, the distributed
transaction is committed in two phases but the value of
foreign_twophase_commit is not changed during these two phases. So I
wanted to require user to set foreign_twophase_commit to ‘required’
both when executing PREPARE and executing COMMIT (or ROLLBACK)
PREPARED. Implementation also can become simple because we can assume
that foreign_twophase_commit is always enabled when a transaction
requires foreign transaction preparation and resolution.

> >
> > >
> > > 4.
> > > +      <entry><structfield>in_doubt</structfield></entry>
> > > +      <entry><type>boolean</type></entry>
> > > +      <entry></entry>
> > > +      <entry>
> > > +       If <literal>true</literal> this foreign transaction is
> > > in-doubt status and
> > > +       needs to be resolved by calling <function>pg_resolve_fdwxact</function>
> > > +       function.
> > > +      </entry>
> > >
> > > It would be better if you can add an additional sentence to say when
> > > and or how can foreign transactions reach in-doubt state.
> > >
>
> +       If <literal>true</literal> this foreign transaction is in-doubt status.
> +       A foreign transaction becomes in-doubt status when user canceled the
> +       query during transaction commit or the server crashed during transaction
> +       commit.
>
> Can we reword the second sentence as: "A foreign transaction can have
> this status when the user has cancelled the statement or the server
> crashes during transaction commit."?

Agreed. Updated in my local branch.

>  I have another question about
> this field, why can't it be one of the status ('preparing',
> 'prepared', 'committing', 'aborting', 'in-doubt') rather than having a
> separate field?

Because I'm using in-doubt field also for checking if the foreign
transaction entry can also be resolved manually, i.g.
pg_resolve_foreign_xact(). For instance, a foreign transaction which
status = 'prepared' and in-doubt = 'true' can be resolved either
foreign transaction resolver or pg_resolve_foreign_xact(). When a user
execute pg_resolve_foreign_xact() against the foreign transaction, it
sets status = 'committing' (or 'rollbacking') by checking transaction
status in clog. The user might cancel pg_resolve_foreign_xact() during
resolution. In this case, the foreign transaction is still status =
'committing' and in-doubt = 'true'. Then if a foreign transaction
resolver process processes the foreign transaction, it can commit it
without clog looking.

> Also, isn't it more suitable to name 'status' field
> as 'state' because these appear to be more like different states of
> transaction?

Agreed.

Regards,

--
Masahiko Sawada            http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services