[YSQL] Restore fails due to ysql_dump not preserving ASC/DESC ordering of UNIQUE CONSTRAINT (it always defaults to HASH)

[tverona1]

Jira Link: DB-3178

Description

If a unique index with asc column ordering is added as a constraint to a table, ysql_dump does not preserve the asc ordering. Instead, it generates an ALTER TABLE ADD CONSTRAINT syntax that results in the column being hash partitioned.

Subsequent restore of the original database then fails due to mismatch in expected definition of the constraint - i.e.: Error running import_snapshot: Internal error (yb/master/catalog_manager_ent.cc:2141): Unable to import snapshot meta file /tmp/yb_backup_yhbuwflrvtataaov/SnapshotInfoPB: Invalid created PGSQL_TABLE_TYPE table 'c1' in namespace id 00004005000030008000000000000000: schema=...

Repro On the original database, create a unique index and add it as a unique constraint:

CREATE TABLE t1(col int);
CREATE UNIQUE INDEX i1 on t1(col asc);
ALTER TABLE t1 ADD CONSTRAINT c1 UNIQUE USING INDEX i1;

Examine the unique constraint - it is defined with col ASC as expected:

\d t1
                                    Table "public.t1"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 col    | integer |           |          |         | plain   |              | 
Indexes:
    "c1" UNIQUE CONSTRAINT, lsm (col ASC)

Run ysql_dump, which generates the following output:

CREATE TABLE t1(col int);
ALTER TABLE t1 ADD CONSTRAINT c1 UNIQUE(col);

Examine the unique constraint - it is incorrectly defined with col HASH:

\d+ t1
                                    Table "public.t1"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 col    | integer |           |          |         | plain   |              | 
Indexes:
    "c1" UNIQUE CONSTRAINT, lsm (col HASH)

The reason is that PG does not distinguish whether the constraint was added via USING INDEX or directly via ADD CONSTRAINT and YB default to HASH partitioning.

A fix would be to change ysql_dump to split UNIQUE CONSTRAINT into two statements - CREATE INDEX and ADD CONSTRAINT USING INDEX. This fix would also address #11583.

[tedyu]

This seems to be dup of #13601