Cannot delete entry linked to materialized view

Phoenix Forum Questions / Help
1

So I have this entry I want to delete:

Repo.get(Delegate, 761) |> Repo.delete()             
** (Postgrex.Error) ERROR 42809 (wrong_object_type) cannot change materialized view "certifications_delegates"
    (ecto_sql) lib/ecto/adapters/sql.ex:749: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql) lib/ecto/adapters/sql.ex:682: Ecto.Adapters.SQL.execute/5
    (ecto) lib/ecto/repo/schema.ex:511: anonymous fn/5 in Ecto.Repo.Schema.do_delete/4
    (elixir) lib/enum.ex:1940: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto) lib/ecto/repo/schema.ex:510: anonymous fn/10 in Ecto.Repo.Schema.do_delete/4
    (ecto) lib/ecto/repo/schema.ex:942: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
    (ecto_sql) lib/ecto/adapters/sql.ex:1005: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection) lib/db_connection.ex:1512: DBConnection.run_transaction/4

I indeed have a materialized view that builds a smart association between the Delegate object and the Certification one.

When I created it, I added:

        CREATE TRIGGER refresh_certifications_delegates_mat_view
        AFTER insert OR update OR delete OR truncate
        ON #{table_name} FOR EACH STATEMENT
        EXECUTE PROCEDURE refresh_certifications_delegates_mat_view();

on several association tables.

But in my case, I started by removing all associations, then I refreshed the materialized view (so there is no line that connects to the delegate#761), I can’t see how the trigger is called, and why it fails.

Any hint on this? Can’t I enforce something? I’m completely locked here (unless deleting/rebuilding the materialized view but it is not really a satisfying solution to me …)

I can give more details on objects if necessary, but tried to make the question as simple as possible.

Cheers!

2

I don’t believe you can perform DML (insert, update or delete) on a Postgres materialised view.

3

So I found a solution:

My materialized view was used as a many_to_many association with the following definition:

    many_to_many(
      :delegates,
      Delegate,
      join_through: "certifications_delegates",
      on_replace: :delete,
      on_delete: :delete_all
    )

So when I tried to do Repo.find(Delegate, 10) |> Repo.delete() it was trying to delete entries in the materialized view also, which doesn’t make sense. So I removed the last two lines (apparently setting on_delete: :nothing doesn’t work with my version of ecto 3.6.2) and it works now.

1 Like