Re: invisible commit question for sync replication

In the code it will write a warning to postgresql log. Why not also write
the detailed sql? with the exact sql, DBA might do something to fix the
issue.

if (ProcDiePending)
{
ereport(WARNING,
(errcode(ERRCODE_ADMIN_SHUTDOWN),
errmsg("canceling the wait for synchronous replication and terminating
connection due to administrator command"),
errdetail("The transaction has already committed locally, but might not
have been replicated to the standby.")));
whereToSendOutput = DestNone;
SyncRepCancelWait();
break;
}

On Wed, Feb 1, 2023 at 4:38 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Wed, 2023-02-01 at 14:52 +0800, qihua wu wrote:
> > When run a cluster with sync replication, if DML is done on primary, but
> primary is
> > isolated from all slave, then the DML will hang, if cancel it DML, it
> will say:
> > WARNING: canceling wait for synchronous replication due to user request
> > DETAIL: The transaction has already committed locally, but might not
> have been replicated to the standby
> >
> > So the workflow is
> > 1: commit to local.
> > 2: waiting for ACK from remote sync.
> >
> > When cancel the DML at step 2. the data are arealy on local, that's why
> it's warning.
> >
> > But when runs an insert which is waiting for remote ACK, and then query
> from another
> > session, I didn't find that row. Why this happen? If the insert is
> already one locally,
> > whey another session can't read it?
>
> COMMIT is not as atomic as it appears. When the backend is waiting for
> the standby,
> it has already committed the transaction on disk, but that fact is not
> advertised to
> the other backends yet.
>
> Yours,
> Laurenz Albe
>