Broke the connection when running 'insert into select' statement

[sophie-jeong]

I was trying to your examples also 'insert into select' statement. but my connection in postgresql was broken when running 'insert into select' statement. below is my proceeding. Please kindly check this out, and give me any feedback through the comment in this document.

• my env : centos 7 / postgresql12 / ClickHouse 20.6.4

* Clickhouse *** :) CREATE DATABASE test_database;

:) USE test_database; :) CREATE TABLE tax_bills_nyc ( bbl Int64 , owner_name String , tax_class String , tbea Float64 , bav Float64 , insertion_date DateTime MATERIALIZED now() ) ENGINE = MergeTree PARTITION BY tax_class ORDER BY ( owner_name ) ;

:) CREATE TABLE tax_bills ( bbl bigint , owner_name text ) ENGINE = MergeTree PARTITION BY bbl ORDER BY ( bbl ) ;

-- manual data uploading cat tax_bills_nyc.csv | clickhouse-client --input_format_allow_errors_num=10 --query="INSERT INTO test_database.tax_bills_nyc FORMAT CSV" * Clickhouse***

* PgSQL ***

create server clickhouse_svr foreign data wrapper clickhouse_fdw OPTIONS (dbname 'test_database', driver 'binary' ,host '127.0.0.1');

create user MAPPING FOR CURRENT_USER server clickhouse_svr ;

IMPORT FOREIGN SCHEMA "default" FROM SERVER clickhouse_svr INTO public;

IMPORT FOREIGN SCHEMA "test_database" FROM SERVER clickhouse_svr INTO public;

\d

               List of relations

Schema | Name | Type | Owner
--------+--------------------+---------------+---------- public | pg_stat_statements | view | postgres public | tax_bills | foreign table | postgres public | tax_bills_nyc | foreign table | postgres public | test_form | foreign table | postgres (4 rows)

select * from tax_biils_nyc_cp ;

bbl     | owner_name | tax_class | tbea  |  bav   |   insertion_date    

------------+------------+-----------+-------+--------+--------------------- 4000620001 | DVYA | d | 8961 | 80550 | 2020-11-18 03:36:50 1001200009 | LOXI | d | 72190 | 648900 | 2020-11-18 03:36:50 4157860094 | LROB | d | 13317 | 119700 | 2020-11-18 03:36:50 4123850237 | VYIE | d | 50 | 450 | 2020-11-18 03:36:50 4103150163 | WGZW | d | 2053 | 18450 | 2020-11-18 03:36:50 4123850237 | WGZW | d | 222 | 52413 | 2020-11-18 07:46:31 (6 rows)

insert into tax_bills ( select a.bbl , a.owner_name from tax_bills_nyc as a limit 1 ) ;

server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. !> \q * PgSQL ***

[ildus]

Looks like segfault, could you send me backtrace from your coredump?

[sophie-jeong]

Hello ildus :) I can give you my sys message like below It occurred when my connection was gone.

Nov 18 16:08:44 servername kernel: postgres[20398]: segfault at 10 ip 00007f98690f7d75 sp 00007ffd18eb0f00 error 4 in clickhouse_fdw.so[7f9869091000+ec000] Nov 18 16:08:52 servername kernel: postgres[27366]: segfault at 10 ip 00007f98690f7d75 sp 00007ffd18eb0f00 error 4 in clickhouse_fdw.so[7f9869091000+ec000] Nov 18 16:09:37 servername kernel: postgres[27596]: segfault at 10 ip 00007f98690f7d75 sp 00007ffd18eb0f00 error 4 in clickhouse_fdw.so[7f9869091000+ec000] Nov 18 16:10:45 servername kernel: postgres[29383]: segfault at 10 ip 00007f98690f7d75 sp 00007ffd18eb0f00 error 4 in clickhouse_fdw.so[7f9869091000+ec000] Nov 18 16:47:57 servername kernel: postgres[30910]: segfault at 10 ip 00007f98690f7d75 sp 00007ffd18eb0f00 error 4 in clickhouse_fdw.so[7f9869091000+ec000] Nov 18 16:58:20 servername kernel: postgres[29526]: segfault at 10 ip 00007f98690f7d75 sp 00007ffd18eb0f00 error 4 in clickhouse_fdw.so[7f9869091000+ec000] Nov 18 16:58:30 servername kernel: postgres[5965]: segfault at 10 ip 00007f98690f7d75 sp 00007ffd18eb0f00 error 4 in clickhouse_fdw.so[7f9869091000+ec000]

• additionally I wanna comment that * It work normally between below.
  1. postgresql table -> clickhouse table
  2. clickhouse table -> postgresql table

only this situation was given connection broken.

3. clickhouse table -> clickhouse table.

[ildus]

binary protocol is quite unstable since it depends on an external library, I would suggest to use http for now.

[ildus]

@sophie-jeong could you check this again? I made some changes since then.

[canghailan]

binary protocol is quite unstable since it depends on an external library, I would suggest to use http for now.

It works for me.

[gallyamow]

@sophie-jeong could you check this again? I made some changes since then.

The problem still appears. I noticed it appears only when I make insert to clickhouse-stored table when selecting from clickhouse-stored table. But does not appear when select by postgresql table.