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 1rn2fa-00BIaS-Fu for pgsql-general@arkaria.postgresql.org; Wed, 20 Mar 2024 20:36:19 +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 1rn2fX-0085TP-W5 for pgsql-general@arkaria.postgresql.org; Wed, 20 Mar 2024 20:36:16 +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 1rn2fW-0085TH-8n for pgsql-general@lists.postgresql.org; Wed, 20 Mar 2024 20:36:16 +0000 Received: from wfout5-smtp.messagingengine.com ([64.147.123.148]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rn2fR-005bZ8-Fl for pgsql-general@lists.postgresql.org; Wed, 20 Mar 2024 20:36:14 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailfout.west.internal (Postfix) with ESMTP id E32831C000C5; Wed, 20 Mar 2024 16:36:05 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute1.internal (MEProxy); Wed, 20 Mar 2024 16:36:06 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc: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=fm1; t=1710966965; x=1711053365; bh=TrJr56XJlObDQIoh92QEdmGt/SuKSSew6oGKbgem5DE=; b= C0/GdcbuJqdokkZkzf80y05mHUmlujdH0wBcBbZA71lh3qdDGpnLnOzQuzMiHm9+ xEoiQ2LgqX1fpVlGNWRdWKhCrZHVRHiIMx/4vbEqm+NoZFPhjVBRfAX1Lh1d2NU6 EGXVU3O2dLPoQ8LhM9EZAYsZgNkVYYMlw0tQiIBbp15pschlPuzc9AEv5vCRNqGC VZK6wGX63AseIaNg217p8mx1ecJ5+mwArtA5JWJaQ9UveXMHtc+ZL2iYOOv/NSy6 bzKIyY4TX/lxNfMbK4vc+SQgyjcL33oM5n7iaLBJkQCUVQDthlicIIG8uqhXjEeZ 5a6NerQO2kRLBcE8dTyzMA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc: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=fm2; t=1710966965; x= 1711053365; bh=TrJr56XJlObDQIoh92QEdmGt/SuKSSew6oGKbgem5DE=; b=W dBEwqKHkeeWrgl8dTIklU/qO4vFFG7Jw5c/lNYZS02dinuvqJjHpkni4cE+OjRbx d8VQ8oQJGm1cWIFO9lCcvYx7u9UDkdFzWcQzliG4bTBL+vShZ7q4f9StNfSrY6oX +2U5fWG6cPUdiMSg9GeCcu96YF9VuRQp3wT0IlKVFDfYOe1/aoolY1MQm5Xjh39N LZ9rU257hpF1WGbCemfDDsFzZP+1fALKGAlJZP7ve1GjCnq8u2QfRcaADOrb605Z FHfBIS1xQpmy9vXltOUSDENnmGRU8y4wAaQtjW593W00YYkkh9NJZI9+U3h4LBYS tZR2njF4oQdiKi3BREh7Q== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrleeggddufeekucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvvehfhfgjtgfgsehtkeertddtvdejnecuhfhrohhmpeetughr ihgrnhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtg homheqnecuggftrfgrthhtvghrnhepgfdufeekhfevfeelveeiueevhedvuddukeduvddv lefhueeuieejtdeuvdevvdeunecuffhomhgrihhnpehpohhsthhgrhgvshhqlhdrohhrgh enucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughr ihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 20 Mar 2024 16:36:04 -0400 (EDT) Message-ID: <8796920e-19d9-4edd-94cf-dac43e2ece75@aklaver.com> Date: Wed, 20 Mar 2024 13:36:03 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Dropping a temporary view? To: Celia McInnis Cc: Erik Wienhold , "pgsql-generallists.postgresql.org" References: <5b2ebf67-df3b-4b94-8a27-74d20d28f014@aklaver.com> <2ce8c3d5-2a54-4c8d-b0bd-bb4f4967b515@aklaver.com> <6d2cb339-7558-4392-9af9-3d6e3de5a595@aklaver.com> Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 3/20/24 13:00, Celia McInnis wrote: > > > On Wed, Mar 20, 2024 at 2:15 PM Adrian Klaver > wrote: > > __ > > > On 3/20/24 10:54 AM, Celia McInnis wrote: > > Comments below more to sort out the process in my head then anything > else. >> Hi Adrian >> >> The only behaviour changed for the debugging was to make the view >> non-temporary, so that I could verify in psql that the content of >> the view was what I wanted it to be. Debugging CGI software can be >> quite difficult, so it's always good to have debugging hooks as a >> part of the software - I know that I always have a DEBUG flag >> which, if on, prints out all kinds of stuff into a debug file, and >> I just had my software set a different name for DEBUG mode's >> non-temporary view than I was using for the temporary view, as >> advised by Christophe Pettus. > > This indicates you are working in different sessions and therefore > creating a regular view to see the same data in all sessions. > > Previously this regular view was named the same as the temporary > view you create in the production database. > > Now you name that regular view a unique name not to conflict with > the temporary view name(s). > >> No, unfortunately I didn't do an explain on the slow query - and >> it's too late now since the views are removed. However, I never >> had a delay when waiting for the view to be created in my web >> software, so, I'll just proceed being more careful and hope that >> the delay seen was due to some big mess I created. > > In your original post you say the delay occurred on a SELECT not a > CREATE VIEW after: > > Correct. But the initial CREATE VIEW was done  as a SELECT from the > database, so if the create view was quick, I thought that the select > from the view would be equally quick. Is this a faulty assumption? https://www.postgresql.org/docs/current/sql-createview.html "CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query." In addition the 'canned' query is running against tables(excepting the VALUES case) which in turn maybe getting queries(SELECT, INSERT, UPDATE, DELETE) from other sources. This means that each SELECT from a view could be seeing an entirely different state. The above is in reference to a regular(temporary or not) view not a: https://www.postgresql.org/docs/current/sql-creatematerializedview.html "CREATE MATERIALIZED VIEW defines a materialized view of a query. The query is executed and used to populate the view at the time the command is issued (unless WITH NO DATA is used) and may be refreshed later using REFRESH MATERIALIZED VIEW." -- Adrian Klaver adrian.klaver@aklaver.com