[docdb] Geo-partitioning support for the transaction status table

[mbautin]

Jira Link: [DB-359](https://yugabyte.atlassian.net/browse/DB-359)

Phase 1 -- Done

Proof of concept for this work. User should be able to create txn tables manually, control local vs global behavior via a session variable and get appropriate errors when trying to do global work against local transaction tables.

✅ Ability to create new transaction tables with custom placement (#10157)
✅ Ability for a TS to use a local vs global transaction table (#10158)
✅ Session variable to control local vs global transaction table usage (#10159)

Phase 2 -- Done

Improve usability, by automatically creating tables for the user, by piggybacking on tablespaces, as the means to configure this feature in YSQL. New, local txn tables, will be created on the first table added to a tablespace, either via CREATE TABLE or via ALTER TABLE SET TABLESPACE.

✅ Create txn table on first table in a tablespace (#10783)
✅ Create txn table on first use of ALTER TABLE SET TABLESPACE (#11353)

Phase 3 -- 2.13

Further improve usability by also removing the local vs global error for the user, allowing them to start in a local context and be able to execute global queries, without receiving an error, by transparently promoting their transaction to a global one.

✅ Automatically promote from local to global transactions when necessary (#10537)

Future work

⬜️ Support for ALTER TABLESPACE (#11075)
✅ Support for deleting local txn tables (#11123)

[mbautin]

From @bmatican:

As I understand it, the objective here would be to be able to guarantee low latency, region-local, reads & writes, for users of geo partition.
An example to illustrate that
Multi-region cluster: 9 node cluster, RF3, spread across 3 regions A/B/C (that might be far apart), with each region having 3 nodes — so each region has 1 copy of the data.
Geo partition setup for a table: each of the 3 regions gets one partition, with all 3 copies of their data being fully contained in that region, eg: table_A / table_B / table_C.
However, the transactions table would inherit the cluster settings, so it would be 3-way replicated, across regions!
This means that we cannot guarantee that writes to say table_A only, that otherwise would only need to touch data in region A, will have region-local latency, because we’ll still need to make writes to the transactions table (eg: txn commit), which need global x-region quorum…
On the functional/usage side: One thing that is unclear, is how would we want to tie together the placement policies for the transactions table, to the ad-hoc tablespaces that a user might setup — which could reference regions that don’t exist yet, or might have different settings across different tables, etc? This feels relevant both to the lower level docdb discussion, but also higher level.
On the design side: Assuming that we had a fixed placement that we know the transactions table would have to respect, somehow, is this a problem we’ve already discussed in some form? What are some ways in which we might approach this?

From the docdb side, aside from understanding how we expect this to be used, I’d then like to figure out if we need some high level, potentially complicated, architectural changes, like figuring out how create sub-tables for the transaction table — I believe that was floated around, so it fits in the geo-partition model we use for YSQL…or if we could get by with something much cheaper, like figuring out how to apply placement policies at individual tablet levels, for the transactions table, letting the LB do much of the heavy lifting — case in which we might need some folks with more master/LB context in the meeting.

There are two possible approaches:

1. Specify different placement for different tablets of the transaction status table. Specifying placement per-tablet is a new feature we will need to add.
2. Allow creating multiple transaction status tables. We can already specify placement per table. We will just need to learn to select the status tablet to use out of all the available transaction status tables.

From a discussion with @spolitov, the second approach (multiple status tablets) seems easier. This will involve periodically refreshing the information about the complete set of transaction status tables/tablets on each transaction client (YBClient).

[bmatican]

existing limitations

• we cannot create new txn status tablets

notes from meeting on the topic

• preferable to go with option 2, as we already have per-table placement, but also because we currently cannot add new tablets to the txn table, so this would allow us a clean way of generating new txn tablets
• PG should have some mechanism (session var / gflag), to select if a txn should use an in-region txn status tablet, or a globally distributed one
  • introduce new retriable error (eg: like the txn conflict ones), that we can bubble up to the user, in case a txn starts off as region local, but then touches global tablets
• we will also need to flow the placement information of these tables, from master to tserver, in order to be able to distinguish which tablets are region local vs which are global
• there's also some code where we cache the txn tablets, as we currently expect them to be a fixed set, so this will also have to change

@mbautin @kgupta-yb in case I missed anything

[bmatican]

Followup clarification from Sergei, on how any queries to the txn status tablet would be handled, after a txn is started (eg: conflict resolution)

each transaction has assigned status tablet. and it is stored in transaction metadata, so when we find intent we always know what status tablet should be queried