Reimplement the session mode

[auxten]

Current chDB(till v2.0.2) relys on temp disk storage to keep the session data.
Everytime session.query runs almost everything in memory will be recreated and reinit which caused a lot of state problems like:

• Memory engine could not keep state in session.
• how to query postgre table？ #225
• Create table view/ import from pandas in a session #258
• Is there a way to have system.query_log in chdb ? #261
• When no DB used we get: Code: 57. DB::Exception: Directory for table data data/default/testtable/ already exists. (TABLE_ALREADY_EXISTS) chdb-node#18

Also some feature implemented and bugs walked around before also need a better way to fix:

• Fix changing default database by USE in session mode #133
• Allow path in dbapi connect #176
• Fix set clause not work through chdb session #207

Some chDB contributor also gave a try to make session better:

• Long living client #108

^{Originally posted by l1t1 February 7, 2024}

:) create table a engine=Memory as select 1 a;

0.11995077133178711
:) select * from a;
Code: 60. DB::Exception: Table _local.a does not exist. (UNKNOWN_TABLE)

0.11473512649536133

Here is how clickhouse-local interactive mode works:

root@0a8b55995b6e:/auxten/chdb/tests# ./ch24.5/usr/bin/clickhouse
ClickHouse local version 24.5.1.1763 (official build).

0a8b55995b6e :) create table a engine=Memory as select 1 a;

CREATE TABLE a
ENGINE = Memory
AS SELECT 1 AS a

Query id: 967a5d72-bb39-4a42-8a11-a108eda2a5d9

Ok.

0 rows in set. Elapsed: 0.008 sec. 

0a8b55995b6e :) select * from a;

SELECT *
FROM a

Query id: e5be6b9b-b752-4418-8753-adb8cc69a127

   ┌─a─┐
1. │ 1 │
   └───┘

1 row in set. Elapsed: 0.008 sec. 

The good part are:

1. Better support for states like 'Memory Table Engine', 'UDF', 'SET', 'USE'
2. Less tricky code to handle default database and 'SET', 'USE' statements
3. Without load tables and do init on every query function call, Performance should be much better than current implementation

[auxten]

All issues with label Session is related to this feature

#225
#258
#261
chdb-io/chdb-node#18

[auxten]

Here is the rough plan:

1. Upgrade engine to 24.8 or newer, as ClickHouse engine did a lot of bugfix and optimization on clickhouse-local in recent 3 releases
2. Better handling BackgroundSchedulePool and all kinds of Context
3. Reimplement the session mode

[ruslandoga]

👋 @auxten

Would it also support stale parts cleanup #107?

And, in general, full use of MegreTrees (writing, parallel reading, etc.) https://github.com/orgs/chdb-io/discussions/210#discussioncomment-8863924

[auxten]

👋 @auxten

Would it also support stale parts cleanup #107?

Yes, I think so

[raystyle]

👋 @auxten
Would it also support stale parts cleanup #107?

Yes, I think so

If it is not possible to merge partitions, each append operation will cause the partition to keep expanding.

[auxten]

👋 @auxten
Would it also support stale parts cleanup #107?

Yes, I think so

If it is not possible to merge partitions, each append operation will cause the partition to keep expanding.

Yes, It's a serious problem in current impl. In this new impl, there will be some background threads doing the merge work periodically.