Re: invisible commit question for sync replication - Mailing list pgsql-general

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@127.0.0.1:14295) rjuju=# select * from tt;
 id | val
----+-----
(0 rows)

[1456]rjuju@127.0.0.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@127.0.0.1:14295) rjuju=# select pg_backend_pid(), * from tt;
 pg_backend_pid | id |  val
----------------+----+--------
           1456 |  1 | <NULL>
(1 row)


and another session:

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