[BUG] PURGE empties or drops materialized views and user space tables #799

Describe the bug

PURGE CONSERVATIVE empties materialized views along with the provider cache, and a global PURGE drops the physical tables behind materialized views and user space tables while __iql__.materialized_views still lists the view. After that a SELECT from the view fails with no such table.

Materialized views and user space tables are user data, not cache, so I would expect purge to leave them alone. If dropping them is intended, the view metadata should go with them and the behaviour should be documented, because with a file backend this is the only garbage collection available between processes and it currently destroys the data the file was created to keep.

To Reproduce

No provider is needed.

$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "CREATE TABLE repos (repo text, enabled text)"
$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "INSERT INTO repos (repo, enabled) VALUES ('a', 'true'), ('b', 'false')"
$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "CREATE MATERIALIZED VIEW mv AS SELECT repo, enabled FROM repos"

$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "SELECT count(*) AS n FROM mv"
[{"n":"2"}]

$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "PURGE CONSERVATIVE"
[{"message":"PURGE of type 'conservative' successfully completed"}]

$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "SELECT count(*) AS n FROM mv"
[{"n":"0"}]

$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "PURGE"
Global PURGE successfully completed
[{"message":"purge 'GLOBAL' completed"}]

$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "SELECT count(*) AS n FROM mv"
sql packet preparation error: SQL logic error: no such table: mv (1)

State of the file afterwards: __iql__.materialized_views still has a row for mv, and neither mv nor repos exists in sqlite_master.

Expected behavior

After either purge, SELECT count(*) AS n FROM mv still returns 2 and repos still exists. Only provider cache tables (<provider>.<service>.<resource>.<schema>.generation_N) are collected.

Environment

• stackql v0.12.718 Windows (0dba3cf), Windows 11
• --sqlBackend '{"dsn":"file:bug.db"}'

Additional context

Related observation: with a file backend the provider cache tables grow by one batch of rows per process and are never collected between processes (collected_dttm stays null in __iql__.control.gc.txn_table_x_ref), so a long lived file needs purge, which then removes the views. The workaround I settled on is a fresh file per collection run and no purge at all.