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 1roY8i-0025Lr-OG for pgsql-general@arkaria.postgresql.org; Mon, 25 Mar 2024 00:24:37 +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 1roY8g-00GGq5-Uy for pgsql-general@arkaria.postgresql.org; Mon, 25 Mar 2024 00:24:35 +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 1roY8f-00GGpv-5d for pgsql-general@lists.postgresql.org; Mon, 25 Mar 2024 00:24:35 +0000 Received: from fhigh7-smtp.messagingengine.com ([103.168.172.158]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1roY8b-006JCC-Cq for pgsql-general@lists.postgresql.org; Mon, 25 Mar 2024 00:24:32 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailfhigh.nyi.internal (Postfix) with ESMTP id 09C8D1140109; Sun, 24 Mar 2024 20:24:27 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute6.internal (MEProxy); Sun, 24 Mar 2024 20:24:27 -0400 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=fm1; t=1711326267; x=1711412667; bh=ZHdGgfdNveF/Pd0PHS+kUhuepfQXVI3SHZShyYY6ypA=; b= Tp88gTDNTsq5QIvSy06j0g6CZz+znGftQwyKkSyinm7jY6Z3mPw0ozH93D8oOQTa pckTxupdaayIqvi+ukqTtIe2j2wIbSBs7BqIIXbRC35hkKZTfIfYmOwoQnyeELwH GUV4PNFuDO7ceBdXHkpX6U6yPYMxGqphr8oe53KWnjvr4w5qK9fYmaZvT5t1HmIM G80LFhKfel6f/YJVym/fXPxX0SrqzHXxbrCHA4Xb8+hCI95hErKSSER6CXDvZTvP gJe0QTUDWG1Bw2nPBzvwGde0HJpDFQCVfmFHFyElZsVZP4l2c5/olFmPMlRun3q4 QlYk7wqkDafAXuhr8HgE3g== 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=fm2; t=1711326267; x= 1711412667; bh=ZHdGgfdNveF/Pd0PHS+kUhuepfQXVI3SHZShyYY6ypA=; b=A 3zsIzBcmeuDlEq+VF4JZWGIEJn6IaQSaGW1hLu9mDzn1TW2/1x/Rm96XjvWT8Qig Js56PxPGfrBoM0855OD5xxsmsjKuVLqG7qZqfcq7quFiponpO4OcyCC56f3ftRh6 Tzi1Q572xh1QYRiAzhm9ZMZjc3Zxu1Fv5+IiK8fWnlCQ+ntrOj1J0WvB2QxloT7X PDSNgPRGU5L5emNXPD2AjjGo2nm5uEDz0KZrmayEPOlgxw9gd369A5lRbK6xbALT sr2Bz2xdtGA7Mgdy6YIcaH0QAfRm5VXL3qMD/CHe+LIc9/IXU6jPZTTLTbBhyMPe 1V5CJnfhfd4KltAY+nGDw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledruddtkedgvdduucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhfhjggtgfesthekredttddvjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeffleegieefgfevudehtdfhkeeutdffjeevgeffgeej vedthefgudeiteefheejheenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmh grihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Sun, 24 Mar 2024 20:24:26 -0400 (EDT) Message-ID: Date: Sun, 24 Mar 2024 17:24:25 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Empty materialized view To: Thiemo Kellner , "pgsql-general@lists.postgresql.org" References: <33aa6b8e-86fc-4f10-9242-e0c9ad39c007@gelassene-pferde.biz> <2ec1a2bd-7791-4cc5-90ed-1b7b606aa420@gelassene-pferde.biz> <61e2c2b7-1098-4179-91a2-24e06e0683a9@aklaver.com> <983c4ea7-4861-436a-a23e-f40780c9f0d6@gelassene-pferde.biz> 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/24/24 14:27, Thiemo Kellner wrote: >>> Feeling quite dumb now. But then, there neither is data visible in >>> the install session. >>> >>> insert data into TASK_DEPENDENCY⠒V >>> INSERT 0 34 >> >> The above says the data was inserted. > > But not into the MV but into TASK_DEPENDENCY⠒V. > >> Where and when was the count query run? > > > Excerpt of the according protocol: > > ## tenth level ## > Set materialised view QUERY_PER_TASK⠒MV up > psql:views/QUERY_PER_TASK⠒MV.pg_sql:3: HINWEIS:  materialisierte Sicht > »query_per_task⠒mv« existiert nicht, wird übersprungen > DROP MATERIALIZED VIEW > SELECT 0 > REFRESH MATERIALIZED VIEW > COMMENT > COMMIT At above you have not entered the data into the tables the MV depends on so SELECT 0 is reasonable. > # insert data # > ## first level ## > insert data into CENTRICITY > INSERT 0 2 > COMMIT > insert data into DIRECTION > INSERT 0 8 > COMMIT > insert data into GOOD_CLASS > INSERT 0 15 > COMMIT > insert data into NODE_TYPE > INSERT 0 3 > COMMIT > insert data into REGION > INSERT 0 15 > COMMIT > insert data into TASK_TYPE > INSERT 0 5 > COMMIT > ## second level ## > insert data into AREA > INSERT 0 16 > COMMIT > insert data into DISTANCE⠒V > INSERT 0 3 > COMMIT > insert data into GOOD⠒V > INSERT 0 164 > COMMIT > insert data into MAP⠒V > INSERT 0 41 > COMMIT > ## third level ## > insert data into DIRECT_NEIGHBOUR > INSERT 0 8 > INSERT 0 16 > COMMIT > ### Scandinavia ### > insert data into NODE⠒V > INSERT 0 112 > COMMIT > insert data into PRODUCTION⠒V > INSERT 0 11 > COMMIT > insert data into TASK⠒V > INSERT 0 56 > COMMIT > ## forth level ## > Scandinavia > insert data into DROP_OFF⠒V > INSERT 0 91 > COMMIT > insert data into PICK_UP⠒V > INSERT 0 73 > COMMIT > insert data into TASK_DEPENDENCY⠒V > INSERT 0 34 > COMMIT >  count > ------- >     66 > (1 row) > >  count > ------- >      0 > (1 row) The 0 count above represents the below correct? : select count(*) from SNOWRUNNER.QUERY_PER_TASK⠒MV; If so, again that is reasonable as I don't see anywhere you refresh QUERY_PER_TASK⠒MV after the underlying tables have data entered. At this point it is still at the state you left it at here: ## tenth level ## Set materialised view QUERY_PER_TASK⠒MV up psql:views/QUERY_PER_TASK⠒MV.pg_sql:3: HINWEIS: materialisierte Sicht »query_per_task⠒mv« existiert nicht, wird übersprungen DROP MATERIALIZED VIEW SELECT 0 REFRESH MATERIALIZED VIEW COMMENT COMMIT > > COMMIT > > > -- Adrian Klaver adrian.klaver@aklaver.com