Allow watching replicated tables

[frerich]

Postgres features a mechanism called 'logical replication' which makes it easy to have data replicated from one database to another. Alas, using EctoWatch on the target tables (i.e. the ones which will receive the replicated data) is not possible since triggers, by default, do not fire on replicated tables. The Postgres documentation at https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-DISABLE-ENABLE-TRIGGER explains:

The effect of this mechanism is that in the default configuration, triggers do not fire on replicas. This is useful because if a trigger is used on the origin to propagate data between tables, then the replication system will also replicate the propagated data; so the trigger should not fire a second time on the replica, because that would lead to duplication. However, if a trigger is used for another purpose such as creating external alerts, then it might be appropriate to set it to ENABLE ALWAYS so that it is also fired on replicas.

I'd love to try using EctoWatch on replicated tables. For this, it would be great if the library would either expose the names of the created triggers, such that I can manually execute ALTER TABLE commands -- or it would maybe support an additional option in the list of watcher (like role: :always with role: :origin being the default) such that EctoWatch issues ALTER TABLE commands itself.

[cheerfulstoic]

Wow, TIL!

Sure, that seems reasonable 🤔 When doing an EctoWatch.subscribe a GenServer.call needs to be made to the WatcherServer to lookup details based on the schema/label and the update type (inserted/updated/deleted). It would be pretty easy to have another function which makes a similar call to query the state of the server to get the trigger name (and maybe other information to reflect upon).

I can work on this shortly. Thanks! 👍

[cheerfulstoic]

FYI I made this PR to warn when triggers are left behind (because a watcher is added and then later removed, for example):

https://github.com/cheerfulstoic/ecto_watch/pull/20

As part of it I've implemented an EctoWatch.details/1 function which returns trigger/function/schema/etc... details for each watcher. It's based on #19, so it can come along after that (plus I need to add the mix task in the new PR which devs can run to actually do the cleanup)

[cheerfulstoic]

Released EctoWatch.details/1 function as 0.10.0

Examples:

EctoWatch.details({Comment, :updated})

# for labeled watchers:
EctoWatch.details(:comment_title_updated)