support CockroachDB

[dbist]

I would like to test MartenDB with CockroachDB and I'm currently faced with the following error

CREATE OR REPLACE FUNCTION public.mt_immutable_timestamp(value text) RETURNS timestamp without time zone LANGUAGE sql IMMUTABLE AS
$function$
select value::timestamp

$function$;


CREATE OR REPLACE FUNCTION public.mt_immutable_timestamptz(value text) RETURNS timestamp with time zone LANGUAGE sql IMMUTABLE AS
$function$
select value::timestamptz

$function$;


CREATE OR REPLACE FUNCTION public.mt_grams_vector(text)
        RETURNS tsvector
        LANGUAGE plpgsql
        IMMUTABLE STRICT
AS $function$
BEGIN
        RETURN (SELECT array_to_string(mt_grams_array($1), ' ')::tsvector);
END
$function$;


CREATE OR REPLACE FUNCTION public.mt_grams_query(text)
        RETURNS tsquery
        LANGUAGE plpgsql
        IMMUTABLE STRICT
AS $function$
BEGIN
        RETURN (SELECT array_to_string(mt_grams_array($1), ' & ')::tsquery);
END
$function$;


CREATE OR REPLACE FUNCTION public.mt_grams_array(words text)
        RETURNS text[]
        LANGUAGE plpgsql
        IMMUTABLE STRICT
AS $function$
        DECLARE result text[];
        DECLARE word text;
        DECLARE clean_word text;
        BEGIN
                FOREACH word IN ARRAY string_to_array(words, ' ')
                LOOP
                     clean_word = regexp_replace(word, '[^a-zA-Z0-9]+', '','g');
                     FOR i IN 1 .. length(clean_word)
                     LOOP
                         result := result || quote_literal(substr(lower(clean_word), i, 1));
                         result := result || quote_literal(substr(lower(clean_word), i, 2));
                         result := result || quote_literal(substr(lower(clean_word), i, 3));
                     END LOOP;
                END LOOP;

                RETURN ARRAY(SELECT DISTINCT e FROM unnest(result) AS a(e) ORDER BY e);
        END;
$function$;

CockroachDB supports UDF and we support trigram indexes. I can attempt to migrate a PostgreSQL schema and avoid the migration step but I'm not sure we will hit a code path that will break when these functions are called. I have a repro in the following repo: https://github.com/dbist/cockroach-docker/tree/main/cockroach-martendb. It'd be great to understand the level of effort to create a weasel.cockroachdb adapter.

[jeremydmiller]

That's gonna be a healthy amount of work. I would say that it went much faster building out the SqlServer adapter after the Postgresql adapter from the amount of copy/paste you do for the tests.

Looking on the bright side, it's at least likely that Cockroach's metadata views will be easier to use than Postgresql's were:)