[PG] Sequence owned by an identity column is mis-attributed when the table has dropped columns

Provide the Bytebase version you are using

3.21.1 (self-hosted, Docker, embedded PostgreSQL)

Describe the bug

The Schema change tab renders the visual diff correctly, but the Generated DDL statement tab fails:

Code 13: Internal
[internal] failed to compute schema diff: failed to load source schema:
ERROR: sequence "menu_group_id_seq" already exists (SQLSTATE 42710)

The exported schema emits the same sequence twice — once as a standalone sequence, and once via the identity clause on the table:

-- 1. emitted as a standalone sequence
CREATE SEQUENCE "app"."menu_group_id_seq" AS bigint
    START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 NO CYCLE CACHE 1;

-- 2. created a second time by the identity clause
CREATE TABLE "app"."menu_group" (
    "col_01" character varying(7) NOT NULL,
    ...
    "id"     bigint GENERATED ALWAYS AS IDENTITY,   -- creates menu_group_id_seq again
    "col_13" character(1) DEFAULT 'N'::bpchar,
    "col_14" character(1) DEFAULT 'N'::bpchar,
    "col_15" character(1) DEFAULT 'N'::bpchar,
    CONSTRAINT "menu_group_pk" PRIMARY KEY (id),
    CONSTRAINT "menu_group_uk" UNIQUE (col_01, col_02, col_03, col_04)
);

-- 3. ownership points at the wrong column
ALTER SEQUENCE "app"."menu_group_id_seq"
    OWNED BY "app"."menu_group"."col_14";

Replaying this fails at step 2, because step 1 already created the sequence.

Root cause. pg_depend.refobjsubid is an attnum, but it appears to be resolved as an ordinal position in the list of visible columns. The two diverge as soon as the table has dropped columns, because dropped columns keep their attnum.

Catalog state for the affected table (names anonymised):

attnum  attname                       attisdropped   visible position
     1  col_01                        false           1
     2  col_02                        false           2
     3  col_03                        false           3
     4  col_04                        false           4
     5  col_05                        false           5
     6  ........pg.dropped.6........  true            -   <-- dropped
     7  col_06                        false           6
     8  ........pg.dropped.8........  true            -   <-- dropped
     9  col_07                        false           7
    10  col_08                        false           8
    11  col_09                        false           9
    12  col_10                        false          10
    13  col_11                        false          11
    14  col_12                        false          12
    15  id                            false          13   <-- real owner
    16  col_13                        false          14
    17  col_14                        false          15   <-- named in OWNED BY
    18  col_15                        false          16

• id has attnum 15, but is the 13th visible column.
• The 15th visible column is col_14 — exactly what the OWNED BY clause names.
• The offset is 2, matching the 2 dropped columns.

Because ownership resolves to a non-identity column, the sequence is classified as standalone and gets its own CREATE SEQUENCE, colliding with the one the identity clause creates.

Verified on the source database: menu_group_id_seq has exactly one dependency — menu_group.id with pg_depend.deptype = 'i'. No duplicate object of that name exists in any schema, and no column default references it.

Scope. 2 of ~300 sequences are affected. Both are on tables where the id column was added later via ALTER TABLE ADD COLUMN, and both have dropped columns at attnum 6 and 8. These are also the only two ALTER SEQUENCE ... OWNED BY statements in the whole export, and both name the wrong column.

Steps to reproduce

Minimal repro:

CREATE TABLE t (a int, b int);
ALTER TABLE t DROP COLUMN a;                                      -- attnum 1 dropped
ALTER TABLE t ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY;  -- attnum 3
ALTER TABLE t ADD COLUMN z int;                                   -- attnum 4

-- visible columns: b (1st), id (2nd), z (3rd)
-- pg_depend.refobjsubid for the identity dependency = 3 (attnum of id)
-- resolved as "3rd visible column" -> z   (wrong)

1. Create the table above on a PostgreSQL instance.
2. Register the instance in Bytebase and sync it.
3. Go to Database > Sync Schema, pick this database as the source and any other database as the target.
4. Open the Generated DDL statement tab.
5. See the error. (Export Schema shows the same wrong OWNED BY clause.)

Expected behavior

pg_depend.refobjsubid should be resolved as an attnum, so the sequence is attributed to id and recognised as owned by an identity column — emitted only through the identity clause, with no separate CREATE SEQUENCE.

Provide the database you are using

PostgreSQL 17.7 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 10.5.0, 64-bit

Additional context

We are on 3.21.1, which already contains the composite-type fixes from #20434 (#20911 / #20912 / #20913), so this appears to be a separate gap in how sequences owned by identity columns are resolved.

There is no workaround on our side: attnum is only renumbered by recreating the table, which is not an option for production tables. Schema comparison itself remains usable (the visual diff is correct), but DDL generation is unavailable on any schema containing such a table.

A related earlier symptom on the same install, worked around separately:

ERROR: START value must be between MINVALUE and MAXVALUE (SQLSTATE 22023)

caused by an identity column whose sequence had MINVALUE 0 / START 0 — the generated DDL kept START WITH 0 but emitted the default MINVALUE 1. Looks like min_value = 0 is treated as unset. Reproducible with:

CREATE TABLE t2 (id bigint GENERATED ALWAYS AS IDENTITY (MINVALUE 0 START WITH 0));

(Schema, table and column names in this report are anonymised.)