Remove meta-id-based foreign keys from views?

[erichanson]

Right now, each meta view (for example, meta.column) has an id column (e.g. meta.column.id of type meta.column_id) that simulates a "primary key". Views don't have keys, but the id field can essentially be used like a primary key, it's guaranteed to be unique, etc.

Each meta view also has two types of fields that overlap with the values in the id field, a set of named columns (e.g. for meta.column, relation_name and schema_name) and also identifier-based columns that simulate "foreign keys", (e.g. for meta.column, relation_id of type meta.relation_id).

The idea here is to make these views usable like traditional normalized views, so you can run a query like

select c.* from meta.column c
join meta.relation r on c.relation_id = r.id
join meta.schema s on r.schema_id = s.id;

^^^ A typical simple join statement.

Then, in the insert triggers on the views, there's two ways to insert, either by name:

insert into meta.column(schema_name, relation_name, name, type)
values ('public','ninjas','throwing_star_skill_level', 'decimal');

OR by id:

insert into meta.column(relation_id, name, type)
values (meta.relation_id('public','ninjas'),'throwing_star_skill_level', 'decimal');

This I guess is kind of cool, but we really almost never use the insert/update-by-id pattern, nor the join by id patterns. It begs the question, whether or not this complexity is really worth it. If we switched to only names (or even if we don't) you can still do a pretty nice join across meta views using down-casts, like:

select c.* from meta.column c
join meta.relation r on (c.id)::meta.relation_id = r.id
join meta.schema s on (c.id)::meta.schema_id = s.id;

Or you could still do named joins, like:

select * from meta.column c
join meta.relation r on c.relation_name = r.name
join meta.schema s on r.schema_name = s.name

Both are fine, the second one is the most beautiful.

Maybe it would be cleaner and simpler and more obvious and less magical if we just switched to name columns, and drop the meta-style foreign keys entirely. It would simplify the triggers a lot (you could require exactly the named columns, not this require-one or require-all logic that exists right now), and I don't know that the meta-id-based fks are really providing much value.