When finished, fix bundle.not_ignored_row_stmt to use it.

Privilege coverage for relations in meta is quite good. Copy this pattern for meta.function_privilege and meta.schema_privilege.

Add rls_enabled boolean on the view meta.table, to enable row-level security on that table.

Create a table called meta.ignored_schema with a schema_id column. Whatever schemas are in this column are ignored in meta views. Might be useful for, say, ignoring pg_catalog and INFORMATION_SCHEMA.

Right now an insert into meta.table creates the "normal" pk/fk indexes that are automatically created in a create table statement. Stop doing this, and instead make a meta.index view that one needs to insert into to create those (or any other) indexes. This way we can track and version control indexes like everything else.

When trying to create a rls policy on a table whose name is a quoted identifier (e.g. endpoint."user"), the cast from oid to meta.relation_id parses the name as {'endpoint', '"user"'} and breaks the meta.policy view

Found pgddl, the DDL eXtractor. PostgreSQL doesn't yet provide full coverage of getting the definition of every pg entity. Meta has a half-baked attempt and including definitions in the system catalog views. Give this a try instead!

It is unspeakably slow. Eradicate the scourge.

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.

@davidfetter Hey I'm going to split out meta's update triggers into a separate extension, maybe call it meta_updaters. I like it, developers could decide whether or not they want to install such a giant foot-gun. :)

Anything else we need to do?

Would it be possible to add a column for the file system location of the, say, hstore.control file that was used to install the extension?

Creating a bundle throws a 500 because:

{
  "status_code": 500,
  "title": "Server Error",
  "message": {
    "state": "42725",
    "message": "function quote_literal(\"char\") is not unique",
    "detail": "",
    "context": "PL/pgSQL function meta.get_typedef_base(oid) line 5 at SQL statement\nPL/pgSQL function meta.get_typedef(oid) line 10 at RETURN\nPL/pgSQL assignment \"q := '\n            with inserted_row as (\n                insert into ' || quote_ident(_schema_name) || '.' || quote_ident(_relation_name) ||\n                case when args::text = '{}'::text then\n                    ' default values '\n                else\n                    ' (' || (\n                        select string_agg(quote_ident(json_object_keys), ',' order by json_object_keys)\n                        from json_object_keys(args)\n\n                    ) || ') values (' || (\n\n\n\n                           select string_agg('\n                                   case when json_typeof($3->' || quote_literal(json_object_keys) || ') = ''array'' then ((\n                                            select ''{'' || string_agg(value::text, '', '') || ''}''\n                                            from json_array_elements(($3->>' || quote_literal(json_object_keys) || ')::json)\n                                        ))\n                                        when json_typeof($3->' || quote_literal(json_object_keys) || ') = ''object'' then\n                                            ($3->' || quote_literal(json_object_keys) || ')::text\n                                        else ($3->>' || quote_literal(json_object_keys) || ')::text\n                                   end::' || case when json_typeof((args->json_object_keys)) = 'object' then 'json::'\n                                                  else ''\n                                             end || c.type_name, ',\n                                   '\n                                   order by json_object_keys\n                           ) from json_object_keys(args)\n                           inner join meta.relation_column c\n                                   on c.schema_name = _schema_name and\n                                      c.relation_name = _relation_name and\n                                      c.name = json_object_keys\n                           left join meta.type t on c.type_id = t.id\n\n\n\n                    ) || ') '\n                end ||\n                'returning *\n            )\n            select (''{\n                \"columns\": ' || endpoint.columns_json(_schema_name, _relation_name, null::text[], null::text[]) || ',\n                \"pk\":\"' || coalesce(endpoint.pk_name(_schema_name, _relation_name), 'null') || '\",\n                \"result\": [{ \"row\": '' || row_to_json(inserted_row.*, true) || '' }]\n            }'')::json\n            from inserted_row\n        '\"\nPL/pgSQL function row_insert(relation_id,json) line 20 at assignment\nSQL statement \"select 200, 'OK'::text, (select endpoint.row_insert(relation_id, post_data))::text, 'application/json'::text\"\nPL/pgSQL function endpoint.request(text,text,text,json,json) line 134 at RETURN QUERY",
    "sqlerr": "function quote_literal(\"char\") is not unique",
    "sqlstate": "42725"
  }
}

aquameta=# select * from meta.function where name='quote_literal';
-[ RECORD 1 ]--+-----------------------------------------------------
id             | ("(pg_catalog)",quote_literal,{anyelement})
schema_id      | (pg_catalog)
schema_name    | pg_catalog
name           | quote_literal
parameters     | {anyelement}
definition     | select pg_catalog.quote_literal($1::pg_catalog.text)
return_type    | text
return_type_id | ("(pg_catalog)",text)
language       | sql
returns_set    | f
-[ RECORD 2 ]--+-----------------------------------------------------
id             | ("(pg_catalog)",quote_literal,{text})
schema_id      | (pg_catalog)
schema_name    | pg_catalog
name           | quote_literal
parameters     | {text}
definition     | quote_literal
return_type    | text
return_type_id | ("(pg_catalog)",text)
language       | internal
returns_set    | f

Functions with no parameters (e.g. triggers like meta.schema_delete) are not showing up in meta.function. Tests are failing.

PostgreSQL 14 introduced multirange types, and our meta.get_typedef(oid) functions do not support them. Add support.

Not everything in meta.relation has a primary key, because views, foreign tables, etc. do not have "real" primary keys. Remove meta.relation.primary_key_column_ids, and put it on meta.table instead. While we're at it, rename meta.column.relation_id to meta.column.table_id.

https://wiki.postgresql.org/wiki/Count_estimate

How expensive is this? I believe this is just a cached value? If it's fast enough, add it to meta.table.

The meta identifier type system has been a source of great joy and also much tears. They very nicely encapsulate the identifier of a database object (which can be in some cases four or five separate values) in a single value. This encapsulation makes life easier in many, many scenarios. Overall the concept of meta identifiers is a huge win. HOWEVER. The implementation has a number of problems:

1. They are highly composite

schema_id has just a name value.
relation_id has a relation name value and a schema_id.
column_id has a column name value and a relation_id.
row_id has a primary key column_id, and a primary key value pk_value.
field_id has a row_id and a column_id

The nesting here is hopefully obvious.

While correct in terms of information architecture, five-level-deep composite types are patently ridiculous to work with.

Creating one (without using the handy constructors) looks like:

row(row(row(value->'relation_id'->'schema_id'->>'name'), value->'relation_id'->>'name'), row(value->'role_id'->>'name'), value->>'type')::meta.table_privilege_id

Accessing some variable (without using a handy cast function) looks like:

schema_name := (((column_id).relation_id).schema_id).name

2. Their internal representation is awful

aquameta=# select meta.field_id('bundle','bundle','id','1b5bf8c5-c93c-48f5-81a8-bd39277ecde6','name');
                                                               field_id                                                               
--------------------------------------------------------------------------------------------------------------------------------------
 ("(""(""""(""""""""(bundle)"""""""",bundle)"""",id)"",1b5bf8c5-c93c-48f5-81a8-bd39277ecde6)","(""(""""(bundle)"""",bundle)"",name)")
(1 row)

Unfortunately we cannot override this behavior, because in postgres, composite types do not have input or output functions, but all share a single global (and dumb) function.

3. They cause havoc in datum.js

So, when endpoint's REST API selects rows, it checks to see if that row's type is JSON or a composite type, and if it is, sends the row over the wire as a JSON object. When pushing the object back to the database, ... really not sure what happens, but I doubt it works. Basically the REST API has never worked properly with composite types, which has been a massive bottleneck in terms of UI design of the schema admin, bundle admin, user admin, and more. They don't work, and the obtuseness of their structure makes designing a sane REST API to handle them...formidable.

We need to redesign them entirely, to be flat, non-composite types.

Functionality to retain

These patterns of usage should continue to work:

1. Constructors

select meta.field_id('a','b','c','d');

2. Casts from one type to another

select field_id::meta.schema_id;

3. Casts to/from JSON

select meta.field_id::json

Improvements

1. Easily accessible (flat) value namespace

select field_id.schema_name;

2. Casts to/from text

We already have casts to text for some types, however not full coverage. Text representation should be readable, unique and unambiguous. We need to make use of quote_ident and quote_literal in our casting, and test against identifiers that contain quotes, slashes, etc.

aquameta=# select meta.field_id('bundle','bundle','id','1b5bf8c5-c93c-48f5-81a8-bd39277ecde6','name')::text;
                        field_id                         
---------------------------------------------------------
 bundle/bundle/1b5bf8c5-c93c-48f5-81a8-bd39277ecde6/name
(1 row)

Desired Outcomes

1. Support for CRUD on meta.* from datum.js
2. Version control on meta.* from bundle

These are the two biggest objectives still ahead, and this is the source of all the problems.

Implementation Ramifications

This is a central pillar in the architecture, a high-flying buttress. Pulling it out will be highly impactful throughout the system. Test coverage will preserve functionality in bundle and meta, presuming tests don't rely too heavily on internals and instead use constructors and casts. Need a endpoint test suite.

Implementation Approach

The constructors for the types should be a pretty good indicator of how the flattened types should be structured. Do that.

We need to figure out what bundle is going to store in blob.value for, say, a meta.column_id, be it the text representation or ... whatever postgresql outputs for non-composite types...? Since these types won't be composite anymore, we have hope of writing input/output functions that do the right thing without the incessant explicit casting.

PostgreSQL supports COMMENTs on database objects like schema, table, column, etc. Add these to the meta views where applicable, so we can easily make comments on things. Could be a good way to do documentation.