Custom types

[mathemancer]

I've been thinking a lot about custom types (for this post, I'll use email as an example type). To quickly recap, we want custom types to:

1. Have a representation to the user that is "natural" ([email protected]),
2. Allow custom GROUP BY (for example, we could group emails by domain),
3. Have custom validators (we could use the HTML5 spec
4. Be available in the DB for users who want to interact using, e.g., psql

The natural solution would be to use PostgreSQL's Custom Base Type extensibility. This presents a number of problems:

• These need to be written in C, compiled, and make installed on the machine running the postgres server.
• This would be burdensome for many users.
• A number of managed PostgreSQL solutions don't even allow this (for example, AWS's RDS postgres).
• The process of writing and testing these might be difficult for the community (maybe, C is still a pretty popular/common language)
• This would seem to lock us quite strongly into PostgreSQL as a technology, since the custom base type creation is not a part of the SQL standard.

A less natural solution would be to try to cobble together similar functionality using Composite types, domains to check for input format (in our example, the domain could check that the email address fulfills some regex), and a system of Triggers to automatically parse the string input from a given column into the composite type, and store that in a separate column. This also has a number of downsides:

• Everything here will be kludgy, even with the simple email example (we'd need at a minimum:
  • A domain for any email text (input/output) column to enforce actual email addresses
  • An automatically-created email type column associated with the input/output column
  • A function that knows how to take emails apart into user, domain pairs
  • A trigger that runs that function on any insert/update of an email text column, and adds the result into the email type column.
  • Some way to associate any grouping on the email text column with a grouping on the appropriate email type sub-column

This second solution would be quite painful to create and maintain, and would be suboptimal for anyone who does have full control over their postgres install (or who's using our docker image). It would, however, be possible to implement completely through SQL scripts, and therefore should be maximally portable. It would at least be portable to any Postgres instance; other RDBMSes might not work as well: They should still support custom composite types (this is part of the SQL standard), and it should be possible to recreate the checks and auto-filling of columns using the available functionality in the other systems.

To me, it seems like this question comes down to what balance we want to strike between flexibility w.r.t. porting to other DBMSes and using managed solutions vs. "natural" implementation and probably a more maintainable codebase in the long run.

[pavish]

In my opinion, flexibility over "natural" implementation would be better, especially this early into development. Here are some of my thoughts on this:

• We may pivot from the current implementation/requirement later on. We never know the requirements we may deem important in the future. Having a Postgres lock-in would be a blocker when/if such a scenario occurs.
• Users who use Mathesar on the cloud would most probably go with managed Postgres instances, and as you mentioned most providers do not allow custom base type extensibility.
• Compiling and installing the custom base types whenever a user needs to run Mathesar, would make things harder for the user and for us i.e. the code base would not be as maintainable.
• Being flexible with composite data types make it easier for users who want to quickly setup Mathesar. This reduces complexity in setting up things for initial run, which most OSS suffer from.
• The number of users who query the db directly is going to be relatively small, compared to the ones using the UI. People who manage the db instances are going to be the ones doing it, and the chances of them modifying data directly is low. Even then, having a composite data type would hardly have an effect IMO, they would probably query the schema of the table and then query the tables accordingly.
• For all operations that happen through the UI, we have control over the behaviour i.e., when a user groups by domain in an email, we know that we have to group it by the type column. The only problem is when users query it directly in db and as I mentioned before, any tech-savvy user would be able to identify the schema and query the tables accordingly, and we could keep a detailed documentation for anyone who wishes to do so.
• As you mentioned, it is going to seem kludgy, especially the creation of a new column for handling this. But it would still be maintainable, as we decide the list of custom data types we're going to allow, and the operations needed on those datatypes. Also, we could even consider having only one column email which is a composite column instead of two different columns. We could have the structure as (full_email, user, domain).

[mathemancer]

These are some good points, thanks for that. I think I'm going to do some experimental implementations the "kludgy way", and see just how bad it is.

I did some more thinking this morning, and I'm starting to think that being more closely associated with PostgreSQL wouldn't be all bad. There's a strong community there, and it's consistently among the fastest-growing DBMS products (certainly if we restrict to RDBMSes): https://db-engines.com/en/ranking_trend . My thinking is that a really good "pack of types" extension with a big community around it would be a super valuable open source project on its own merits, with the potential to be eventually included in AWS's PostgreSQL installations and even the PostgreSQL default extensions. While committing to Postgres isn't our current strategy, it would have a number of benefits, both in implementation simplicity, probable performance, and community favor.

[kgodey]

I'll hold off on an opinion until @mathemancer posts more about the experimental implementation, but a couple of general points:

• I'm not sure that optimizing for multiple database backends from the start is worth it. We're aiming for this to be a plug-and-play product for non-technical users, and it doesn't seem like multiple database backends would be high on the priority list for those users. I do think we should modularize the PostgreSQL-related code as much as possible for readability/testability and future extensibility, but I don't think our fundamental architectural choices should depend on supporting multiple backends, and I'm fine with committing to Postgres as our current strategy.
• That being said, it would be ideal for someone to be able to deploy Mathesar on RDS without depending on a "pack of types" extension getting to maturity (although I do agree that it would be good open source infrastructure).

[jbruni]

My 2 cents here: in my ignorance about the project architecture and goals, I just want to say that there is room to provide some functionality which looks like "custom types" in simple ways.

For example, the user can provide a name and a regex (regular expression) to validate a given field.

And this is all.

From this point onwards, the user can use this new "type" called name, and have the input always validated by the provided regular expression.

I understand this does not match the design and the ongoing discussion here. But I wonder what would be the implementation strategy for such a simple use case within the realm of the ongoing discussion.

This naive suggestion and observation comes from the acknowledgement that the tool shall be "a breeze" to use by non-data-experts...

...and I believe the meaning of "custom type" within the current discussion does not necessarily match the meaning of "custom type" from the provided suggestion/example.

[mathemancer]

I've been trying different things, and I'm going to put notes about different ideas in different messages below to make threading a little more organized

[mathemancer]

Option: skip the extra types at the Postgres level, and just use JSONB

• Put things together for the user at the service level (e.g., so they can see the email as the desired string), and let anyone using the DB just figure out how to deal with the JSONB columns representing exotic types on their own.
• Handle validation at the service level as well; provide some functions to check and fix data which may have been added to a column of a given type erroneously.
• Pros:
  • Easy installation on the DB: There is none.
  • Great developer UI from the service level. We'd have a nice dot notation way to access parts of types for grouping, etc.
  • We get the grouping by behavior we want for many of the types for free, assuming we have the appropriate sub-parts separated out.
  • SQLAlchemy has a TypeDecorator to let us easily define functions for input/output
• Cons
  • We'll have to add some metadata to each column to let us pick up the service-layer type using reflection when we load a user's tables, or use a column model to store this info for each column.
  • Any validation or other functionality from these types won't be portable outside of our tool
  • This will be suboptimal on performance, since we'll give up automatic checking for required parts and so on on insert/update at the DB level.
  • We'll have to replicate much of what the DBMS would give us for free if we were using custom types on the DB level (casting, extracting parts of types, collating, uniqueness that makes sense, etc.)
  • Less powerful end-user experience, since we'll be more constrained by performance (likely).

[mathemancer]

Implement everything at the Postgres level for types

• Use the custom base types functionality of Postgres to implement at the DB level.
• Create full SQLAlchemy types to go along with them.
• Extend the TypeEngine in SQLAlchemy to implement SQLAlchemy functionality (may not be necessary if committed to just Postgres; we can just use string queries at that point).
• Pros:
  • performance
  • Everything will "just work" for the user once they've suffered through the installation
  • More likely to be useful to others in their own Postgres projects (as I've already mentioned, a well-made box-o-types extension for Postgres would probably see wide adoption).
  • More idiomatic use of both the RDBMS tools, and SQLAlchemy, once the types are set up; everything should "just work" for developers (except for implementing the types)
• Cons:
  • The types, and some input/output functions need to be implemented in C, adding another language to the project.
  • The types need to be installed as an extension (or through even lower-level means) that aren't currently possible on AWS RDS.
  • The types need to be implemented in SQLAlchemy, at least to the point where SQLAlchemy can see them as "real": If SQLAlchemy encounters a type it doesn't understand, it makes the column NullType. We don't really need any extra functionality, but it would be nice to be able to figure out the type so it can percolate up to the UI.

[mathemancer]

Use Postgres composite types combined with SQLAlchemy for input/output parsing

• This is a middle ground between the above options
• We'd use the composite types to enforce field existence, and give meaningful type names.
• We'd have SQLAlchemy check input for correctness, and split it into the pieces for the composite type using UserDefinedType or TypeDecorator.
• Pros:
  • A DB user would be able to see the types of the columns
  • We'd get a bit of help from the DBMS w.r.t. ensuring existence of required fields, indexing, etc.
  • Installation would be possible with just an SQL script
• Cons:
  • The definition of a type would be spread between the service and DB (the service would need to know what a valid email looks like to work with an email column. If what a valid email is changes for us, we'd need to modify both places). No source of truth on the type definition.
  • performance would be better than all SQLAlchemy, but not as good as all Postgres
  • The Postgres types would be useless by themselves (less modular)
  • The SQLAlchemy types would be useless by themselves (less modular)

This is my least favorite option

[mathemancer]

Use Postgres Domains, and skip types altogether

Postgres DOMAINs are essentially a way to declare a "type" that consists of nothing more than a check function on any input to a column of that "type".

• We could implement a DOMAIN for each desired type to ensure that input is in-line with what we want to handle. This would let us semi-safely write custom grouping, etc. functions with reasonable assumptions about input.
• Pros:
  • For the list of desired types (so far), this would get us 80% of the functionality for 1% of the effort.
• Cons:
  • SQLAlchemy doesn't support domains at all. In particular, reflecting them doesn't work at all; we'd need to store that info in a model, or in metadata on a column.
  • Performance on things like grouping will suffer, since we'd be doing string-slicing and so on to group. For example, to group by email domain (sorry for the overloaded word), the query might look like:

  SELECT SPLIT_PART("Teacher Email", '@', 2) AS domain, COUNT(*) FROM roster GROUP BY domain;

  • It's likely that we'll run up against some limitations in this approach (otherwise why would custom base types even exist?) once we want more sophisticated usage of type idiosyncrasies.

I think this might be the best short-term (I.e., MVP) option, though I really don't like the lack of support in SQLAlchemy.

[mathemancer]

It occurs to me that SQLAlchemy does reflect check functions for columns. This will be much (much) messier at the DB layer, but at least we'll get reflection. We could name the check function in some way that would help one understand the 'type' involved.

[pavish]

Domains seem good in my opinion, and I agree that this could probably be the fastest to implement and best short-term solution for the MVP. I would lean towards custom base types functionality if it weren't for the time constraint in getting it upto maturity.

Does this seem a good idea?
Is it possible to combine domains with composite type and have a composite domain type? The major drawback of domains as specified above is grouping performance. If we could split the data in the service layer and store it as a composite type, and have a domain constraint on the data, it would be more efficient.

Or, if it's not possible to declare a domain type over a composite type, we could still have the column as a composite type eg., email (full_email domain_email, user text, domain text), where full_email will belong to the domain_email type on which we have the constraints.

This way validation occurs at db level as well, but the service will still have to know what a valid email looks like, which is fine in my opinion. Whenever there's an API call, we would ideally run a validation on the request body before directly storing it in the db, so this should be okay I guess.

Just a thought to try out.

[mathemancer]

I was thinking of avoiding composite types for this way, SQLAlchemy doesn't really have support for them in an easy way, but I think we'll need them just to give the type a name so we can catch it with reflection. Here's an example of the DB layer implementation:

CREATE TYPE email_type AS (address text, username text, domain text);
CREATE DOMAIN email AS email_type
  CHECK (
    (value).address ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$'
    AND split_part((value).address, '@', 1) = (value).username
    AND split_part((value).address, '@', 2) = (value).domain
 );

This enforces a proper email (as per HTML5 def; see the link above) in the address field, and makes sure that it's split properly into username and domain fields. We can use SQLAlchemy's UserDefinedType to make it so that select from this type only shows the address field. INSERT and GROUP BY will be a bit painful, but should work. I'm not really happy about the amount of type info that'll have to be in the service, but I'm starting to think there's no escaping that.

Using this setup, one can create a table with an email column like so:

CREATE TABLE teacher_email (
        "__M_time_created" TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
        "__M_time_modified" TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
        "__M_id" SERIAL NOT NULL,
        email1 email,
        PRIMARY KEY ("__M_id")
);

[pavish]

I find this approach to be better than the rest, with respect to establishing a tradeoff between execution performance and maintainability (not considering custom types). Also composite types allow us to have primary keys, fk references and grouping.

However, if we are to move forward with this, I think we might have to run a benchmark just to compare the performance of insert, select and group by queries in particular, to having separate columns for address and domain (which is not maintainable but fast). This will give us more confidence with the approach if the benchmark returns good results.