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 1qCdWb-00051i-25 for pgsql-general@arkaria.postgresql.org; Fri, 23 Jun 2023 09:56:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qCdWa-00054K-0F for pgsql-general@arkaria.postgresql.org; Fri, 23 Jun 2023 09:56:16 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qCdWZ-00051s-L9 for pgsql-general@lists.postgresql.org; Fri, 23 Jun 2023 09:56:15 +0000 Received: from mail-wr1-x435.google.com ([2a00:1450:4864:20::435]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qCdWW-003mkP-Pu for pgsql-general@lists.postgresql.org; Fri, 23 Jun 2023 09:56:14 +0000 Received: by mail-wr1-x435.google.com with SMTP id ffacd0b85a97d-307d58b3efbso444072f8f.0 for ; Fri, 23 Jun 2023 02:56:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20221208.gappssmtp.com; s=20221208; t=1687514170; x=1690106170; 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=kAV4YcAc3ANUfJNbRzADek8HorOML7DyiZbVw/AJwdg=; b=scc8pS/ZrqFlIJYfRxwUAI4ZhOHXGwr16j+WbcMaTuz8DXmy04G5u+aHbN6iQp0t87 p/bMNhA3qriWjgfaDPW7P30j5wXBka8eurc3YYzIoeww+7J/8YxEPz0/IiqItqP0ONKE Hf0aWIzhZOnVImY1Cy3DQCnzwd/c3Hi4RyYNKyhQir5SDEKgZj+4UuY2A3+b46xq/3lC R9Z//h5OfAet/pD1GdmaitRofhCBO1goH1KG8Qg8CO6gONnRQXvtYWgKtxmW5FCpIp4e 9sGijgeBbLgPSWXiklqVFsgBhL+S7F2djBo4mzQKEfkhrLnC0U/IJjC/SloSMHXZZhz/ jN3A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1687514170; x=1690106170; 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=kAV4YcAc3ANUfJNbRzADek8HorOML7DyiZbVw/AJwdg=; b=WXP0MCX9n5aI1fyfbgKdA9VjqUR5d62ywblpI8JlQ821MpfWexGDlZ5MiSduY+INPJ i2vpYG9s2YwRbuCFIsR2dCUVaX8SuA0O5f0LEpyDfDUrp0zTh2Kswm1sRrhlAeK4R2I4 TqaJiXgATme1+TLXJkC6qWfTtBsebPM16QX6ZSp8MaFOizOCNVb/dkBP6IdqIuJSElVX LmdqjATjIqB78g2AED+qEM7dABu/l7YxJydjZcTOcgwk74X1/aVYMJxLUvgN9rbmYQJv PetDdwV5POvwBhmCUd8wgaVaCA0yQf0T3rASHg0Cdr79uZ4ET/jR6OGtebekt6apsiN9 ZXGw== X-Gm-Message-State: AC+VfDwugpUi/SImOTgYr2gOtZpfQ1ozVQetxom4ZoZ7Jh0mHIEbeRL+ YBGqhd8ORtR9NVzRPMTbomRi9g== X-Google-Smtp-Source: ACHHUZ6AzV8gdm52ihPDMNuMWUa73h0QuPqJb+qRkvZkUt8cb9RsPRCN056iLCAiWR29cvqWDzTUyw== X-Received: by 2002:adf:df12:0:b0:30d:af7c:5046 with SMTP id y18-20020adfdf12000000b0030daf7c5046mr3953237wrl.60.1687514170384; Fri, 23 Jun 2023 02:56:10 -0700 (PDT) Received: from localhost.localdomain ([2001:871:5e:b6f2:2c0d:33fb:f78e:a8df]) by smtp.gmail.com with ESMTPSA id z5-20020a5d6545000000b003113bd9ecaesm9057670wrv.116.2023.06.23.02.56.09 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 23 Jun 2023 02:56:10 -0700 (PDT) Message-ID: <84cc0332fadf4f4bb1fff7a169db8ecd58f7911c.camel@cybertec.at> Subject: Re: synchronous_commit= remote_apply | "The transaction has already committed locally..." From: Laurenz Albe To: Postgres all-rounder , pgsql-general@lists.postgresql.org Date: Fri, 23 Jun 2023 11:56:09 +0200 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 2023-06-23 at 15:05 +0530, Postgres all-rounder wrote: > Context: We have faced a network isolation and ended-up with locally comm= itted data on the=20 > old primary database server as one of the tools that is in-place for HA d= ecided to promote > one of the SYNC standby servers. As the PostgreSQL won't provide a HA sol= ution as in-built, > I would like to just confirm on the behaviour of core parameter synchrono= us_commit=3D remote_apply. >=20 > As per the documentation the PRIMARY database server will NOT commit unle= ss > the SYNC standby acknowledges=C2=A0 that it=C2=A0 received the commit rec= ord of the transaction > and applied it, so that it has become visible to queries on the standby(s= ), and also written > to durable storage on the standbys. That's not true. The primary will commit locally, but wait for the synchro= nous standby servers before it reports success to the client. > However, during the network outage or few scenarios where the current pri= mary is waiting > for the SYNC to acknowledge and when the application sends a cancel signa= l [even control +c > from a PSQL session which inserted data]=C2=A0 then we see locally commit= ted data on the primary > database server. >=20 > "The transaction has already committed locally, but might not have been r= eplicated to the standby." >=20 > 1. It appears to be a known behaviour, however wanted to understand, is t= his considered as an > expected behaviour or limitation with the architecture This is expected behavior AND a limitation of PostgreSQL. > 2. Any known future plans in the backlog to change the behaviour in=20 > such a way PRIMARY won't have the LOCALLY commit data which is NOT receiv= ed and acknowledged > by a SYNC standby when=C2=A0 synchronous_commit=3D remote_apply is used? There have been efforts to use two-phase commit, but that would require Pos= tgreSQL to have its own distributed transaction manager. > 3. If the information is available in the document that primary database = can have locally > committed data when it is waiting on SYNC and receive the cancel signal f= rom the application, > it can be helpful. I don't think that's anywhere in the documentation. Yours, Laurenz Albe