Automatically delete shape(s) when a table is dropped

[kevin-dp]

When a table is dropped from Postgres, users currently need to manually delete all shapes that are defined on that table. This is needed to avoid any problems that would occur when recreating the shape (e.g. with a different schema or different data) as Electric would still contain outdated data for that table potentially even in the old (invalid) format.

Currently, Electric is subscribed to a Postgres publication that streams changes (DML statements) about all tables involved in one or more shapes. However, Postgres does not stream DDL statements (such as DROP TABLE), which currently prevents Electric from detecting dropped tables and hence Electric can't automatically delete the corresponding shapes.

The goal of this issue is to discuss/propose ways of detecting tables being dropped in order to automate shape deletion.

@thruflo proposed a very neat approach of using an event trigger that calls a special "purge" function when a table is dropped. That special function would write a row into a meta table, which will come through the replication stream. We could check for this special row on the replication stream to detect a table being dropped right in time. The only downside here is that event triggers require superuser access and don't work in single-user mode (e.g. which is the case of PGlite iirc).