[DocDB] Deleting a PG catalog table doesn't delete its rows

[foucher]

Jira Link: DB-11673

Description

Steps to repro:

1. Run bin/yb-ctl wipe_restart --tserver_flags="ysql_pg_conf_csv=allow_system_table_mods=true"
2. Run ysqlsh and run the following commands:

   
   CREATE TABLEGROUP tgrp1;
   SELECT * FROM pg_yb_tablegroup;
   SET yb_non_ddl_txn_for_sys_tables_allowed TO ON;
   DELETE FROM pg_depend WHERE refobjid = 8037;
   DELETE FROM pg_depend WHERE refobjid = 8036;
   DROP INDEX pg_yb_tablegroup_oid_index;
   DROP TABLE pg_yb_tablegroup;
   DELETE FROM pg_type WHERE oid = 8038;
   DELETE FROM pg_depend WHERE refobjid = 8038;

SET ysql_upgrade_mode TO ON; CREATE TABLE IF NOT EXISTS pg_catalog.pg_yb_tablegroup ( grpname name NOT NULL, grpowner oid NOT NULL, grptablespace oid NOT NULL, grpacl aclitem[], grpoptions text[], CONSTRAINT pg_yb_tablegroup_oid_index PRIMARY KEY (oid ASC) WITH (table_oid = 8037) ) WITH ( oids = true, table_oid = 8036, row_type_oid = 8038 ); SELECT * FROM pg_yb_tablegroup;

Expected:
Since `pg_yb_tablegroup` has just been dropped, you would expect it to not have any rows when re-created.

Result:
It still has the same row it had in the first SELECT statement in the repro steps.

### Issue Type

kind/bug

### Warning: Please confirm that this issue does not contain any sensitive information

- [X] I confirm this issue does not contain any sensitive information.

[DB-11673]: https://yugabyte.atlassian.net/browse/DB-11673?atlOrigin=eyJpIjoiNWRkNTljNzYxNjVmNDY3MDlhMDU5Y2ZhYzA5YTRkZjUiLCJwIjoiZ2l0aHViLWNvbS1KU1cifQ

[foucher]

We've put this on hold due to the performance impact. I tested a fix which deletes the rows, using:

\timing
DROP DATABASE postgres;

run 3x on each of fastdebug and release on my M1 Mac, on a fresh cluster each time. Here are the median results:

Build type	Don't delete rows	Delete rows	Delete / Don't delete
fastdebug	533.456 ms	1390.993 ms	2.6x
release	497.827 ms	1335.448 ms	2.7x

It will get even slower with more objects meaning more catalog rows.

We could return quickly while still deleting the rows by making the delete async, at the cost of some additional complexity.