Repository: elixir-ecto/ecto_sql
Pull request #530 (2023-06-22 15:37:17)

Implements hints for Postgresql
### Overview

Implements FROM statement hints for postgresql

### Use-case

This is useful for `TABLESAMPLE ` hint in postgresql
https://www.postgresql.org/docs/current/sql-select.html#SQL-FROM

For example:
```elixir
from x in Item, hints: ["TABLESAMPLE system_rows(1)"]
```

Fixes well-known problem with getting random rows from the table using Ecto
https://elixirforum.com/t/ecto-add-raw-sql-at-end-of-query-or-rather-choose-row-at-random/3386

---

If this change requires any tests, I am happy to implement them, just show me how.

josevalim: You can write a unit test at test/ecto/adapters/postgres_test.exs that test we generate the proper SQL. :)
hissssst: @josevalim done
v0idpwn: I like the idea of supporting TABLESAMPLE, but this isn't a hint...
greg-rychlewski: It feels a bit off to me too because Postgres pretty famously doesn't use query hints https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion.

The only alternative I think is to add a new option like `:sample`. I think any generic option to add text behind `FROM` would conflict with the hints in the other databases. Or we just accept the minor abuse of the term `hint`. 
hissssst: @v0idpwn @greg-rychlewski other adapters here implement samples through this hint system. It is even in the documentation

https://hexdocs.pm/ecto/Ecto.Query.html#join/5-hints
greg-rychlewski: Ah ok, then I guess that's that :). 

Patch:
diff --git a/lib/ecto/adapters/postgres/connection.ex b/lib/ecto/adapters/postgres/connection.ex
index 954fdbb..4d76f61 100644
--- a/lib/ecto/adapters/postgres/connection.ex
+++ b/lib/ecto/adapters/postgres/connection.ex
@@ -392,13 +392,18 @@ if Code.ensure_loaded?(Postgrex) do
        exprs}
     end
 
-    defp from(%{from: %{hints: [_ | _]}} = query, _sources) do
-      error!(query, "table hints are not supported by PostgreSQL")
+    defp from(%{from: %{source: source, hints: hints}} = query, sources) do
+      {from, name} = get_source(query, sources, 0, source)
+      [" FROM ", from, " AS ", name | hints(hints)]
     end
 
-    defp from(%{from: %{source: source}} = query, sources) do
-      {from, name} = get_source(query, sources, 0, source)
-      [" FROM ", from, " AS " | name]
+    defp hints([]), do: []
+    defp hints(hint) when is_binary(hint), do: [?\s | hint]
+    defp hints([{key, value} | tail]) do
+      [?\s, to_string(key), ?\s, to_string(value) | hints(tail)]
+    end
+    defp hints([hint | tail]) do
+      [?\s, hint | hints(tail)]
     end
 
     defp cte(%{with_ctes: %WithExpr{recursive: recursive, queries: [_ | _] = queries}} = query, sources) do


Test patch:
diff --git a/test/ecto/adapters/postgres_test.exs b/test/ecto/adapters/postgres_test.exs
index 0509aed..f880dec 100644
--- a/test/ecto/adapters/postgres_test.exs
+++ b/test/ecto/adapters/postgres_test.exs
@@ -74,10 +74,35 @@ defmodule Ecto.Adapters.PostgresTest do
     assert all(query) == ~s{SELECT s0."x" FROM "schema" AS s0}
   end
 
-  test "from with hints" do
-    assert_raise Ecto.QueryError, ~r/table hints are not supported by PostgreSQL/, fn ->
-      Schema |> from(hints: "USE INDEX FOO") |> select([r], r.x) |> plan() |> all()
-    end
+  test "from with hints list" do
+    query =
+      Schema
+      |> from(hints: ["TABLESAMPLE system_rows(1)"])
+      |> select([r], r.x)
+      |> plan()
+
+    assert all(query) == ~s{SELECT s0."x" FROM "schema" AS s0 TABLESAMPLE system_rows(1)}
+  end
+
+  test "from with hints string" do
+    query =
+      Schema
+      |> from(hints: "TABLESAMPLE system_rows(1)")
+      |> select([r], r.x)
+      |> plan()
+
+    assert all(query) == ~s{SELECT s0."x" FROM "schema" AS s0 TABLESAMPLE system_rows(1)}
+  end
+
+  test "from with hints list of tuples" do
+    one = 1
+    query =
+      Schema
+      |> from(hints: ["TABLESAMPLE SYSTEM": one])
+      |> select([r], r.x)
+      |> plan()
+
+    assert all(query) == ~s{SELECT s0."x" FROM "schema" AS s0 TABLESAMPLE SYSTEM 1}
   end
 
   test "from without schema" do