Error interpolating `ts_vector` Ecto - is this a bug?

I’m trying to dynamically define the language I want for my PostgreSQL full text search:

def search(query, search_term, locale) do
        language =
          case locale do
            "pt" ->
              "portuguese"
            "es" ->
              "spanish"
            "fr" ->
              "french"
            "de" ->
              "german"
            _ ->
              "english"
          end
        (from u in query,
        where: fragment("(to_tsvector(?, unaccent(?)) || to_tsvector(?, unaccent(?)) || to_tsvector(coalesce(?, unaccent(?)))) @@ plainto_tsquery(?, unaccent(?))", 
                         ^language, u.date, ^language, u.title, ^language, u.text, ^language, ^search_term),
        order_by: fragment("ts_rank((to_tsvector(?, ?) || to_tsvector(?, ?) || to_tsvector(coalesce(?, ?))), plainto_tsquery(?, ?)) DESC", 
                        ^language, u.date, ^language, u.title, ^language, u.text, ^language, ^search_term))
        |> Repo.all()

end
But, I’m getting this error:

(RuntimeError) typeregconfigcan not be handled by the types module Ecto.Adapters.Postgres.TypeModule
The code works fine when I place the language directly, i.e.:

to_tsvector('portuguese', unaccent(?)) || ...
After trying to understand what this PostgreSQL type (regconfig) is and a few more tests I’m convinced that it could be due too the fact of using '' to define the language name instead of using "" but I’ve tried sigils and also explicitly passing the ^language type with no success, so I imagine that this must be some very Ecto specific issue.

The issue is that the first argument to to_tsvector is regconfig in PostgreSQL, but poxtgrex does not know how to encode it. You probably need an explicit cast there:

to_tsvector(?::text::regconfig, ?)
2 Likes

Yes, but even tying that I get another type error:
ERROR 42804 (datatype_mismatch): COALESCE types regconfig and text cannot be matched
Whatever I try, unless I set a language explicitly with ‘’ I get some type error.

I’m surprised postgrex would not support a native built-in postgresql type, sounds like a PR should be done…

This works for me on postgres 9.6:

ericmj=# SELECT 'english'::text::regconfig;
 regconfig
-----------
 english
(1 row)

PR’s are always appreciated. In this case I don’t think it would help because regconfig’s encodes as an OID and in this case a string was used.

1 Like

I’ve just upgraded Postgrex to the latest v0.13.3 and when running the code I posted I still get the same error (I’m using PostgreSQL 9.6.2.).
Note that if I use the language directly, i.e. ’ english'::text::regconfig it works.
But if I’m passing it as a variable, i.e. ?::text::regconfig it doesn’t.

Huh, what is the SQL it generates?

For example:

[debug] QUERY ERROR source="eventos" db=21.3ms
SELECT e0."id", e0."date", e0."text", e0."title", e0."date_pt", e0."text_pt", e0."title_pt", e0."date_es", e0."text_es", e0."title_es", e0."date_fr", e0."text_fr", e0."title_fr", e0."date_de", e0."text_de", e0."title_de", e0."imgPaths", e0."videoPath", e0."youTubePath", e0."inserted_at", e0."updated_at" FROM "eventos" AS e0 WHERE ((to_tsvector($1::text::regconfig, unaccent(e0."date")) || to_tsvector($2::text::regconfig, unaccent(e0."title")) || 
                        to_tsvector(coalesce($3::text::regconfig, unaccent(e0."text")))) @@ plainto_tsquery($4::text::regconfig, unaccent($5))) ORDER BY ts_rank((to_tsvector($6::text::regconfig, e0."date") || to_tsvector($7::text::regconfig, e0."title") || 
                          to_tsvector(coalesce($8::text::regconfig, e0."text"))), plainto_tsquery($9::text::regconfig, $10)) DESC ["english", "english", "english", "english", "car", "english", "english", "english", "english", "car"]

The problem is with your call to coalesce and not with ?::text::regconfig, as your error message says “ERROR 42804 (datatype_mismatch): COALESCE types regconfig and text cannot be matched”. The arguments to coalesce needs to be of the same type, you are giving it regconfig and text. Try to cast the result of coalesce to regconfig instead: coalesce($8, e0."text")::regconfig.

1 Like

You and Michael Muskala were both right and by combining your advices I got it:
1 ) [quote=“michalmuskala, post:2, topic:6815”]
The issue is that the first argument to to_tsvector is regconfig in PostgreSQL
[/quote]
2) [quote=“ericmj, post:9, topic:6815”]
The problem is with your call to coalesce and not with ?::text::regconfig
[/quote]

So I realised that, in one place, I was passing the language as the first coalesce argument when I should pass the language as the first to_tsvector argument and coalesce as the second to_tsvector argument.
Thank you both.

1 Like

For future reference, I added an example query to the related GitHub issue which shows how to use the ?::text::regconfig workaround when working with regconfig: