Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rcSiM-000GOQ-9E for pgsql-general@arkaria.postgresql.org; Tue, 20 Feb 2024 16:11:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rcShM-000gu1-JC for pgsql-general@arkaria.postgresql.org; Tue, 20 Feb 2024 16:10:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rcShL-000gtt-31 for pgsql-general@lists.postgresql.org; Tue, 20 Feb 2024 16:10:24 +0000 Received: from wfhigh3-smtp.messagingengine.com ([64.147.123.154]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rcShH-0001w8-0V for pgsql-general@lists.postgresql.org; Tue, 20 Feb 2024 16:10:22 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailfhigh.west.internal (Postfix) with ESMTP id 66B70180009F; Tue, 20 Feb 2024 11:10:15 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute3.internal (MEProxy); Tue, 20 Feb 2024 11:10:15 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm3; t=1708445414; x=1708531814; bh=N8AkJBIwGMhUG2Hlw5dPHUsyTYfY1D/Mlh/mfdpfSj4=; b= DQAFpQW3CYMthbsD8CCN9bkcgtSbyknjqHRq67fUpbUNTM4ngHV3RUU29/7on/Si jkf95yfSUfvmapJF4dnwc6P/9vcJG6Qiy2VxvbFmaNu2y2kdeQ/Qi03O5CFvi4TB pJyykD9GKrr4vEXhkFyF2wAzzNyVlU6PO4ZD69l1qB/KVRFc0QraeMmorvfPeg9h ylrPOY5iKEVMlfaxC8D7RR/rhVMN6kDKJNYtfl25pEo+zUmUERDcF7FmW5bdV0wL VuWHsKfUu3EEkBX6d5CFSHja0aoufVhYjd9fo3SbxIznYEMr6LAWj6+P//KNyM7Y jqW6hsOwBFmlPQkvrsTLcg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1708445414; x= 1708531814; bh=N8AkJBIwGMhUG2Hlw5dPHUsyTYfY1D/Mlh/mfdpfSj4=; b=e i320dcVP5Nq/Rxah2aSIas5Ph2OZXng54dpx05jOShUDti+NxtSTgKiPcG3/z2FK hcS+vgaeaDCrOluoBxgljFNcDSRsCSA641h7JOL1kcdsc4klgmr1JJR1sFKyyHy2 e0bhXomtJDyGqe9TjnjgUg35XQ128ZjKTdq02+kqzs8fhuTpmr8sn+ZDwvU/oZwo EdZ4mUrkiCb4lQnZ7t92yylX2Fz1hE0i6op2hHLx/AiGQOCJelXk62tze9m1aHXr UWo8IxEQKDrMGwFzSxm7gqbWz1099dgje9z79902naXKuIP11NvbhBir7fw65PW4 yUL08WdDoKc/bkALr7yLg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrfedtgdekiecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfuvfhfhfgjtgfgsehtjeertddtvdejnecuhfhrohhmpeetughrihgr nhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh eqnecuggftrfgrthhtvghrnhepkeefheduvdejiefgieefjedtudduffelvdefleehfedt ieffuefgvdekleegtddvnecuffhomhgrihhnpehpohhsthhgrhgvshhqlhdrohhrghenuc evlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgr nhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 20 Feb 2024 11:10:13 -0500 (EST) Message-ID: Date: Tue, 20 Feb 2024 08:10:12 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Mat Views and Conflicts To: Zahir Lalani , "pgsql-general@lists.postgresql.org" References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2/20/24 04:20, Zahir Lalani wrote: >> 1) When you say '... ran the underlying query ...' are you referring to: >> >> REFRESH MATERIALIZED VIEW >> https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html >> >> 2) What is the complete error message from the Postgres log? >> >>> >>> Have I got it wrong?? >>> >>> Z >>> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com > > To clarify: > > We are not running the REFRESH. We are only running the materialised view. We used to run the non-view query directly and always suffered from the conflict with recovery error due to data changes on the replica server during the query run. We increased the max_standby_streaming_delay quite a lot, but these are very heavy queries which take a long time. So that is when we decided to convert to an MV. We get the conflict a lot less, but still enough to be annoying. This suggests that not all the MV data is cached and it still queries the source tables in some way? 2) What is the complete error message from the Postgres log? > > Z -- Adrian Klaver adrian.klaver@aklaver.com