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 1rfcNV-001CeC-Hm for pgsql-general@arkaria.postgresql.org; Thu, 29 Feb 2024 09:06:58 +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 1rfcNU-001bxW-8a for pgsql-general@arkaria.postgresql.org; Thu, 29 Feb 2024 09:06:56 +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 1rfcKO-001Xpz-No for pgsql-general@lists.postgresql.org; Thu, 29 Feb 2024 09:03:45 +0000 Received: from mail-lf1-x135.google.com ([2a00:1450:4864:20::135]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rfcKN-0021Lt-0x for pgsql-general@lists.postgresql.org; Thu, 29 Feb 2024 09:03:44 +0000 Received: by mail-lf1-x135.google.com with SMTP id 2adb3069b0e04-512e39226efso544174e87.0 for ; Thu, 29 Feb 2024 01:03:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20230601.gappssmtp.com; s=20230601; t=1709197422; x=1709802222; darn=lists.postgresql.org; h=mime-version:user-agent:content-transfer-encoding:autocrypt :references:in-reply-to:date:cc:to:from:subject:message-id:from:to :cc:subject:date:message-id:reply-to; bh=NjvO3iL8jWAtNFuAVlgEkY1fqp69Sk1gcLbeQ/LVB14=; b=ho7s33Sx8e0cJ2UJjVT2zI6JW3WbskF53INIXadHj4GjTEiMxe8M+BomXnrNc4ikdN y7mxtjub8xPwJI8SWVXRAEm5GBeNDx5Fm9ZV3cP2yMFNlaPfttgb9VaIRVZozES5i59W rt0ok/E07wjdzpJ+ME94+D7iczKFgBBbuT1XFSEF/W9STZ9U0qIuLSY0fdVCc5TGcFGU O5FMUiUOvzi/4xWtOKtILFGrtzQ0o60pysBL2PBHkzYgctSNHo/NuiO6KDws3Z/diQFt BVW4qjSAW4x6v87BftNgu4iNUWI1sr5PnaxrhiUe051gqqNV6dtYzAdEH+rlvpJpsZPl kvNg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1709197422; x=1709802222; h=mime-version:user-agent:content-transfer-encoding:autocrypt :references:in-reply-to:date:cc:to:from:subject:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=NjvO3iL8jWAtNFuAVlgEkY1fqp69Sk1gcLbeQ/LVB14=; b=aYWlI42qukghDW8PtWIhprrZitijKTFH4LpVj/aP4U7ffPHuS6cm9ERKBOy7fiTqWU sfaLsuyAsITsis27l2nE5yUse/NEVqcFS9iLpk3YlXYdMHbTyG27lUawAHuCYqRkzkmY 1lXxjDHMRpKpyVpbaHMeTXGxjdalWUXR5l+hyGQFUvCwNPL6lbR88IrXieRQR/4WpU/R etvdl2C9Yh+AJyEGH4Wi18h20kKLr+LNfSWavgn6JYBZs1DaC1BpRHkoG8/2+geUynBe IpohIksPmvvbf369JAbmfyueI+UVwPiZR6A4LHplkrCR2EqgApyp3z8xDAdRjzA3o0UI 9Zog== X-Gm-Message-State: AOJu0Yzw/h7aK9r8/zM1FMdzGI7HqYaacH6XDNe9yCKFAuSr4CDJwq40 zGA8+wPW/n9Pe0IA+b/X+0FrUO7JGuyZBVT4XXfxBEtJ6jY5At8PGNRHv9+fWZU= X-Google-Smtp-Source: AGHT+IGdPJj44nnv7vSQ5Wnaz1kiPiE+OUmAehyv1LSCF7fv1uIFueXrCdU6GWzK+y1RPQzUNs7kzA== X-Received: by 2002:a05:6512:230c:b0:512:eb6a:5228 with SMTP id o12-20020a056512230c00b00512eb6a5228mr1109550lfu.41.1709197422447; Thu, 29 Feb 2024 01:03:42 -0800 (PST) Received: from localhost.localdomain ([2001:871:260:cf75:ce9a:f6a2:aa9c:105f]) by smtp.gmail.com with ESMTPSA id c13-20020a056000104d00b0033dfa7ecd33sm240822wrx.61.2024.02.29.01.03.41 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 29 Feb 2024 01:03:42 -0800 (PST) Message-ID: Subject: Re: Non-Stored Generated Columns From: Laurenz Albe To: Dominique Devienne , Tom Lane Cc: pgsql-general@lists.postgresql.org Date: Thu, 29 Feb 2024 10:03:41 +0100 In-Reply-To: References: <2425903.1709147504@sss.pgh.pa.us> Autocrypt: addr=laurenz.albe@cybertec.at; prefer-encrypt=mutual; keydata=mQINBGGDwAQBEADgbWy5cKXQld3N2mF+DFyiNFbi2oBl2T+XgxpPF8wTRw2D/u4bBKXP0SYSE/lA86jIVNWWU0gf1KODIkVvgJm2w4vH2VBV1b7ddVViGl1Iu+9zaRnv9wulhnH42KefepXnoean6UT1EzLM0opF/Ik0j+40TxdRtobkBprkQUyHDXWlHc2ffPs3SipyFEP9AVLf7ejRC46CXWDnsqjOBSMEW8Z4HiK/8RrPZBsKLts8dJxKF4pygOdJb0CWk8k/X1jbcfdxo+zOLjOMvJcSJ2pFdJmQHU+JufB3rePziqQ2S9Ur6sccr9XnTC1GVBWN4Lf5VHq+vf+bFJjVwg+2hrySZnAVfcOrxoqFLErr7ug1zN2nM1kcpgA4VWn4gxlJtYNYYq+9WxX5dtvnNANlG3ZCrRKQzl8lxtzoF6Zo7LUhEqPaHDwn7Rvs+IdbOn41lF5UDTJGqmC4gS/bZydW2Fy3YWm4aSaN9fgFf8D+PVkrlKAZB7gBLz1TyHjbcRf85cYF+GKKrDld5SzMB/V60VX3oP/Eo8ikFpyWaqiz1f9X7MBot3/PjJkY+wDzp3nmb19QEcOBuQiSQ4xds2r0HewbuHTAR68u8jNNMGmpm2j4x+g09Jd/WQDjqlTBZ/jEltH41fYCCPWMfljXTOOXu2eLNGdfi7ETZogtwjM9oTtSPQARAQABtCdMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5hdD6JAk4EEwEIADgWIQR0CqhbZGGABqoaSbdi8bhXA2EdmAUCYYPABAIbAwULCQgHAgYVCgkICwIEFgIDAQIeAQIXgAAKCRBi8bhXA2EdmM/6EADK232JCwmBzhlj8h7U9CjG6kx0JHP3uJGv+XfsHtHAlmY/RCwF1BHMEsRlk bT5UrLvJ2jb99bA9QARzhFaxzyn0F/BUKzuIjRGNs/n6d5dNUFA0kOt8sX+TacmC GEyjEBCrVCm4ranBiUyePn9NhHNWnaex7pJyqvMLLdwW9BEMJx0Fqo+DN8ukbXmYRsmhEtd3ue+x/luYmOmJnaGtzInaY5aOJYbW9XqoRIZkZvOCgbi1FfvNmoqWa+3oVxTOgw9RafjJDyW0lTHzKGjbGI5ofMU98l+/hKJFYJqWUF6VpFJY5YIcN/1lf4ZICMwDl+MPIVo/tpq8L10seJL28nLlvw3K+cI+TVW8IW/qL/LyVoDofI3USeOORuYmhpWRhik8JXX6xf3v6GrRilJIPWNFIJbxm1ZblQiQnOw3IOW7T+8nAmPin1HKqM3VrOrJQ2VtShsefNBibNAsr1oFaqcDBkn3yGG8i6CTW+FyO4PZ+/EwNxMVgktxbYdy5AT1/lpXr5tB+phhLIyVfiBvrWs5EThxYMQ/L8Y85c3GMsAy1l/x4h3jqySIYy3SCU9+jc5UVuNnXljbvkEzJ+NLWJ6C1rACFWrMszgPdh5tCrlRY9PpmYll4JbCgb8BtxEIUmR+xr50/ZElEK5iml7Q00KUekCcDt+36PsyGFTXBzNOrkCDQRhg8AEARAAzOZ2tLHlI4rrhG411h6cdCFjBZxuljaFCxFyHn3m6wbGLqwBUWC5k8UrRqjHMz88KcTSaNO7XGAmCqPdWd2SeflPZRnNTbjsVpw7mLdffsBm4JX7kki2Pvk5h0NtYeidXT1PSpc2ri4DutYXuT9uD8RAm1wUDCE5HQNUihT/WH6opt+hskHW21uHao0+y822tG0QQcGMqdQR5Vxdxj89wiEPdqW+HpU/oOZIhrf2E7prduAppxixjHy/o1rcnoznnJvc8D3+YgI9O0LrBMij89dM55pRGbLovTR1oGR3U74sX774+0xmSzeIKwZfiMUz7Atlvfk5SHOsRUFPN2Ux9kaXiiBibQpHFxt7b lDrT4wxdLJ/XCdbPPAyl+lZtOLsaHEEZvYNyTXwZc35dVf3R4/oz20HoG6s7ct8e1 AQygj43XAERzty9SkWgxs8+grp1PrGx6FHVSYRqBM8dS/ZR6yRVwOwJXPyaSSqfIF21DkE4j1y4n+ItSewPGoRp8K/yWCikt6qlkVkO2ASNIiX04fAbtzwVOaNn8ZMRNqyvLc1fED4sr49onE4cAIcBLjcC3KL+w9DUGRQCdziROj5H2Yl/sXGPdMciUHo/Uz2rggc+2th3bQiMhrHWSsBpUkDQp0yWewemstPpPgBL3h2fHKaX8B9oH5Qu/H1IgrOuX8AEQEAAYkCNgQYAQgAIBYhBHQKqFtkYYAGqhpJt2LxuFcDYR2YBQJhg8AEAhsMAAoJEGLxuFcDYR2YuPwQAMkpGtR80pQ1gVsONhdkqj0H2eU66efP/gO3CoyaoIcvrpKYj7C2HipVSmkt1gpByL0X4AMQ/vKuknUz3wd28Ba+G1dCfbVs/Xiusq+SmpUj5rTwmYqdSjWMuCo1R6oS5hdJMdUUJYGMT0QkVlm1KnW8jkmCTl9GzjDxOAsN9O6/6lPzaGFtk9XF+34Bry/N4HKiJkqpC4+UTd0AprPfzJ2jdT64e1F0+W88X8y1bTTgNrHwK4mDiLnlE4SKRuEm54lNhJz//ar86Or5BErzNpM6TL7lk44QS06hwsMrEdKIy8J/SYJPjfzR8tIUnKscclVpOgjKaBqC+0iFiVaRqAgfOlIEiezX6kMh5Q2FIUfqs46qWhhXjRrdKOEoStYAaikdLu5ZXr7vfb0ZaDh+ZwTQtbSMFolyOkecwI81MCdbMfT/1TqIGTOdAj5as9fAakk0jb2pXgUYQ8X1DVTR8ahSDVEaw9VTmWiSvTxvguVJ1Mb7gG4Gmh6aviDTJhfXtH4rPUNXhDLqrTH8JkJjyKROOMakIF68Hjse5vUfUxreBEOtb5r1Coa2Fe7ncJayaSE7ryrDbFqpZ 36UMAx4ulWMyqJajLNGY0DdG8qIsR5nxRhrnK/mrCidZ8F9/D3bWAl4rjtHlsztN59 +AnW5l0HsQcY9ntFL/zEBOaonjdJf Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.50.4 (3.50.4-1.fc39) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Thu, 2024-02-29 at 08:55 +0100, Dominique Devienne wrote: > On Wed, Feb 28, 2024 at 8:11=E2=80=AFPM Tom Lane wrot= e: > > Dominique Devienne writes: > > > Views can have foreign-keys? > >=20 > > Surely you'd put the FK on the underlying table. >=20 > Again, the FKs are on the *generated* columns. So=C2=A0 > =C2=A0 > > > Generated view columns be indexed? > >=20 > > [...[ it's hard to see much use-case here >=20 > The use-case is Polymorphic Foreign Key (aka PFKs). >=20 > For NULL'able PFKs, you have two read-write concrete columns, the ID > and the CODE (aka TYPE) of the relation. > Then you have 1 generated column (that I call XArc) per possible CODE/TYP= E > value (for that specific relation), which is either ID or NULL, depending > on the CODE. > And the "regular" FK is on that generated (and ideally Non-Stored, the > whole point of this thread), since it points to a single table now. > And since these FKs are CASCADE'ing, you want them INDEX'ed of course. I concede that that is a borderline use case. But you may as well have the foreign key columns as actual columns (or as stored generated columns). If that's a handful or so, it shouldn't be a big problem. If you have hundred types (hundred referenced tables), you'd end up with hundreds of indexes, and that already looks like a very bad idea (both DML and query planning time will be affected). Polymorphic Foreign Keys are nigh impossible to model well in SQL, and I doubt that non-stored generated columns will solve that. > For NOT NULL PFKs, that more of a PITA [...] See? > So has Ron says. If SQLite and Oracle has them, that's not an accident. Oracle has a lot of things that are not enviable... I am sure there are some use cases for "virtual" generated columns, and I don't think that a patch that tries to implement them will be rejected out of hand. It's just that I don't think it is a very important feature. > PS: Since we are on this subject, I'd like to take an opportunity to > ask a question I've had for a long time, but didn't have time to research= . > For a given PFK, all its XArc columns are mutually-exclusive (by design, > see above). So their indexes are full of NULLs. > Are the NULLs stored in these indexes, consuming extra space? In our use > case, we don't care about NULLs. > So should be use an expression index to explicitly not index the NULLs? > The sum of indexes for a given PFK should in theory be the same as a > single index, whatever the cardinality of the PFK, "logically". Yes, NULLs are stored in indexes, just like everything else. You could use conditional indexes, but then you have to make sure that the optimizer knows it can use these indexes. The sum of the sizes of these indexes shouldn't exceed the size of an unconditional index by much, but they would still be more expensive: each statement would have to look through all the indexes to decide which ones it can use and which ones not. Yours, Laurenz Albe