MaterializedPostgreSQL fails on data update in case of postgresql generated columns.

[istarkov]

Clickhouse version affected 22.3.7.28, 22.8.1.2097
postgres 14

Imaging following table in postgresql.

--postgres
CREATE TABLE test (
    id integer PRIMARY KEY,
    x double precision,
    y double precision GENERATED ALWAYS AS (x*2) STORED
);
insert into test values (1,1);
insert into test values (2,2);

then at clickhouse create

-- clickhouse
CREATE DATABASE IF NOT EXISTS {REPLICATION_IDENTIFIER:Identifier}
ENGINE = MaterializedPostgreSQL('db:5432', database', 'login', 'pass')
SETTINGS materialized_postgresql_tables_list='test';
// Wait until SELECT name, total_rows  FROM system.tables WHERE database = 'pg'; // gives 2 rows

do

-- clickhouse
select * from pg.test

Everything is fine, all 3 columns are visible id, x, y

Now at postgresql delete column

-- postgres
delete from test where id = 2;

Update will not be replicated. In logs will be

<Warning> PostgreSQLReplicaConsumer(scrapers): Table test (relation_id: 61753011) is skipped, because table schema has changed

I think, but not sure 100% it's because of this check

ClickHouse/src/Storages/PostgreSQL/MaterializedPostgreSQLConsumer.cpp

Lines 500 to 504 in 5e86d6c

	if (static_cast<size_t>(num_columns) + 2 != buffer.getColumnsNum()) /// +2 -- sign and version columns
	{
	markTableAsSkipped(relation_id, table_name);
	return;
	}

because no other warnings etc are seen in the log.

without GENERATED columns all is fine.