Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hC07V-0006Hf-6J for pgsql-hackers@arkaria.postgresql.org; Thu, 04 Apr 2019 11:01:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hC07S-0003V6-U4 for pgsql-hackers@arkaria.postgresql.org; Thu, 04 Apr 2019 11:01:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hC07S-0003T0-Hx for pgsql-hackers@lists.postgresql.org; Thu, 04 Apr 2019 11:01:18 +0000 Received: from out5-smtp.messagingengine.com ([66.111.4.29]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hC07P-0005eV-Du for pgsql-hackers@postgresql.org; Thu, 04 Apr 2019 11:01:17 +0000 Received: from compute7.internal (compute7.nyi.internal [10.202.2.47]) by mailout.nyi.internal (Postfix) with ESMTP id 8F4472206E; Thu, 4 Apr 2019 07:01:14 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute7.internal (MEProxy); Thu, 04 Apr 2019 07:01:14 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:from:in-reply-to:message-id:mime-version:references :subject:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm2; bh=BEt+uRMPICU/sh6eSY7cAZ8RnN3qBWZGvSmvKdztc Jw=; b=2aO7Q0e3PCtghBeBMuBL2Vmds6vE2w2CniKELvTeEYB/0k9wikNJzqsvB S5p985eeOrL65gn0uk8TNZcNgevBEE49f1Lc7BG6l6u18kJ7uYogHDa73DY66FCb dJhmGX915u30A3AdWeQdsuq8uszvbUhZz9heVsNqjN4S1RtcCc01kZEd8XC1wb3L V4vs36nKczgcCm1zR3BRrV27DLg/BetUXID0mj0S1Fijvve7uRlHEe+nL8K21egn l7d1i1uD/d81U7NJvyruO7U/+s6p8iXZDKp7l4URDx260OTCx3MFHsbhUsGvrAvt Knm6kF9yimLw8MZkwSE2E7pDXGWeQ== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeduuddrtdehgdefgeculddtuddrgedutddrtddtmd cutefuodetggdotefrodftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdp uffrtefokffrpgfnqfghnecuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivg hnthhsucdlqddutddtmdenucfjughrpefuvfhfhfhokffffgggjggtgfesthekredttdef heenucfhrhhomheprfgvthgvrhcugfhishgvnhhtrhgruhhtuceophgvthgvrhdrvghish gvnhhtrhgruhhtsedvnhguqhhurggurhgrnhhtrdgtohhmqeenucffohhmrghinhepvdhn ughquhgrughrrghnthdrtghomhenucfkphepleefrddvgeefrddukeekrdduvddvnecurf grrhgrmhepmhgrihhlfhhrohhmpehpvghtvghrrdgvihhsvghnthhrrghuthesvdhnughq uhgrughrrghnthdrtghomhenucevlhhushhtvghrufhiiigvpedt X-ME-Proxy: Received: from april.pezone.net (p5df3bc7a.dip0.t-ipconnect.de [93.243.188.122]) by mail.messagingengine.com (Postfix) with ESMTPA id 4E6FEE4482; Thu, 4 Apr 2019 07:01:12 -0400 (EDT) Subject: Re: [HACKERS] generated columns To: Amit Langote , Erik Rijkers References: <20180119051804.GC12826@paquier.xyz> <2e3d5147-16f8-af0f-00ab-4c72cafc896f@2ndquadrant.com> <20180131131804.GB1586@paquier.xyz> <20180202022532.GA84623@paquier.xyz> <050e663d-d915-f61b-af96-5a09c10ade02@2ndquadrant.com> <79e4de23-52e3-2c45-9ba0-5f6932f590aa@2ndquadrant.com> <28574501540912440@iva5-750e13568e4d.qloud-c.yandex.net> <2ddd0bfa-0fcd-35ec-266c-f03cf72fc581@2ndquadrant.com> <26129f50-cf47-2013-8f9b-e7a98853aa69@2ndquadrant.com> <17fd00ff02ede468e2a3d63ff8cd5ff7@xs4all.nl> <59e69246-8f32-b3cb-af4c-8c359d4049c7@2ndquadrant.com> <854c50a1-b050-1308-4222-a6278b079c7d@2ndquadrant.com> Cc: pgsql-hackers , Sergei Kornilov , Michael Paquier , Jaime Casanova From: Peter Eisentraut Openpgp: preference=signencrypt Autocrypt: addr=peter.eisentraut@2ndquadrant.com; prefer-encrypt=mutual; keydata= xsFNBFUUt6kBEADZd9NNTBJbHmAXXJgPGOdd6Q73l80oifIVgn2tRxP9dYkCfTlo/yLqbKTl JNqTcyM6XrJDnB2sMEUGYCewYZXl+9C4bBJWyNqcEI1ae9u71vEq/fknFLArJ+2wN+f7QuRx Q8d94ZJAiJQ5g3fNk2HVeedCQCKP5dCzU+ZLt2dbsx1PDZLjTO9gf+JswWuGFb5wY5PZVM5V hq66Xk7ZJzHuARacza2RT7J2fkMmQBY+kr7L98Perb0GhafnlhXB0PvUB+N3xpmggMfwuhTu BngS/G/r3GvemxJuFxouw8xsMqrsfdksJXA2gLXYtwRCssdPV3PO0rlD9twqDo8URfIJ5fb6 1g/MV8aTOKjg3Q8MpJPtkwJqN4o3PvbwYdedeFnOVkkYAJdu/aX1v1BeCBDu7Hy3sF52scD3 pQbyQSl9vUDpTLarASAxNbAV0NRJE8dp4vfW6AH1ccryGiFqw/czPK/iN0+MT8wsjAW6WW4w hvjm5UPA1KTwi3ZT8eT5uycuH7QpuNk7LnGGXQ6KaRALFywzkr0A0V0AvFNa+l4wstMaXaB6 dmMXyF0/hfryqyOeu1gAdq4q0/PNC2dPgb/6uoh4eMjN6A5GXcprilhz5DulmsilY21hfSoU S64ODed96pKhprsOxjZiDRsN1QM5MwB+uwVWUNfsX7fIxE0BrwARAQABzSdQZXRlciBFaXNl bnRyYXV0IDxwZXRlckBlaXNlbnRyYXV0Lm9yZz7CwXoEEwEIACQCGwMFCwkIBwMFFQoJCAsF FgIDAQACHgECF4AFAlUUuKQCGQEACgkQoZ8SQ+xBJp8llw//RbGoXnF72FZTlOFsnNyxnWbF vViPCfdAlZl1GGK9OYCvV1dp/956HuhB0wpaGQHiBmQfOPJf/jLOPjXFYm2iEChEycrBChuE j4TSYemMp5BSPILYk9Uwd16kVD/+U6fQfK6H5pOhfBvbM6asMnmpY2wQuvktxHvaGlytFw84 EzFWNZtAZfxkw28w3AhIQkKe9hJanfyDL750RcbyEr8AAHty5IErrvS7tM5vQwIeh5BF26ND CyKPRdZgOgMpcwFfROKBDpAt052CgWIpNdhBdfIKlkCs4/ILxmC3rT/Mb/qZiHcoMMwEu6k+ T7vRAFZZAk62Yrr/U8/vUGKlzRZQoo86fyojCfI0ibCIc0z8rwCVY9P40G9wD9BTCZS/z8Vh jjpBlNnIKw2hM6hbxavKndCJjFFJD08rXrTyrLF/of18xD37vNdYooP9VATB5QjOWFRrscSZ 4a89e+to3m7YVnd4xdXwGp47dYusoax84C8tg1gZrH38ol9dPmnKX6GZrXeS7tH9k8VeUpJ8 HQbRThQRWAT936QpYbLConBmOtmbLmjQOGtiXPo1oK57X/7U/WBja5vWmm2q+Yxtt+3uHGVg DdDU4CJpXo4lX0v2n3QV5EBSW9hjdF3bNfl6GFBb1pPEhqNcMDUc7zkx/2uENdAdk/BnqMgH umip7b0pVfvOwU0EVRS3qQEQAJf6p0P7D+hMYUgCPkvgrcz6OJYsAYdUMUmokfqmkKwK0oJM ew8TNGM53ocKAgQGma5f10rwbZNPlpVPNRX5hwUmwVlcwSut0oE+JfUBH7mpysWvpF05RKIy q9RCoJZrAbXS7Tig56A1QD8IcM00rERZMcCOlI1+q9m4u9AOI+0LyGGSH+VvoyDHD/Jew4R2 TmPEF8Pu7lYiNYK88Q6u0kBkc+Tz3BNlyc6fi3HxnFfp0b75HLXPSTu2SwA/0Q31wHZXwrEU BR9KgSn/qbgy8ydPc97i82fhiFE3cbGexfPrQSMYmDGi+dcQ0s+AMQwC3XyMC0JkHpYUuFkR QdlIvaIhgFUUN2Vr4xF2ADaNTNFc4DYaiPHFAgHZw+3pZWqS4uIiWgJynrEaHw4F3QWU8/JB iyEWDhNq4WBrRA0u914TCCAoFOOKMnRdl8eJrRQKM/5/lXbHFjjlI/LDKhLRFsuQsnJJ3331 RjCzpHPZG5/p5KR1RHiAXa0p/DW5lXyq21hS3kjDlleoHqOxCN/cy0Jgz7qLDq1vZoCl67Sy RAH60c2Xu4may9SVltmot0OzWVHdrfS+85FQGp9580DaeLio7YMFKy2bfEvwQJ/c9xD2QPB+ oSb3V8ueSznXBG5+dyfOsZFRPxceA0wA2rSWZSjxLdKxvyGTj9vsCjaDu13rABEBAAHCwV8E GAEIAAkFAlUUt6kCGwwACgkQoZ8SQ+xBJp+HJhAAxmSTsC5y7p9Zu+nnLpucv/h88oEx985e qWeXzQiwM3pfRZS+PdTH9TyzHK0KOgKoAiubk0vt+BrT91D9d0G5kVQaTctAErGBoMkTaZJT XVEcNphiPzysdSqvprjojMp1y7rG8AP+Xd6m0rE1LRnkRRm8HyI8vdXsuLQPITGWXDLMjaNq AOV3BUnzkgtjMuIYw7SCr+12ZOb5dEKvaG4kjC4kaJ/TLbuBhVANcXkC90yp0AOZLs/cB1Dj OAXno/RUUruJ3r27a+pFv768UAi9QWcdw58yPxreMNMLqAgsS9oTHwHfGNWcB2dJ4UW+GMLJ hTf9TjQ2f+N/sIx5XMQ9pYw6CAJMK29qPx6/ZayrPxf22EXf0D3zTwIEZgwfSkynFVwV25L7 awQq60fifdMYheSxq7GbhpsixmcD+e161cCmLIt75YDt5bQ0B1yf7EmxG8n7pGZAOM6LoNsU fkSldba5XVwh8LuN5mK0nQBELGW6Os4TDq4REggLpfcvFC215zpuo0iCGnmE/Bns4nowqUSP u4tkWL+nxW4qgv8wo8HJ52jd79EJ8iqfIBoidBfy9AIUiEUApGQzWPu6yWcI8fY0rN5D+tc+ Yw1Zt6Mm+huYJpgCRsTcfzEcahnBn521CWcWvhW1ff3KFileFMtNmnBBhMncmRKHphtTKEpN 3jI= Organization: 2ndQuadrant Message-ID: <63ef7f01-b460-e26c-84f7-5e13c66662ff@2ndquadrant.com> Date: Thu, 4 Apr 2019 13:01:09 +0200 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=windows-1252 Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 2019-04-04 11:42, Amit Langote wrote: > Hmm, I'm afraid we might get bug reports if we go with this. Why is it OK > to get null in this case when a user explicitly asked for 'foo'? The way stored generated columns work on foreign tables is that the to-be-stored value is computed, then given to the foreign table handler, which then has to store it, and then return it later when queried. However, since the backing store of a foreign table is typically modifiable directly by the user via other channels, it's possible to create situations where actually stored data does not satisfy the generation expression. That is the case here. I don't know of a principled way to prevent that. It's just one of the problems that can happen when you store data in a foreign table: You have very little control over what data actually ends up being stored. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services