Skip to content

2025.1.5.0-b77

2025.1.5.0-b77: [BACKPORT 2025.1][#26610] YSQL: Fix backup for a dropped table column
@OlegLoginov OlegLoginov tagged this 24 Jun 08:46 
Summary:
Original commit: d292491e68ff71225f4d4ea4fa635c835d865292 / D53238

If we do `ALTER TABLE ... DROP COLUMN ...` the dropped column is marked as `dropped` in the `pg_attribute` table.
Currently backup-restore process loses such dropped columns.
So, if the table with the dropped column is used as a type, the reference becomes invalid.
For example:
```
CREATE TABLE row_type_tbl (x4 INT, x1 INT);
CREATE TABLE my_table (x row_type_tbl);
ALTER TABLE row_type_tbl DROP COLUMN x4;

    format_type(a.atttypid, a.atttypmod) AS "Type",
    a.atttypid AS "Type_OID", a.attisdropped as "DROPPED"
FROM pg_attribute a
WHERE a.attrelid = 'public.row_type_tbl'::regclass AND a.attnum > 0;
 # |            Column            |  Type   | Type_OID | DROPPED
---+------------------------------+---------+----------+---------
 1 | ........pg.dropped.1........ | -       |        0 | t
 2 | x1                           | integer |       23 | f
(2 rows)
```
After `DROP COLUMN` the table `row_type_tbl` is updated, but the table `my_table` is NOT updated.
For the tables like `my_table` (which use `row_type_tbl` as a type), we need to generate in YSQLDump
complete list of columns including the dropped ones:
```
CREATE TABLE public.row_type_tbl (
    "........pg.dropped.1........" INTEGER /* dummy */,
    x1 integer
) SPLIT INTO 1 TABLETS;

ALTER TABLE ONLY public.row_type_tbl DROP COLUMN "........pg.dropped.1........";
```
Before the diff we had in the dump only:
```
CREATE TABLE public.row_type_tbl (
    x1 integer
) SPLIT INTO 1 TABLETS;
```
So, before backup we had: [1] x4 - dropped, [2] x1.
After the backup restore: [1] x1. Order is changed, so `select x from my_table`
actually returns 1-st column - dropped `x4` value (instead of `x1` - 2-nd column).
It's not failing in the schema check in `ImportSnapshot`, because in `my_table` anyway we have `x BINARY` column.

After the fix the column order - `attnum` value in `pg_attribute` is correct after the backup-restore.
And the complex type columns (like `x` in `my_table`) can be correctly decoded
from the BINARY DocDB type after the backup-restore.

Actually we have needed code for the `binary_upgrade` mode in `ysql_dump`.
This diff enables the code for backup (`include-yb-metadata` mode).

Also the diff adds 4 new tests (see the Test Plan below) and updates `yb.orig.backup_restore.sql` /
`yb.orig.backup_restore_describe.sql` (test `testPgRegressStyle`).

Test Plan:
./yb_build.sh --java-test org.yb.pgsql.TestYbBackup#testPgRegressStyle
./yb_build.sh --java-test org.yb.pgsql.TestYbBackup#testBackupDropColumn

./yb_build.sh --java-test org.yb.pgsql.TestYbBackup#testRowTypeTable
./yb_build.sh --java-test org.yb.pgsql.TestYbBackup#testPartitionedTableWithDroppedColumnsAttnumMismatch
./yb_build.sh --java-test org.yb.pgsql.TestYbBackup#testPartitionedTableWithCompositeTypesAndDroppedColumns

Reviewers: sanketh, fizaa

Reviewed By: fizaa

Subscribers: yql, svc_phabricator

Differential Revision: https://phorge.dev.yugabyte.com/D54769
Assets 2
Loading