-- Verify column_name FDW option is honored by INSERT on both engines.
-- Regression: SELECT primed the column-name cache via GetForeignRelSize, but
-- INSERT-only sessions never primed it, so deparse fell back to PG attnames
-- and the binary engine could not match the CH block's columns.
SET datestyle = 'ISO';
CREATE SERVER cn_http_loopback FOREIGN DATA WRAPPER clickhouse_fdw
    OPTIONS(dbname 'cn_test', driver 'http');
CREATE SERVER cn_bin_loopback FOREIGN DATA WRAPPER clickhouse_fdw
    OPTIONS(dbname 'cn_test', driver 'binary');
CREATE USER MAPPING FOR CURRENT_USER SERVER cn_http_loopback;
CREATE USER MAPPING FOR CURRENT_USER SERVER cn_bin_loopback;
CREATE SERVER cn_admin FOREIGN DATA WRAPPER clickhouse_fdw;
CREATE USER MAPPING FOR CURRENT_USER SERVER cn_admin;
CALL clickhouse_perform('cn_admin', 'drop database if exists cn_test');
CALL clickhouse_perform('cn_admin', 'create database cn_test');
CALL clickhouse_perform('cn_admin', 'CREATE TABLE cn_test.t_http (
    ch_id Int32, ch_val String, plain Nullable(Int32)
) ENGINE = MergeTree ORDER BY (ch_id);');
CALL clickhouse_perform('cn_admin', 'CREATE TABLE cn_test.t_bin (
    ch_id Int32, ch_val String, plain Nullable(Int32)
) ENGINE = MergeTree ORDER BY (ch_id);');
CREATE FOREIGN TABLE cn_http (
    pg_id int OPTIONS (column_name 'ch_id'),
    pg_val text OPTIONS (column_name 'ch_val'),
    plain int
) SERVER cn_http_loopback OPTIONS (table_name 't_http');
CREATE FOREIGN TABLE cn_bin (
    pg_id int OPTIONS (column_name 'ch_id'),
    pg_val text OPTIONS (column_name 'ch_val'),
    plain int
) SERVER cn_bin_loopback OPTIONS (table_name 't_bin');
-- INSERT before any SELECT keeps the column-name cache cold and exercises
-- the lazy populate path inside chfdw_get_custom_column_info.
INSERT INTO cn_http VALUES (1, 'http one', 100);
INSERT INTO cn_bin  VALUES (2, 'bin one',  200);
-- Reordered column list exercises deparse_insert_sql and, for binary,
-- ch_binary_make_tuple_map's name matching against the CH block columns.
INSERT INTO cn_http (plain, pg_val, pg_id) VALUES (300, 'http reorder', 3);
INSERT INTO cn_bin  (plain, pg_val, pg_id) VALUES (400, 'bin reorder',  4);
-- Subset INSERT leaves plain unset.
INSERT INTO cn_http (pg_id, pg_val) VALUES (5, 'http subset');
INSERT INTO cn_bin  (pg_id, pg_val) VALUES (6, 'bin subset');
SELECT * FROM cn_http ORDER BY pg_id;
 pg_id |    pg_val    | plain 
-------+--------------+-------
     1 | http one     |   100
     3 | http reorder |   300
     5 | http subset  |      
(3 rows)

SELECT * FROM cn_bin  ORDER BY pg_id;
 pg_id |   pg_val    | plain 
-------+-------------+-------
     2 | bin one     |   200
     4 | bin reorder |   400
     6 | bin subset  |      
(3 rows)

-- Dropped attnum: forces lazy populator to skip an attisdropped slot when
-- materializing the cache entry on first INSERT.
CALL clickhouse_perform('cn_admin', 'CREATE TABLE cn_test.td (
    ch_id Int32, ch_val String
) ENGINE = MergeTree ORDER BY (ch_id);');
CREATE FOREIGN TABLE cn_drop (
    discarded int,
    pg_id int OPTIONS (column_name 'ch_id'),
    pg_val text OPTIONS (column_name 'ch_val')
) SERVER cn_bin_loopback OPTIONS (table_name 'td');
ALTER FOREIGN TABLE cn_drop DROP COLUMN discarded;
INSERT INTO cn_drop VALUES (7, 'after drop');
SELECT * FROM cn_drop ORDER BY pg_id;
 pg_id |   pg_val   
-------+------------
     7 | after drop
(1 row)

-- Drop column after the cache is primed by an INSERT and SELECT, to confirm
-- the ATTNUM syscache invalidation purges stale CustomColumnInfo entries.
CALL clickhouse_perform('cn_admin', 'CREATE TABLE cn_test.tlate (
    discarded Nullable(Int32), ch_id Int32, ch_val String
) ENGINE = MergeTree ORDER BY (ch_id);');
CREATE FOREIGN TABLE cn_drop_late (
    discarded int,
    pg_id int OPTIONS (column_name 'ch_id'),
    pg_val text OPTIONS (column_name 'ch_val')
) SERVER cn_bin_loopback OPTIONS (table_name 'tlate');
INSERT INTO cn_drop_late VALUES (800, 8, 'before drop');
SELECT * FROM cn_drop_late ORDER BY pg_id;
 discarded | pg_id |   pg_val    
-----------+-------+-------------
       800 |     8 | before drop
(1 row)

ALTER FOREIGN TABLE cn_drop_late DROP COLUMN discarded;
INSERT INTO cn_drop_late VALUES (9, 'after drop');
SELECT * FROM cn_drop_late ORDER BY pg_id;
 pg_id |   pg_val    
-------+-------------
     8 | before drop
     9 | after drop
(2 rows)

-- Import names longer than PostgreSQL's 63 byte identifier limit.
-- Match truncated table names with LIMIT TO; preserve full names with column_name.
CALL clickhouse_perform('cn_admin', 'CREATE TABLE cn_test.table_with_a_name_longer_than_postgres_allows_for_identifiers_too (
    id Int32, column_with_a_name_longer_than_postgres_allows_for_identifiers_too String
) ENGINE = MergeTree ORDER BY (id);');
CREATE SCHEMA cn_long_http;
CREATE SCHEMA cn_long_bin;
IMPORT FOREIGN SCHEMA cn_test
    LIMIT TO ("table_with_a_name_longer_than_postgres_allows_for_identifiers_too")
    FROM SERVER cn_http_loopback INTO cn_long_http;
NOTICE:  identifier "table_with_a_name_longer_than_postgres_allows_for_identifiers_too" will be truncated to "table_with_a_name_longer_than_postgres_allows_for_identifiers_t"
NOTICE:  identifier "table_with_a_name_longer_than_postgres_allows_for_identifiers_too" will be truncated to "table_with_a_name_longer_than_postgres_allows_for_identifiers_t"
NOTICE:  identifier "column_with_a_name_longer_than_postgres_allows_for_identifiers_too" will be truncated to "column_with_a_name_longer_than_postgres_allows_for_identifiers_"
IMPORT FOREIGN SCHEMA cn_test
    LIMIT TO ("table_with_a_name_longer_than_postgres_allows_for_identifiers_too")
    FROM SERVER cn_bin_loopback INTO cn_long_bin;
NOTICE:  identifier "table_with_a_name_longer_than_postgres_allows_for_identifiers_too" will be truncated to "table_with_a_name_longer_than_postgres_allows_for_identifiers_t"
NOTICE:  identifier "table_with_a_name_longer_than_postgres_allows_for_identifiers_too" will be truncated to "table_with_a_name_longer_than_postgres_allows_for_identifiers_t"
NOTICE:  identifier "column_with_a_name_longer_than_postgres_allows_for_identifiers_too" will be truncated to "column_with_a_name_longer_than_postgres_allows_for_identifiers_"
\d cn_long_bin.*
                                                   Foreign table "cn_long_bin.table_with_a_name_longer_than_postgres_allows_for_identifiers_t"
                             Column                              |  Type   | Collation | Nullable | Default |                                    FDW options                                     
-----------------------------------------------------------------+---------+-----------+----------+---------+------------------------------------------------------------------------------------
 id                                                              | integer |           | not null |         | 
 column_with_a_name_longer_than_postgres_allows_for_identifiers_ | text    |           | not null |         | (column_name 'column_with_a_name_longer_than_postgres_allows_for_identifiers_too')
Server: cn_bin_loopback
FDW options: (database 'cn_test', table_name 'table_with_a_name_longer_than_postgres_allows_for_identifiers_too', engine 'MergeTree')

INSERT INTO cn_long_http."table_with_a_name_longer_than_postgres_allows_for_identifiers_too"
    VALUES (1, 'http long');
NOTICE:  identifier "table_with_a_name_longer_than_postgres_allows_for_identifiers_too" will be truncated to "table_with_a_name_longer_than_postgres_allows_for_identifiers_t"
INSERT INTO cn_long_bin."table_with_a_name_longer_than_postgres_allows_for_identifiers_too"
    VALUES (2, 'bin long');
NOTICE:  identifier "table_with_a_name_longer_than_postgres_allows_for_identifiers_too" will be truncated to "table_with_a_name_longer_than_postgres_allows_for_identifiers_t"
SELECT * FROM cn_long_http."table_with_a_name_longer_than_postgres_allows_for_identifiers_too"
    ORDER BY id;
NOTICE:  identifier "table_with_a_name_longer_than_postgres_allows_for_identifiers_too" will be truncated to "table_with_a_name_longer_than_postgres_allows_for_identifiers_t"
 id | column_with_a_name_longer_than_postgres_allows_for_identifiers_ 
----+-----------------------------------------------------------------
  1 | http long
  2 | bin long
(2 rows)

SELECT * FROM cn_long_bin."table_with_a_name_longer_than_postgres_allows_for_identifiers_too"
    ORDER BY id;
NOTICE:  identifier "table_with_a_name_longer_than_postgres_allows_for_identifiers_too" will be truncated to "table_with_a_name_longer_than_postgres_allows_for_identifiers_t"
 id | column_with_a_name_longer_than_postgres_allows_for_identifiers_ 
----+-----------------------------------------------------------------
  1 | http long
  2 | bin long
(2 rows)

DROP SCHEMA cn_long_http, cn_long_bin CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to foreign table cn_long_bin.table_with_a_name_longer_than_postgres_allows_for_identifiers_t
drop cascades to foreign table cn_long_http.table_with_a_name_longer_than_postgres_allows_for_identifiers_t
DROP USER MAPPING FOR CURRENT_USER SERVER cn_http_loopback;
DROP USER MAPPING FOR CURRENT_USER SERVER cn_bin_loopback;
CALL clickhouse_perform('cn_admin', 'DROP DATABASE cn_test');
DROP SERVER cn_http_loopback CASCADE;
NOTICE:  drop cascades to foreign table cn_http
DROP SERVER cn_bin_loopback CASCADE;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to foreign table cn_bin
drop cascades to foreign table cn_drop
drop cascades to foreign table cn_drop_late