Mutable transactions should be downgradable

[joshua-spacetime]

This is specifically related to subscriptions.

Subscriptions are very similar to materialized views (matviews).

Like matviews, they need access to the database, because they depend on both the row updates of a transaction as well as the committed state of the database.

Like matviews, they need to be observably atomic. That is, both the original transaction and the subscription queries need to be executed as a single unit.

Currently we evaluate the original transaction and the subscription queries in two different transactions, but we use an external lock to ensure atomicity. This works, but relies on the database being single-threaded which will not always be the case.

This means that eventually subscription queries will need to be run within the same transaction that produced the row updates.

If they're run in the same transaction, does that mean the entire transaction is aborted if a query fails?

For matviews the answer is yes, because otherwise it would end up in an inconsistent state.

Now we don't support matviews at the moment, so we don't have to abort the transaction in the case of a query failure, so long as we drop the associated connection(s).

Why can't they be run in the same transaction now?

For implementation and performance reasons.

We have two types of transactions - mutable and read-only. Mutable transactions hold an exclusive lock on the database, whereas read-only transactions hold a shared lock. So while the original transaction is mutable, we execute the subscription queries in a read-only transaction which allows for parallel query execution.

Do we need MVCC for this?

No. If we could atomically downgrade a mutable transaction, we could execute everything in a single transaction while maintaining the current database and query architecture.

[Centril]

Relevant to #1094.