Re: invisible commit question for sync replication

Hi,

On Wed, Feb 01, 2023 at 02:52:49PM +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?

It works as expected for me, are you sure both sessions are actually connected
to the same server and/or querying the same table?

[1456]rjuju(at)127(dot)0(dot)0(dot)1:14295) rjuju=# select * from tt;
id | val
----+-----
(0 rows)

[1456]rjuju(at)127(dot)0(dot)0(dot)1:14295) rjuju=# insert into tt select 1;
^CCancel request sent
WARNING: 01000: 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.
LOCATION: SyncRepWaitForLSN, syncrep.c:287
INSERT 0 1

[1456]rjuju(at)127(dot)0(dot)0(dot)1:14295) rjuju=# select pg_backend_pid(), * from tt;
pg_backend_pid | id | val
----------------+----+--------
1456 | 1 | <NULL>
(1 row)

and another session:

[3327]rjuju(at)127(dot)0(dot)0(dot)1:14295) rjuju=# select pg_backend_pid(), * from tt;
pg_backend_pid | id | val
----------------+----+--------
3327 | 1 | <NULL>
(1 row)