[Bug] Some commands forgot to mark meta track calls unstable failed cases.

[avamingli]

Cloudberry Database version

 PostgreSQL 14.4 (Cloudberry Database 1.5.4+dev.74.gc87fc6d8bb6 build dev) on x86_64-pc-linux-gnu, compi
led by gcc (Ubuntu 11.3.0-1ubuntu1~22.04.1) 11.3.0, 64-bit compiled on Jul  8 2024 13:50:22 (with assert
 checking)
(1 row)

What happened

Unstable case:

/code/gpdb_src/src/test/regress/results/aqumv.out
[15511](https://github.com/cloudberrydb/cloudberrydb/actions/runs/9832799441/job/27142431110?pr=501#step:5:15512)--- /code/gpdb_src/src/test/regress/expected/aqumv.out        2024-07-08 03:43:30.532971623 +0000
[15512](https://github.com/cloudberrydb/cloudberrydb/actions/runs/9832799441/job/27142431110?pr=501#step:5:15513)+++ /code/gpdb_src/src/test/regress/results/aqumv.out        2024-07-08 03:43:30.555971269 +0000
[15513](https://github.com/cloudberrydb/cloudberrydb/actions/runs/9832799441/job/27142431110?pr=501#step:5:15514)@@ -4,247 +4,132 @@
[15514](https://github.com/cloudberrydb/cloudberrydb/actions/runs/9832799441/job/27142431110?pr=501#step:5:15515) --
[15515](https://github.com/cloudberrydb/cloudberrydb/actions/runs/9832799441/job/27142431110?pr=501#step:5:15516) set optimizer = off;
[15516](https://github.com/cloudberrydb/cloudberrydb/actions/runs/9832799441/job/27142431110?pr=501#step:5:15517) create schema aqumv;
[15517](https://github.com/cloudberrydb/cloudberrydb/actions/runs/9832799441/job/27142431110?pr=501#step:5:15518)+DETAIL:  Key (classid, objid, staactionname)=(2615, 24638, CREATE) already exists.
[15518](https://github.com/cloudberrydb/cloudberrydb/actions/runs/9832799441/job/27142431110?pr=501#step:5:15519)+ERROR:  duplicate key value violates unique constraint "pg_statlastop_classid_objid_staactionname_index"
[15519](https://github.com/cloudberrydb/cloudberrydb/actions/runs/9832799441/job/27142431110?pr=501#step:5:15520) set search_path to aqumv;

What you think should happen instead

Drop Schema command forget to drop meta track object.

While reading the codes, I find there are several places have the same issue.

• RemoveSchemaById
• RemovePublicationById
• RemovePolicyById

When some oids are used again, it will report error due to the unique index.

gpadmin=# \d+ pg_stat_last_operation;
                                            Table "pg_catalog.pg_stat_last_operation"
    Column     |           Type           | Collation | Nullable | Default | Storage  | Compression | St
ats target | Description
---------------+--------------------------+-----------+----------+---------+----------+-------------+---
-----------+-------------
 classid       | oid                      |           | not null |         | plain    |             |
           |
 objid         | oid                      |           | not null |         | plain    |             |
           |
 staactionname | name                     |           | not null |         | plain    |             |
           |
 stasysid      | oid                      |           | not null |         | plain    |             |
           |
 stausename    | name                     |           | not null |         | plain    |             |
           |
 stasubtype    | text                     | C         |          |         | extended |             |
           |
 statime       | timestamp with time zone |           |          |         | plain    |             |
           |
Indexes:
    "pg_statlastop_classid_objid_staactionname_index" UNIQUE CONSTRAINT, btree (classid, objid, staactio
nname)

How to reproduce

gpadmin=# select * from pg_stat_last_operation;
 classid | objid | staactionname | stasysid | stausename | stasubtype | statime
---------+-------+---------------+----------+------------+------------+---------
(0 rows)

gpadmin=# create schema xmen;
CREATE SCHEMA
gpadmin=# select * from pg_stat_last_operation;
 classid | objid | staactionname | stasysid | stausename | stasubtype |            statime
---------+-------+---------------+----------+------------+------------+-------------------------------
    2615 | 16385 | CREATE        |       10 | gpadmin    | SCHEMA     | 2024-07-08 13:57:18.716533+08
(1 row)

gpadmin=# drop schema xmen;
DROP SCHEMA
gpadmin=# select * from pg_stat_last_operation;
 classid | objid | staactionname | stasysid | stausename | stasubtype |            statime
---------+-------+---------------+----------+------------+------------+-------------------------------
    2615 | 16385 | CREATE        |       10 | gpadmin    | SCHEMA     | 2024-07-08 13:57:18.716533+08
(1 row)

Operating System

Linux i-v3roa8c2 5.15.0-76-generic #83-Ubuntu SMP Thu Jun 15 19:16:32 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux

Anything else

No response

Are you willing to submit PR?

• Yes, I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct.

[avamingli]

Drop policy

CREATE POLICY p1 ON t1 FOR SELECT USING (a % 2 = 0);
CREATE POLICY
gpadmin=# select * from pg_stat_last_operation;
 classid | objid | staactionname | stasysid | stausename | stasubtype |            statime
---------+-------+---------------+----------+------------+------------+-------------------------------
    2615 | 16385 | CREATE        |       10 | gpadmin    | SCHEMA     | 2024-07-08 13:57:18.716533+08
    1259 | 16386 | CREATE        |       10 | gpadmin    | TABLE      | 2024-07-08 14:11:12.50767+08
    3256 | 16389 | CREATE        |       10 | gpadmin    | POLICY     | 2024-07-08 14:11:46.778425+08
(3 rows)

drop policy p1 on t1;
DROP POLICY
gpadmin=# select * from pg_stat_last_operation;
 classid | objid | staactionname | stasysid | stausename | stasubtype |            statime
---------+-------+---------------+----------+------------+------------+-------------------------------
    2615 | 16385 | CREATE        |       10 | gpadmin    | SCHEMA     | 2024-07-08 13:57:18.716533+08
    1259 | 16386 | CREATE        |       10 | gpadmin    | TABLE      | 2024-07-08 14:11:12.50767+08
    3256 | 16389 | CREATE        |       10 | gpadmin    | POLICY     | 2024-07-08 14:11:46.778425+08
(3 rows)

[avamingli]

drop publication

CREATE PUBLICATION dummy_pub;
gpadmin=# select * from pg_stat_last_operation;
 classid | objid | staactionname | stasysid | stausename | stasubtype  |            statime
---------+-------+---------------+----------+------------+-------------+-------------------------------
    2615 | 16385 | CREATE        |       10 | gpadmin    | SCHEMA      | 2024-07-08 13:57:18.716533+08
    1259 | 16386 | CREATE        |       10 | gpadmin    | TABLE       | 2024-07-08 14:11:12.50767+08
    3256 | 16389 | CREATE        |       10 | gpadmin    | POLICY      | 2024-07-08 14:11:46.778425+08
    6104 | 16390 | CREATE        |       10 | gpadmin    | PUBLICATION | 2024-07-08 14:13:53.60819+08
(4 rows)

drop PUBLICATION dummy_pub;
DROP PUBLICATION
gpadmin=# select * from pg_stat_last_operation;
 classid | objid | staactionname | stasysid | stausename | stasubtype  |            statime
---------+-------+---------------+----------+------------+-------------+-------------------------------
    2615 | 16385 | CREATE        |       10 | gpadmin    | SCHEMA      | 2024-07-08 13:57:18.716533+08
    1259 | 16386 | CREATE        |       10 | gpadmin    | TABLE       | 2024-07-08 14:11:12.50767+08
    3256 | 16389 | CREATE        |       10 | gpadmin    | POLICY      | 2024-07-08 14:11:46.778425+08
    6104 | 16390 | CREATE        |       10 | gpadmin    | PUBLICATION | 2024-07-08 14:13:53.60819+08
(4 rows)

[avamingli]

Fixed by #505