[docdb] Make YSQL create table metadata consistent

[bmatican]

Jira Link: DB-2461
Currently, a YSQL issued CREATE TABLE will need to update 2 different types of metadata:

• pg system tables (reads and writes to the system tables)
• docdb metadata, via a CreateTable RPC (actually create tablets on TServers and track the table/tablets in the catalog)

While each of these 2 steps could be considered atomic, we need to guarantee consistency across both. Assume these are actually done sequentially right now, first the pg table queries, then the master CreateTable RPC. We consider 4 cases:

1. Both the pg system table operations and the CreateTable rpc work fine
   Awesome, this is the happy path. Metadata is consistent.

2. Some failures during the pg system table operations.
   The transaction can just be cleanly aborted and no side effects are left in the system.

3. All the pg system table operations go through, but the CreateTable rpc fails
   We can hold off on committing the transaction until we actually get a success from the CreateTable RPC. That way, on a failed CreateTable, we can abort the txn, and that part is handled like in 1). As for the CreateTable rpc itself, it should guarantee its own fault tolerance semantics, so upon clear failure, we should be able to try again.

4. (really 3.1...) All the pg system table operations go through, but the CreateTable rpc times out
   Time outs are tricky. Did the CreateTable even make it to the maser? Did it succeed just fine? Did it fail? Is it still being worked on? If we check for a created table, with the same ID, can we be confident that it was our session that generated it?

Handling this final case is the most problematic. To address this, we're considering the following approach. On timeout, rollback the pg transaction.

• If the CreateTable failed, we have nothing to worry about.
• If it succeeded, we have a stray table in the system, so we need a way to cleanup unneeded tables.

We already have a background thread in CatalogManager which does regular work every 1s (load balancing). We can augment this to also check for invalid tables. We can then tag the YSQL tables with the txn ID of the corresponding pg system table transaction that they were supposed to be consistent with. On cleanup, we can then check against the txn status tablet, by this txn ID, to see if:

• Txn is aborted -- we can delete this table
• Txn is committed -- this table should be here (this is the happy path, should make sure we don't do this over and over again)
• Txn info is missing -- could be aborted/committed but cleaned up. Now we have to check the master pg system tables, based on the table UUID (OID for pg system tables). We already have some code to do read/write in sys_catalog, as part of CREATE DATABASE, when copying template1 to a new database name.

Couple of open questions:

• How do we get the txn ID at the YSQL layer, so we can send it down with the CreateTable request?
• Do we have an API we can use from the master-side, for checking the status of a txn by ID?
• How do we read from the pg system tables? Which table should we check against?

cc @mbautin @m-iancu @ndeodhar

[bmatican]

We can probably tackle this as part of #3097, in a more unified manner.

[nspiegelberg]

Chatted with @m-iancu today to clarify this task. It looks like we can break this into 3 parts:

1. Ensure Failure/Timeout does not prevent us from Creating a Table or Database with the same name. (next release)
2. Ensure that Failed Table/Database entries are garbage collected. (medium term)
3. Provide a Rollback execution stack for failed DDL transactions. (long term)

The main work in #1 is running ysqlsh, injecting lower-level failures, and ensuring that a subsequent create with the same parameters will work. This will mostly mean [a] not adding Namespaces (Databases) to a names map if PGSQL. We will also need to [b] ensure that a yb-master of a partially-created or destroyed table & namespace will perform proper cleanup. Finally, we want to ensure that [c] postgres-level transaction timeouts are respected and any costly IO work is canceled after the deadline. [c] might be tricky since these transactions span a multiple YBClient API requests.

[nspiegelberg]

Note: End solution needs to support the ROLLBACK command for CREATE TABLE (https://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis)