Elixir Ecto(Postgrex) compatability with Materialize 0.6.0

[jnatherley]

Firstly, i would like to say i really love this product. I'm looking into using Materialize to ingest websocket events.

I've tried to setup Materialize to work with Elixir Ecto which is using Postgrex, on startup Postgrex attempts to bootstrap by retrieving the postgres types:

• here
• here

The final bootstrap query for Postgres versions older than 9.2.0 looks like:

SELECT t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput,
           coalesce(d.typelem, t.typelem), coalesce(r.rngsubtype, 0), ARRAY (
      SELECT a.atttypid
      FROM pg_attribute AS a
      WHERE a.attrelid = t.typrelid AND a.attnum > 0 AND NOT a.attisdropped
      ORDER BY a.attnum
    )
    FROM pg_type AS t
    LEFT JOIN pg_type AS d ON t.typbasetype = d.oid
    LEFT JOIN pg_range AS r ON r.rngtypid = t.oid OR (t.typbasetype <> 0 AND r.rngtypid = t.typbasetype)
    WHERE (t.typrelid = 0)
        AND (t.typelem = 0 OR NOT EXISTS (SELECT 1 FROM pg_catalog.pg_type s WHERE s.typrelid != 0 AND s.oid = t.typelem))

Returns:

oid   typname typesend typereceive typoutput typinput coalesce coalesce array
16  bool    boolsend    boolrecv    boolout boolin  0   0   {}

In Materialize it fails at a few different points:

Query 1 ERROR: ERROR:  Expected left square bracket, found left parenthesis
LINE 2: ...ce(d.typelem, t.typelem), coalesce(r.rngsubtype, 0), ARRAY (
                                                                  ^

ERROR: ERROR:  column "t.typsend" does not exist
ERROR: ERROR:  column "t.typoutput" does not exist
ERROR: ERROR:  column "t.typinput" does not exist

Many PostgreSQL tools can be made to work with Materialize with minor changes to the pg_catalog compatibility shim.

I'm hoping with a few adjustments perhaps i can get this to work.

[JLDLaughlin]

Hi @jnatherley -- thank you for bringing this to our attention, and for your very detailed issue! We're glad to hear you've enjoyed working with Materialize so far. We are continuously adding to our pg_catalog support in order to broaden the ecosystem of tools that can be used with Materialize.

Right now, we're working on supporting a few other tools (dbt, for example). When engineering resources free up, we will be sure to triage and investigate the work needed to support Elixir Ecto and Postgrex. (And, we'll update this issue!)

[elindsey]

Glancing at this, it looks like the current list of missing features are:

pg_type.typsend
pg_type.typoutput
pg_type.typelem

And construction of arrays from subqueries (https://www.postgresql.org/docs/9.1/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS)