On Wed, Feb 28, 2024 at 8:11 PM Tom Lane <t...@sss.pgh.pa.us> wrote:
> Dominique Devienne <ddevie...@gmail.com> writes: > > Views can have foreign-keys? > > Surely you'd put the FK on the underlying table. > Again, the FKs are on the *generated* columns. So > > Generated view columns be indexed? > > [...[ it's hard to see much use-case here > The use-case is Polymorphic Foreign Key (aka PFKs). I've already outlined it on this list, in the past. 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/TYPE 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. For NOT NULL PFKs, that more of a PITA, because you cannot SET NULL a generated column (there's no INSTEAD OF trigger on generated columns). So instead we need a BEFORE INSERT/UPDATE trigger on the ID column, to dispatch to the proper per-TYPE-value column, which must now be concrete. And make sure those now-non-generated XArc columns are always in sync with ID/CODE. That's a PITA to implement, I really wish this was built-in to PostgreSQL (albeit non-standard SQL). Especially given that PostgreSQL has table inheritance, but which don't work with FKs. (in our case, some PFKs are not inheritance based, even though we do have inheritance in our logical models). But at least it's fully automated in our case, since the physical schemas are machine generated from logical ones. In one of our smaller schemas, 25 tables and 293 columns, we have 18 PFK virtual columns, i.e. 6% of the columns. So small, but not "that small". We have PFK cardinalities (number of XArcs) that can go up to 8, in that one small schema. When the cardinality goes too high, we explicitly choose to disable referential integrity for specific PFKs, sometimes. So has Ron says. If SQLite and Oracle has them, that's not an accident. And there's a real concrete use-case being it. Albeit an usual one. Any OO language writer (Java, C++, etc...) can recognize the polymorphism pattern (yes, often abused). And in our case, it's a long established pattern (20 years old) in our data models, transcribed to SQL and relational. When this was started with SQLite, it wasn't enforced at the relational model, but the mid-tier C++ level. But now that we move to PostgreSQL with direct SQL access (two tier), it must be PostgreSQL server-side enforced. So, to conclude, it works with PostgreSQL now. But it's more wasteful that it could/should be, because of the STORED only current limitation. Thanks, --DD 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".
