[YSQL] DROP SEQUENCE is not atomic

[jasonyb]

Jira Link: DB-5734

Description

Some objects are represented in both PG and YB (also colloquially referred to as "DocDB"). For example, tables: CREATE TABLE will first create the DocDB table then commit the pg metadata, and DROP TABLE will first remove the pg metadata then remove the DocDB table.

Sequences similarly have a PG representation and DocDB representation. However, it appears they have been neglected on the atomicity improvements that have been done on other objects like tables. So it may be possible for the sequence to exist in PG but not in DocDB or vice versa.

Here's an easy repro showing how DROP SEQUENCE may drop the sequence in DocDB but not in PG in case of a crash between the two internal steps:

Apply patch on master commit c92ad22:

diff --git i/src/postgres/src/backend/commands/sequence.c w/src/postgres/src/backend/commands/sequence.c
index d143502879..3cad67a9fd 100644
--- i/src/postgres/src/backend/commands/sequence.c
+++ w/src/postgres/src/backend/commands/sequence.c
@@ -624,6 +624,8 @@ DeleteSequenceTuple(Oid relid)
         HandleYBStatus(YBCDeleteSequenceTuple(MyDatabaseId, relid));
     }
 
+    elog(ERROR, "crash on drop");
+
     CatalogTupleDelete(rel, tuple);
 
     ReleaseSysCache(tuple);

bin/yb-ctl create --tserver_flags "ysql_sequence_cache_minval=0"
bin/ysqlsh

CREATE SEQUENCE s;
SELECT * FROM pg_sequence;

 seqrelid | seqtypid | seqstart | seqincrement |       seqmax        | seqmin | seqcache | seqcycle 
----------+----------+----------+--------------+---------------------+--------+----------+----------
    16384 |       20 |        1 |            1 | 9223372036854775807 |      1 |        1 | f
(1 row)

DROP SEQUENCE s;

ERROR:  crash on drop

-- reconnnect: this is optional but more closely resembles a crash
\c
SELECT nextval(16384);

ERROR:  Unable to find relation for sequence

cc: @deeps1991, @mislam-yb

Note that we can have issues with ALTER SEQUENCE RESET due to lack of atomicity. CREATE SEQUENCE can also possibly leave behind garbage rows in DocDB. However these issues are separately tracked in #19011 where we will convert the sequences table backed by DocDB into a transactional table that can be updated atomically with the PG catalog. This ticket merely tracks the fix for the failures incurred above due to DROP SEQUENCE not being atomic.