Schema Diff: recreating a foreign table duplicates any column that also differs

[dpage]

Bug Description

When Schema Diff has to drop and recreate a foreign table, because its foreign server differs, any column that also differs between the two sides is written into the new definition twice, and PostgreSQL rejects the statement.

A source foreign table of:

CREATE FOREIGN TABLE test_schema_diff.ft_diff_foreign_server(
    fid bigint,
    fname text
) SERVER test_fs_for_foreign_table;

compared against a target that uses a different foreign server and a different definition of fid produces:

DROP FOREIGN TABLE test_schema_diff.ft_diff_foreign_server;

CREATE FOREIGN TABLE test_schema_diff.ft_diff_foreign_server(
    fid bigint,
    fname text COLLATE pg_catalog."default",
    fid bigint
)
    SERVER test_fs_for_foreign_table;

which fails with:

ERROR:  column "fid" specified more than once

ForeignTableView._modify_column_data() in web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/__init__.py builds the recreated table's column list by starting from the target's existing columns and then appending the difference:

        if 'added' in data['columns']:
            for item in data['columns']['added']:
                tmp_columns.append(item)
        if 'changed' in data['columns']:
            for item in data['columns']['changed']:
                tmp_columns.append(item)

A changed column is already in that list in its old form, so appending the new form leaves both. Appending is right for an added column, but a changed one has to replace what is already there.

Expected Behaviour

The recreated foreign table should declare each column once, with the source's definition.

Context

Found by making the Schema Diff regression test assert that the SQL it generates actually applies (#10293).