Yugabyte-db: [YSQL] Don't change primary_key name when dumping from Postgresql 11 using ysql_dumpall

[ddorian]

1. In Postgresql 11 run:

postgres=# create table my_table(id bigserial);
postgres=# alter table my_table add constraint pk_mymymy PRIMARY KEY (id);
postgres=# \d+ my_table
                                               Table "public.my_table"
 Column |  Type  | Collation | Nullable |               Default                | Storage | Stats target | Description 
--------+--------+-----------+----------+--------------------------------------+---------+--------------+-------------
 id     | bigint |           | not null | nextval('my_table_id_seq'::regclass) | plain   |              | 
Indexes:
    "pk_mymymy" PRIMARY KEY, btree (id)

1. Use ysql_dumpall to export: (port below is 5433 because I have multiple postgresql instances)

./postgres/bin/ysql_dumpall --user=postgres --database=postgres --file=mymymy.txt --port=5433

1. The output doesn't contain the name of the primary key
   mymymy.txt

[nvcnvn]

Hi, Im the one report this issue on slack.
Can someone give me a hint how to fix this? Where is the source code of this fork? (i guess this is a fork or wrapper of pg_dump tools).

[ddorian]

@nvcnvn do you want to make the internal change in c++ ?

Or a workaround for your case ?
If yes, you can:

1. Export just the schema
2. Manually edit the "CREATE TABLE"
3. Import the schema
4. Export data + import data

[nvcnvn]

@nvcnvn do you want to make the internal change in c++ ?

Or a workaround for your case ?
If yes, you can:

1. Export just the schema
2. Manually edit the "CREATE TABLE"
3. Import the schema
4. Export data + import data

Hi @ddorian I want to make change in internal C++ hope that you can show me a hint where to start.
I need to dump the db every week, sometime we have new table also and our current app depends on key name... (I can write a script for faster replace key name but rather to spend time on fixing this).

[kmuthukk]

thx @nvcnvn -- will get back to you on this. Thx for offering to submit a PR for this.

[m-iancu]

@nvcnvn @OlegLoginov I think the issue is related to us inlining the primary key constraints since our tables are index organized.
So for a PG11 table created as above:

create table my_table(id bigserial);
alter table my_table add constraint pk_mymymy PRIMARY KEY (id);

ysql_dump might output something along the lines of

create table my_table(id bigserial, primary key(id));

Instead, we could just do:

create table my_table(id bigserial, constraint pk_mymymy primary key(id));

Then we'd get the expected output:

yugabyte=# \d my_table
                            Table "public.my_table"
 Column |  Type  | Collation | Nullable |               Default
--------+--------+-----------+----------+--------------------------------------
 id     | bigint |           | not null | nextval('my_table_id_seq'::regclass)
Indexes:
    "pk_mymymy" PRIMARY KEY, lsm (id HASH)

I think the relevant code is in pg_dump.c -- we need to add the index/constraint name there.

We should also add a relevant test for TetsYsqlDump.java -- which should just mean we need to update the corresponding .sql/.out files that it uses: add a test with a named pkey -- inline declaration since YSQL only supports that for now -- and confirm it gets exported correctly to the out file.
The two files are here:

• yb_ysql_dump.sql
• yb_ysql_dump.out

cc @kmuthukk @ddorian