Re: [HACKERS] logical decoding of two-phase transactions - Mailing list pgsql-hackers

On Wed, Dec 16, 2020 at 1:04 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> Thank you for updating the patch. I have two questions:
>
> -----
> @@ -239,6 +239,19 @@ CREATE SUBSCRIPTION <replaceable
> class="parameter">subscription_name</replaceabl
>           </para>
>          </listitem>
>         </varlistentry>
> +       <varlistentry>
> +        <term><literal>two_phase</literal> (<type>boolean</type>)</term>
> +        <listitem>
> +         <para>
> +          Specifies whether two-phase commit is enabled for this subscription.
> +          The default is <literal>false</literal>.
> +          When two-phase commit is enabled then the decoded
> transactions are sent
> +          to the subscriber on the PREPARE TRANSACTION. When
> two-phase commit is not
> +          enabled then PREPARE TRANSACTION and COMMIT/ROLLBACK PREPARED are not
> +          decoded on the publisher.
> +         </para>
> +        </listitem>
> +       </varlistentry>
>
> The user will need to specify the 'two_phase’ option on CREATE
> SUBSCRIPTION. It would mean the user will need to control what data is
> streamed both on publication side for INSERT/UPDATE/DELETE/TRUNCATE
> and on subscriber side for PREPARE. Looking at the implementation of
> the ’two_phase’ option of CREATE SUBSCRIPTION, it ultimately just
> passes the ‘two_phase' option to the publisher. Why don’t we set it on
> the publisher side?
>

There could be multiple subscriptions for the same publication, some
want to decode the transaction at prepare time and others might want
to decode at commit time only.  Also, one subscription could subscribe
to multiple publications, so not sure if it is even feasible to set at
publication level (consider one txn has changes belonging to multiple
publications). This option controls how the data is streamed from a
publication similar to other options like 'streaming'. Why do you
think this should be any different?

> Also, I guess we can improve the description of
> ’two_phase’ option of CREATE SUBSCRIPTION in the doc by adding the
> fact that when this option is not enabled the transaction prepared on
> the publisher is decoded as a normal transaction:
>

Sounds reasonable.

> ------
> +   if (LookupGXact(begin_data.gid))
> +   {
> +       /*
> +        * If this gid has already been prepared then we dont want to apply
> +        * this txn again. This can happen after restart where upstream can
> +        * send the prepared transaction again. See
> +        * ReorderBufferFinishPrepared. Don't update remote_final_lsn.
> +        */
> +       skip_prepared_txn = true;
> +       return;
> +   }
>
> When PREPARE arrives at the subscriber node but there is the prepared
> transaction with the same transaction identifier, the apply worker
> skips the whole transaction. So if the users prepared a transaction
> with the same identifier on the subscriber, the prepared transaction
> that came from the publisher would be ignored without any messages. On
> the other hand, if applying other operations such as HEAP_INSERT
> conflicts (such as when violating the unique constraint) the apply
> worker raises an ERROR and stops logical replication until the
> conflict is resolved. IIUC since we can know that the prepared
> transaction came from the same publisher again by checking origin_lsn
> in TwoPhaseFileHeader I guess we can skip the PREPARE message only
> when the existing prepared transaction has the same LSN and the same
> identifier. To be exact, it’s still possible that the subscriber gets
> two PREPARE messages having the same LSN and name from two different
> publishers but it’s unlikely happen in practice.
>

The idea sounds reasonable. I'll try and see if this works.

Thanks.

--
With Regards,
Amit Kapila.