Hi Heikki,

Thank you for the reply.

On Tuesday, March 05, 2024 12:05 MSK, Heikki Linnakangas <hlinn...@iki.fi> 
wrote:
 In a nutshell, this changes PREPARE TRANSACTION so that if
synchronous_commit is 'off', the PREPARE TRANSACTION is not fsync'd to
disk. So if you crash after the PREPARE TRANSACTION has returned, the
transaction might be lost. I think that's completely unacceptable.​​​​​
You are right, the prepared transaction might be lost after crash. The same may 
happen with regular transactions that are not fsync-ed on replica in logical 
replication by default. The subscription parameter synchronous_commit is OFF by 
default. I'm not sure, is there some auto recovery for regular transactions? I 
think, the main difference between these two cases - how to manually recover 
when some PREPARE TRANSACTION or COMMIT PREPARED are lost. For regular 
transactions, some updates or deletes in tables on replica may be enough to fix 
the problem. For twophase transactions, it may be harder to fix it by hands, 
but it is possible, I believe. If you create a custom solution that is based on 
twophase transactions (like multimaster) such auto recovery may happen 
automatically. Another solution is to ignore errors on commit prepared if the 
corresponding prepared tx is missing. I don't know other risks that may happen 
with async commit of twophase transactions.
 If you're ok to lose the prepared state of twophase transactions on
crash, why don't you create the subscription with 'two_phase=off' to
begin with?In usual work, the subscription has two_phase = on. I have to change 
this option at catchup stage only, but this parameter can not be altered. There 
was a patch proposal in past to implement altering of two_phase option, but it 
was rejected. I think, the recreation of the subscription with two_phase = off 
will not work.

I believe, async commit for twophase transactions on catchup will significantly 
improve the catchup performance. It is worth to think about such feature.

P.S. We might introduce a GUC option to allow async commit for twophase 
transactions. By default, sync commit will be applied for twophase 
transactions, as it is now.

With best regards,
Vitaly Davydov

Reply via email to