jetify-com / typeid-sql Goto Github PK

Hey there. I just added the three functions (typeid_generate, typeid_check, uuid_generate_v7) to the database and ran

select typeid_generate('test');

But was greeted with this error output:

Query 1 ERROR: ERROR:  returned record type does not match expected record type
DETAIL:  Returned type uuid does not match expected type text in column 1.
CONTEXT:  PL/pgSQL function typeid_generate(text) while casting return value to function's return type

Running select uuid_generate_v7(); works fine for what it's worth.

Not sure if there's a postgres version mismatch but we're on PostgreSQL 15.1 on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 10.3.0-1ubuntu1~20.04) 10.3.0, 64-bit

Thank you for this!

I was just curious whether I should just go with having the primary key as text or use the scripts to have a type_id in SQL? The text approach seems simpler, but wondering how it impacts Postgresql performance.

Also, the docs in the #Usage section put the type of the "id" field as user_id. Is it supposed to be type_id? Is that a typo?

When adding the equality operator in a supabase migration:

CREATE OR REPLACE FUNCTION compare_type_id_equality(lhs_id typeid, rhs_id VARCHAR)
    RETURNS BOOLEAN AS $$
SELECT lhs_id = typeid_parse(rhs_id);
$$ LANGUAGE SQL IMMUTABLE;

Applying migration as db reset is successful. However, all subsequent supabase db diff fails with the following error:

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedFunction: operator is only a shell: character varying = typeid
LINE 188:             case when p.data_type = 'USER-DEFINED' then
                                            ^

Should we compare the uuid part and the prefix part separately as strings?

After adding the optional operator overload, it seems impossible to select on the native uuid type. The following scenario occurs:

CREATE TABLE mytable (val UUID);

SELECT * FROM mytable WHERE val = 'c67a7925-e109-4abe-9d50-8c2a96a55483'::uuid;

Error:

typeid prefix must match the regular expression [a-z]{0,63}
CONTEXT:  PL/pgSQL function typeid_print(typeid) line 9 at RAISE

I suspect it's due to postgresql's operator type resolution, but I have no clue why typeid_print is being called.

Any ideas?

I'm curious if you have any examples of how you would query from the database using this setup:

https://github.com/jetpack-io/typeid-sql/blob/main/example/example.sql#L1

As I allude to here (jetify-com/typeid#1), I typically generate my IDs in my service layer instead of having my DB generate them.

One of the things I've been struggling with when using my type IDs is whether to insert as postgres' native UUID type - if I insert as the decoded UUID, this leads to a mismatch when e.g. my service's request logs contain the encoded/typed IDs, and then I have to decode them before, say, looking up the records in my DB. Does this custom type allow me to query postgres with the encoded string ID?

Has anyone managed to get typeid-sql working with rails/activerecord/postgres?

Despite hours and hours of trying, I cannot get past this error:

PG::UndefinedFunction: ERROR:  operator is only a shell: character varying = typeid
LINE 1: ...rnal_metadata" WHERE "ar_internal_metadata"."key" = $1 LIMIT...