On Thu, 2024-02-29 at 08:55 +0100, Dominique Devienne wrote: > On Wed, Feb 28, 2024 at 8:11 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > Dominique Devienne <ddevienne@xxxxxxxxx> 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). > > 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. 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
