[YSQL] "alter table t drop constraint if exists" not working

Jira Link: DB-4329

Description

I tested using YB-2.15.3.2.

PostgreSQL's "drop... if exists" construct brings a significant usability benefit. Oracle Database has no counterpart and so, there, installation scripts that must be idempotent will either generate lots of spurious "doesn't exist" errors (and this is the common case) or will require considerable effort (usually a PL/SQL procedure that itself has to be installed) to catch the "doesn't exist" error and stay quiet.

Mainly, YB implements PG's "drop... if exists" syntax properly in all of its many possible contexts. But I just stumbled on a case where it doesn't. Here's a sufficient testcase.

First the positive case. It runs without error.

\set VERBOSITY verbose

drop table if exists t cascade;
create table t(k serial primary key, v int not null);
alter table t add constraint t_v_positive check(v > 0);
alter table t drop constraint if exists t_v_positive;

Notice that the "drop constraint if exists" syntax is accepted and seems to be honored. You can test with the constraint was dropped by recreating it with a different definition than it first had:

alter table t add constraint t_v_positive check(v >= 0);

Now demonstrate the bug.

Simply repeat "alter table t drop constraint if exists t_v_positive" immediately after it did drop the existing constraint. You get this error:

42704: constraint "t_v_positive" for table "t" does not exist

The "42704" code maps to the PL/pgSQL named exception "undefined_object".

The same test produces no such error in vanilla PG Version 11. And the [Version 11 PG doc](https://www.postgresql.org/docs/11/sql-altertable.html) for "alter table" shows that the syntax is supported. However, the corresponding [YB doc](https://docs.yugabyte.com/preview/api/ysql/the-sql-language/statements/ddl_alter_table/) does not show this syntax.

This is bad in many ways:

• It's an ordinary annoyance, on its face, that standard PG functionality simply does not work in YB.

• It's extra annoying that the standard response in this situation (to the effect that the functionality is not yet supported and that the user should add their vote to a referenced GitHub issue that asks for the limitation to be removed) is missing.

• It reflects careless coding. Clearly some of the code path is the vanilla PG code, adopted "as is" behaving normally. That's why there is no syntax error. But the downstream action doesn't reflect the semantics that the syntax analysis detected. Rather, the action is simply always to do an unqualified drop:

  alter table t drop constraint t_v_positive;
  

  So it seems to be a straight wiring problem. In vanilla PG, this syntax causes the "42704" error when the constraint doesn't exist—just as the "if exists" variant does uniquely in YB.

• It reflects careless QA. Somebody must have commented out the vanilla PG test for the functionality under discussion rather than doing the right thing. It's hard to imagine that it would take less effort to implement the proper semantics than to implement the code to say "add you vote to GitHub issue NNNNN". But it presumably does take less effort simply to remove the test.