electric-sql
Automatically delete shape(s) when a table is dropped
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).
Yup, I think the permission issue makes event triggers a bit of a non starter.
I was thinking more that we can install a user defined function and users could choose to call it in the migration where they do their drop. Because you know when you do a big drop table and we can provide a function that makes it easy to notify Electric about it.
Can we compare table identifiers (i.e.: internal table identities, not the reusable name):
a) at initial sync query time? b) when rows come through logical replication?
I.e.: for (b) do we get a table ID in the row metadata? For (a) presumably we could validate the table still has the same internal identifier that we're expecting?
Now ... with (b) a dropped table won't yield any new rows. I don't know if there's something in this. Like could we detect a sequence having been reset somehow?
@thruflo When PG sends a Relation message on the stream it contains the relation's OID. So we could potentially detect any changes there, but.. PG only sends this relation if needed for a change it is going to stream. So, if you drop a table and recreate it, it would only send the relation when you insert a row in that new table. While that's not the case, we won't get the Relation message and we will still serve stale data. Also i doubt PG will send the relation message because i'm assuming that PG removes that table from the publication when the table is dropped (need to verify this).
We need a general solution for winding down shapes that aren't being updated and and eventually drop them.
It would be nice to be able to figure out those that we can stop maintaining immediately, but with the general solution is just a matter of time if they stop being updated.
and don't work in single-user mode (e.g. which is the case of PGlite iirc).
Just here to say that we have patched pg to make these work in PGlite https://github.com/electric-sql/pglite/issues/258
Also, while I agree that cleaning shapes up would mitigate this issue, and using the relation OID is already something I'm looking into to remove ShapeStatus persistence, we would still have the issue of the following case:
- Client creates a shape for table
public.userswith OID 1 - Electric now has a cache matching
public.usersto shape A - Client deletes
public.usersfrom the database and recreates it, so no we havepublic.userswith OID 2 - Client tries to create shape for table
public.users, but Electric sends them to the now basically inactive shape A 4a. New shape will only be created with the correct table once this one is collected
I get that this is "eventually" correct, but it feels like it might not provide a great experience and the cleaning up time will be configurable (some users might want to have long-lasting shapes).
How about having a sort of "keep-alive" ping from shapes to PG - if a shape doesn't get any updates for a short while, it schedules a ping to PG to see that the table is still alive and present.
We could have a service/process that batches these sorts of pings from all shapes in order to avoid unpredictable loads on Postgres - could be one query per 10 seconds or something - and cleans up any shapes that are no longer valid (table deleted or table exists but does not match the OID)
