Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pN8dV-0001Kp-T8 for pgsql-general@arkaria.postgresql.org; Wed, 01 Feb 2023 08:38:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pN8dU-0005p0-Pd for pgsql-general@arkaria.postgresql.org; Wed, 01 Feb 2023 08:38:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pN8dU-0005or-Bo for pgsql-general@lists.postgresql.org; Wed, 01 Feb 2023 08:38:32 +0000 Received: from mail-wm1-x32b.google.com ([2a00:1450:4864:20::32b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pN8dR-0001Kq-JM for pgsql-general@lists.postgresql.org; Wed, 01 Feb 2023 08:38:31 +0000 Received: by mail-wm1-x32b.google.com with SMTP id hn2-20020a05600ca38200b003dc5cb96d46so754892wmb.4 for ; Wed, 01 Feb 2023 00:38:29 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20210112.gappssmtp.com; s=20210112; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=/d6aScG0RA8R6e95w7NeVYvWRq89Umc1EUHdeIlXuYk=; b=Cm3hKNRYtZudzuglideE+oYkh36KhgFTZr7NqlzcVr5BgRsVIS1iq9HuuxnusLeRs7 2v+PI8SKI6YclRIo0DoiHYn0EE6xA0n6N7bGWylWW3ekUpg3zP2QuN9olnYISVzdq+wB Z9MUEUDIQ7VK4pxTBNtOdxIykJiYazYeuQZefArgANkMBkG2+lO63MC2PN8o7FOBmjtb v33MkLbrR9P2PFAS0ywu7AIM5QLq+LtupPlIN/iGMs1Fz2R2kkr+xVcAoB3Lth1ihAl+ JEVvHb68Kf4CNBVlBdF1Q08mahjrbs3H3xCKF+42vwnxbTYNSkXbYJWM5xW1fmFVoNWS SQNg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=/d6aScG0RA8R6e95w7NeVYvWRq89Umc1EUHdeIlXuYk=; b=jPyyP8Xda34zkxVxj56nGX8kBmD4UNac0JdmC8qUE+kcEH9ISROwA6AJw9PK4x5bmb 4s+xFr4hFojUfnZXh3dAQ6ZPzcpa3pFYz4M0dhQjT/KIIwZaQTIxwn/4WkCBOvJ8Dwh9 0jqGdO0+BgLoZsI8WZNibdm/3rNGgWZfDDIPc1TEPR/Stze3ak5dbuc24212JhY51Y7q bUVTuH0lfiXsaD2cJu+sdGXhUiIjU3nkV41rnnVyS/ToA2BkUc5ka7hm2yM/PxWe5JYD PmPh0sCsSXXIDvPZlQ6NqyjL/nKpJB3/JpOt4AsPG0rCGCk6ZEcY2EypXKW4U4fdYD5L RktQ== X-Gm-Message-State: AO0yUKUTXtoW9SeLY5Jp2yEJUlvbO9/BLQK8Qvf82cQZ4RBi4Be3giBn YF3bec8EPdYo9f0vtztamotEtA== X-Google-Smtp-Source: AK7set/Qd5bHkgCg1WpmwRkco6jgucjDk4pWkaubqpjwKAGSk3P1esVh9udGZEbl62IpBFE2Vek25g== X-Received: by 2002:a05:600c:3151:b0:3dc:5342:9f53 with SMTP id h17-20020a05600c315100b003dc53429f53mr1280663wmo.38.1675240708449; Wed, 01 Feb 2023 00:38:28 -0800 (PST) Received: from localhost.localdomain ([88.116.133.170]) by smtp.gmail.com with ESMTPSA id v10-20020a05600c12ca00b003dc0cb5e3f1sm951626wmd.46.2023.02.01.00.38.27 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 01 Feb 2023 00:38:28 -0800 (PST) Message-ID: <49ef95ee06cb0d94af198cac331f7bc39d743619.camel@cybertec.at> Subject: Re: invisible commit question for sync replication From: Laurenz Albe To: qihua wu , pgsql-general@lists.postgresql.org Date: Wed, 01 Feb 2023 09:38:27 +0100 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.3 (3.46.3-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 wil= l say: > WARNING: =C2=A0canceling wait for synchronous replication due to user req= uest > DETAIL: =C2=A0The transaction has already committed locally, but might no= t have been replicated to the standby >=20 > So the workflow is=20 > 1: commit to local. > 2: waiting for ACK from remote sync. >=20 > When cancel the DML at step 2. the data are arealy on local, that's why i= t's warning. >=20 > But when runs an insert which is waiting for remote ACK, and then query f= rom another > session, I didn't find that row. Why this happen? If the insert is alread= y 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 adve= rtised to the other backends yet. Yours, Laurenz Albe