[YSQL] Dropping a primary key constraint is not supported

[radekg]

Jira Link: DB-1677
Attempting dropping a primary key constraint results in an error:

Error: ERROR: dropping a primary key constraint is not yet supported [Failed SQL: ALTER TABLE public.TABLE_NAME DROP CONSTRAINT CONSTRAINT_2]

This is explicitly disabled in the code:

yugabyte-db/src/postgres/src/backend/commands/tablecmds.c

Line 10500 in 1a8f161

errmsg("dropping a primary key constraint is not yet supported")));

.

From what I hear, the reason is that the primary key is tied to the table's storage layout and this is remotely related to ALTER TABLE adding a primary key #1104. This seems like a reversal of fd257e7.

[mskarbek]

+1 with Boundary:

"error": "postgres.(Postgres).EnsureVersionTable: unknown, unknown: error #0: ERROR: dropping a primary key constraint is not yet supported (SQLSTATE 0A000)",

[jimlambrt]

+1 for this capability. I'd like to run Boundary with yugabyte

[amitsaxena]

Is there a workaround if I want to change the primary key of a table? Haven't been able to find anything in the docs:
https://docs.yugabyte.com/preview/explore/indexes-constraints/primary-key-ysql/

[frozenspider]

@amitsaxena While this issue isn't implemented, you can do something like

CREATE TABLE t2 (...);
INSERT INTO t2 SELECT * FROM t;
DROP TABLE t;
ALTER TABLE t2 RENAME TO t;

This assumes you're just playing around and there are no real workloads using t

[radekg]

While this is a great insight, it’s not enough in many cases. This issue is still preventing installing keycloak on ybdb using default migrations.

[frozenspider]

@radekg of course, this is merely a workaround. This issue is on our radars though.

[arunk-r]

YugaByte DB Liquibase error for Keycloak.
Reason: liquibase.exception.DatabaseException: ERROR: dropping a primary key constraint is not yet supported [Failed SQL: (0) ALTER TABLE public.USER_REQUIRED_ACTION DROP CONSTRAINT CONSTRAINT_2]

[frozenspider]

This has been implemented.
Note that just like ALTER ADD PK, this is NOT an online operation, and is only expected to be used for a database setup. Concurrent DMLs to this table might be lost!
Making it online is tracked separately in #13274.