Fixed querying tables with schema prefixes

[davidsulc]

Unless the table name is quoted, postgrex will fail with an undefined_table error

I have a prefixed schema: "structured.companies" (create table("structured.companies") do ..., schema "structured.companies" do ...).

Upon application startup, the current Honeydew code fails with:

08:29:13.753 [warn]  [Honeydew] Ecto queue ':collate' couldn't poll for jobs because 
%Postgrex.Error{connection_id: 9492, message: nil, postgres: %{code: :undefined_table, 
file: "parse_relation.c", line: "1159", message: "relation \"structured.companies\" does not exist", 
pg_code: "42P01", position: "8", routine: "parserOpenTable", severity: "ERROR", unknown: 
"ERROR"}, query: "UPDATE structured.companies\n      SET honeydew_collate_lock = 
(CAST(EXTRACT(epoch from NOW()) * 1000 AS BIGINT)) + 300000\n      WHERE ROW(id) = (\n   
     SELECT id\n        FROM structured.companies\n        WHERE honeydew_collate_lock 
BETWEEN 0 AND (CAST(EXTRACT(epoch from NOW()) * 1000 AS BIGINT)) - 
(CAST(EXTRACT(epoch from timestamp '1994-03-26 04:20:00') * 1000 AS BIGINT)) \n        
ORDER BY honeydew_collate_lock\n        LIMIT 1\n        FOR UPDATE SKIP LOCKED\n      )\n  
    RETURNING honeydew_collate_private, id"}

Reproduction in iex:

iex> q = "UPDATE structured.companies SET honeydew_collate_lock = (CAST(EXTRACT(epoch 
from NOW()) * 1000 AS BIGINT)) + 300000 WHERE ROW(id) = (\n        SELECT id\n        FROM 
structured.companies WHERE honeydew_collate_lock BETWEEN 0 AND (CAST(EXTRACT(epoch 
from NOW()) * 1000 AS BIGINT)) - (CAST(EXTRACT(epoch from timestamp '1994-03-26 04:20:00') * 
1000 AS BIGINT)) ORDER BY honeydew_collate_lock LIMIT 1 FOR UPDATE SKIP LOCKED ) 
RETURNING honeydew_collate_private, id"

iex> Ecto.Adapters.SQL.query!(Repo, q)

** (Postgrex.Error) ERROR 42P01 (undefined_table) relation "structured.companies" does not exist

    query: UPDATE structured.companies SET honeydew_collate_lock = (CAST(EXTRACT(epoch 
from NOW()) * 1000 AS BIGINT)) + 300000 WHERE ROW(id) = (
        SELECT id 
        FROM structured.companies WHERE honeydew_collate_lock BETWEEN 0 AND 
(CAST(EXTRACT(epoch from NOW()) * 1000 AS BIGINT)) - (CAST(EXTRACT(epoch from 
timestamp '1994-03-26 04:20:00') * 1000 AS BIGINT)) ORDER BY honeydew_collate_lock LIMIT 1 
FOR UPDATE SKIP LOCKED ) RETURNING honeydew_collate_private, id
    (ecto_sql) lib/ecto/adapters/sql.ex:621: Ecto.Adapters.SQL.raise_sql_call_error/1

Same query, with quoted table name:

iex> q_fixed = "UPDATE \"structured.companies\" SET honeydew_collate_lock = 
(CAST(EXTRACT(epoch from NOW()) * 1000 AS BIGINT)) + 300000 WHERE ROW(id) = (\n   
     SELECT id\n        FROM \"structured.companies\" WHERE honeydew_collate_lock BETWEEN 0 
AND (CAST(EXTRACT(epoch from NOW()) * 1000 AS BIGINT)) - (CAST(EXTRACT(epoch from 
timestamp '1994-03-26 04:20:00') * 1000 AS BIGINT)) ORDER BY honeydew_collate_lock LIMIT 1 
FOR UPDATE SKIP LOCKED ) RETURNING honeydew_collate_private, id"

iex> Ecto.Adapters.SQL.query!(Repo, q_fixed)

%Postgrex.Result{
  columns: ["honeydew_collate_private", "id"],
  command: :update,
  connection_id: 11121,
  messages: [],
  num_rows: 1,
  rows: [[nil, 1]]
}

Testing

I'm not sure how to proceed to add a regression test for this (assuming this is indeed a proper fix).

Running the current test suite yields 5 failures, but those failures are also present without the above code change (i.e. they don't seem related to the patch).

[koudelka]

Hey @davidsulc,

I believe the proper way to use a schema prefix is to use the @schema_prefix attribute in your schema/2 definition.

I suspect the way you're doing it now is creating a table with a literal "dot" in its name.

Happy to dig in more if I'm mistaken. :)

[davidsulc]

Apologies: I was indeed mistaken in how to handle Postgres schemas via Ecto (still not quite sure how I missed that...), and am closing this. Thanks for your work on Honeydew!

[koudelka]

hey no worries, feel free to open a new issue if you run into anything else, happy to help. :)