[YSQL] CREATE TABLE without a primary key followed by ALTER TABLE adding a primary key should end up with one primary key

[mbautin]

1. Currently, the PK specification has to be done inline as part of the CREATE TABLE. In YugabyteDB, the PK columns guides the primary dimension in which the table is laid out in storage (so that retrieval by those PK column lookups is as efficient).

2. Creating the PK afterwards using an ALTER is not just syntax sugar work, but will actually involve reorganizing the on-disk structure of the table in a way that it is now organized by the PK as the primary dimension. So, with YugabyteDB it'll be advantageous if the system can be told upfront as part of the CREATE TABLE what the PK columns are.

Nevertheless, we should support ALTER TABLE to allow adding a PRIMARY KEY after the fact to be compatible with the broader ecosystem of tools.

[nocaway]

Once Hector completes the work on partitioning, this enhancement can be done.

[ndeodhar]

One possible approach can be to enhance index backfill for this: #448

Similar to creating index, when we run ALTER TABLE ADD PRIMARY KEY, we should:

1. Create a new table with the PK.
2. Any writes to old table should go to both old and new tables.
3. Backfill new table with entries from old table.
4. Switch all writes to only write to new table.
5. Drop old table.
   cc: @amitanandaiyer

[ben-pr-p]

This currently makes restoring from pg_dump impossible, since pg_dump chooses to add all indexes and constraints (and therefore primary keys as well) in it's "post-data" phase, since they may slow down the copy that might be there

[ndeodhar]

@ben-pr-p You can use ysql_dump utility to create the database dump and then restore it. This will take care of ensuring that schema and constraints are in YB format.
Note that you can use ysql_dump to take a dump of vanilla postgres DB as well.

[ben-pr-p]

Oh, neat! I'm having difficulty finding installation instructions for ysql_dump – do you know where those are? Not seeing it in my ./bin/

[ndeodhar]

It'll be in <yugabyte_install_dir>/postgres/bin folder. It's a modified version of pg_dump, so usage will be similar.

[frozenspider]

One possible approach can be to enhance index backfill for this: #448

Similar to creating index, when we run ALTER TABLE ADD PRIMARY KEY, we should:

1. Create a new table with the PK.

2. Any writes to old table should go to both old and new tables.

3. Backfill new table with entries from old table.

4. Switch all writes to only write to new table.

5. Drop old table.
   cc: @amitanandaiyer

Original PostgreSQL code takes AccessExclusiveLock on a table for ALTER TABLE ADD PRIMARY KEY, preventing concurrent writes.
Making this an online operation is a part of a larger endeavour, which is outside of this issue's scope and would be done separately at some point forward.

[frozenspider]

Note that the current implementation has a few limitations. Specifically:

• We don't yet support concurrent DMLs while this ALTER is in progress.
• The following cases are not supported as of now:
  • Colocated tables (including tablegroups).
  • Partitioned tables and table partitions.
  • Table having rules.
  • ALTER TABLE ADD CONSTRAINT PK USING INDEX
• If the table was created using WITH (table_old = x) (beta feature), that OID is lost without notice.
• There are minor mismatches between YB and PG error messages when migrating data fails (SQLSTATE is matching):
  • Null value in PK column
  • Duplicate PK

[uwejan]

Hello guys, good work. Is there an ETA for this feature? adding PK after column is really important with my project.

[frozenspider]

Sorry @uwejan, missed your comment. It's included in v2.6 onward.