Domain type support

[gorbak25]

Hi!
In my app the postgres database is append only for everything, due to it we want to use UUIDv7 not UUIDv4.
I'm trying to enforce in the data model that everybody uses UUIDv7 not UUIDv4. I've created a domain type based on uuid for that:

-- <GORBAK_CUSTOM>
CREATE OR REPLACE FUNCTION get_uuid_version(_uuid uuid)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE STRICT LEAKPROOF
AS $$
  SELECT get_byte(uuid_send(_uuid), 6) & 240 >> 4;
$$;
CREATE DOMAIN uuidv7 AS uuid CHECK (get_uuid_version(VALUE) = 7);
-- </GORBAK_CUSTOM>

Unfortunately electric doesn't consider that a type might be a domain type and that it has a base type:

    Database error:
    ERROR: Cannot electrify \"public.\"DataTable\"\" because some of its columns have types not supported by Electric:
      \"id\" uuidv7

https://github.com/electric-sql/electric/blob/2b24fa273d9597a3e143f278d756e67221c1d848/components/electric/lib/electric/postgres/extension/functions/validate_table_column_types.sql.eex#L27C20-L27C29
I suggest replacing

    FOR _col_name, _col_type, _col_typmod, _col_type_pretty IN
        SELECT attname, typname, atttypmod, format_type(atttypid, atttypmod)
            FROM pg_attribute
            JOIN pg_type on atttypid = pg_type.oid
            WHERE attrelid = table_name::regclass AND attnum > 0 AND NOT attisdropped
            ORDER BY attnum
    LOOP

with something along the lines of

    FOR _col_name, _col_type, _col_typmod, _col_type_pretty IN
        SELECT attname, (CASE typtype = 'd' THEN (SELECT t1.typname from pg_type AS t1 WHERE t1.oid=pg_type.typbasetype) ELSE typname END), atttypmod, format_type(atttypid, atttypmod)
            FROM pg_attribute
            JOIN pg_type on atttypid = pg_type.oid
            WHERE attrelid = table_name::regclass AND attnum > 0 AND NOT attisdropped
            ORDER BY attnum
    LOOP

[icehaunter]

Hey, thanks for the suggestion!

Electric's scope and logic extend past the PG. What you're suggesting is very similar to, say, CHECK constraint on column. For that to work well, and keep the client and the server consistent, we need to make the both the client and Electric aware of these checks: the client so that non-malicious users have their write rejected locally and not on the server (since rejecting a write on the server is introducing tentativity or rollback logic, and that's not very local-friendly), and on Electric so that bad data doesn't get into Postgres replication stream.

For your suggestion to work, Electric itself (i.e. Elixir code) needs to be made aware of your domain function, and to be able to execute it on incoming user data. We're working towards that, but that still needs some time.

[balegas]

Turned this issue into a feature request

[KyleAMathews]

👋 we've been working the last month on a rebuild of the Electric server over at a temporary repo https://github.com/electric-sql/electric-next/

You can read more about why we made the decision at https://next.electric-sql.com/about

We're really excited about all the new possibilities the new server brings and we hope you'll check it out soon and give us your feedback.

We're now moving the temporary repo back here. As part of that migration we're closing all the old issues, PRs, and discussions. We really appreciate you taking the time to start this discussion! If it's still relevant in the new system, please reopen.