Schema Diff: a foreign table column added by Schema Diff loses its collation

[dpage]

Bug Description

A column that Schema Diff adds to (or retypes on) a foreign table loses its collation, so applying the generated script leaves the two databases different and re-comparing reports the same difference again.

With a source foreign table of:

CREATE FOREIGN TABLE test_schema_diff.ft_diff_col(
    fid bigint,
    fname text COLLATE pg_catalog."default",
    fcity character varying(40) COLLATE pg_catalog."POSIX"
) SERVER test_fs_for_foreign_table;

and a target lacking fcity, the generated script contains:

ALTER FOREIGN TABLE IF EXISTS test_schema_diff.ft_diff_col
    ADD COLUMN fcity character varying(40);

with no COLLATE pg_catalog."POSIX", so the column arrives with the database default collation.

The cause is that the collation goes by two different names. foreign_tables/sql/default/get_columns.sql exposes it as collname, which is what the properties (and therefore Schema Diff) carry, whilst foreign_table.ui.js calls the dialog field collspcname, which is what foreign_table_columns/sql/default/create.sql and update.sql render:

{% if data.collspcname %}
 COLLATE {{data.collspcname}}{% endif %}

Data coming from the dialog satisfies that test and data coming from Schema Diff never does, so the clause is quietly dropped.

Expected Behaviour

An added or retyped foreign table column should keep the source's collation, and applying the script should settle the difference.

Context

Found by making the Schema Diff regression test assert that applying the generated script leaves the two databases identical (#10293).