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 1rmzJz-00AzOr-7N for pgsql-general@arkaria.postgresql.org; Wed, 20 Mar 2024 17:01:48 +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 1rmzJx-006a0X-OS for pgsql-general@arkaria.postgresql.org; Wed, 20 Mar 2024 17:01:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rmzJv-006a0N-RA for pgsql-general@lists.postgresql.org; Wed, 20 Mar 2024 17:01:45 +0000 Received: from fout4-smtp.messagingengine.com ([103.168.172.147]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rmzJs-005WkD-38 for pgsql-general@lists.postgresql.org; Wed, 20 Mar 2024 17:01:43 +0000 Received: from compute7.internal (compute7.nyi.internal [10.202.2.48]) by mailfout.nyi.internal (Postfix) with ESMTP id 7212B138011D; Wed, 20 Mar 2024 13:01:39 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute7.internal (MEProxy); Wed, 20 Mar 2024 13:01:39 -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=1710954099; x=1711040499; bh=jvDFElNnPfppXg6R7kZVOHMYU0zQzjssqt05jtdX/ts=; b= LXCBKbJEQKq4L1Or/ROUaz0UnsEF/VV6rGS3C1sC2gQ5DnSGq1X/vpykuISuQ4sJ H0qe0LyTp6iG2phOg46F0Aa2kAsQ2vgTN+AN4mV936uDaaLC5eqjyHWS6vWr6A+Z 0YxmoOLW4g+etC/9fx+pj9XnnDzN1XapM4kKRkKNJyL2AN6xbwOFIgd8hD+pBXkk gs2M6s2v9JdwWvqqY+KdJSs0WeFSbdR96aQpLqg5hM97hDofbDZCCoC8o2kSCAMo oZXq9YIE02NgtxteLpzj+sOEP2DiuqFe141GF8yEjQsk/kr4WN7EaQWwlFc9brwH v7LP3Z0j4vjf98NAjiTykA== 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=1710954099; x= 1711040499; bh=jvDFElNnPfppXg6R7kZVOHMYU0zQzjssqt05jtdX/ts=; b=l l26rIup73ZJRQpqsykwrBJpHa9iFwf8/escZYNEpNF1FyCW/UosBwefQGHfIOPPn SuFIcax4Ii5Fo6h2gqgLhgaR3Zes2vFkDf5+qV61zXP48z+z6vVrKhmKQqTy6o+k 3csHWYOPOXUe4J3hpMiT35CA6dC37zUW7xKCykYQmqzraRVggaAiE9u2xwtANOqW ACcoHUE5NfyaVNLE0J25SFmKufntvH21vrDEKKR09epsRGNHeOD7ZLTESm+fMgmL xWrGR2Dp0OvI0M3JVqsoiXvMB6GU4tilPhQ8vlw5n+tf+2SEq+MHTG7ogQKOg8fj Jfvy++DlKelIqIc9ncJ1w== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrleeggdelhecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfuvfevfhfhjggtgfesthekredttddvjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeefgeefieeutdfggfetgefgheekjeehteeileeigfet ieekjedvieeviefgheevtdenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmh grihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 20 Mar 2024 13:01:38 -0400 (EDT) Message-ID: <2ce8c3d5-2a54-4c8d-b0bd-bb4f4967b515@aklaver.com> Date: Wed, 20 Mar 2024 10:01:37 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Dropping a temporary view? Content-Language: en-US To: Celia McInnis Cc: Erik Wienhold , "pgsql-generallists.postgresql.org" References: <5b2ebf67-df3b-4b94-8a27-74d20d28f014@aklaver.com> 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 09:51, Celia McInnis wrote: > The view is being used in some web query software that multiple people > will be accessing and the contents of the view depend on what the person > is querying, so I think that temporary views or tables are a good idea. > I change to non-temporary views or tables (in a test version of the > software which is not web-crawl-able) when I'm trying to debug things, > and I guess I have to be careful to clean those up when I switch back to > the temporary tables/views. Why change behavior for the tests? Seems that sort of negates the value of the testing. Have you run EXPLAIN ANALYZE on the problem query? > > > > On Wed, Mar 20, 2024 at 11:46 AM Adrian Klaver > > wrote: > > On 3/20/24 08:39, Celia McInnis wrote: > > Ok, thanks - so I guess that means that if there is both a > temporary and > > a non temporary view called "tempvie", > > > > DROP VIEW tempview; > > > > will remove the 1st tempview found, which with my path is the > temporary > > one. Is there some reason why it then took 7 minutes to select > from the > > non-temporary view tempview after I dropped the temporary view > tempview? > > > > I have sometimes had some very long query times when running query > > software, and maybe they are resulting from my switching between > > temporary and non-temporary views of the same name while > debugging. If > > so, is there something I should be doing to clean up any temporary > > messes I am creating? > > What is the purpose of the temp view over the the regular view process? > > How do they differ in data? > > Is all the above happening in one session? > > Have you run EXPLAIN ANALYZE on the select from the regular view? > > > > > Thanks, > > Celia McInnis > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com