Issue with PostgreSQL when Compiling with -O2 Flag

[ZhaoChunshan]

Hello, I'm currently trying to use PilotScope, but I encounter some runtime issues when compile with -O2. I followed the documentation to install PilotScope and PostgreSQL, but compile PostgreSQL with -O2 flag. (I note that in your document the -O0 flag is used.) Then, I write a simple Python script to send SQL queries to PostgreSQL and collect the results.

if __name__ == "__main__":
    pilot_config = PostgreSQLConfig(db_host="localhost", db_port=5432, db_user= "pilotscope", db_user_pwd="", db="stats", sql_execution_timeout=3600 * 24 * 7)
    data_interactor = PilotDataInteractor(pilot_config)

    sql = "SELECT COUNT(*) FROM votes as v, posts as p, badges as b, users as u WHERE u.Id = v.UserId AND u.Id = p.OwnerUserId AND u.Id = b.UserId AND p.Score>=0 AND p.Score<=30 AND p.CommentCount=0 AND p.CreationDate>=1280215831 AND p.CreationDate<=1409823910;"

    data_interactor.pull_record()
    data: PilotTransData = data_interactor.execute(sql)
    
    true_card = int(data.records["count"][0])
    print(true_card )

However, when I run the Python code, the PostgreSQL side crushed, and the log is :

2024-09-23 16:29:11.994 CST [105862] LOG:  server process (PID 105946) was terminated by signal 11: Segmentation fault
2024-09-23 16:29:11.994 CST [105862] DETAIL:  Failed process was running: /*pilotscope {"anchor": {"SUBQUERY_CARD_PULL_ANCHOR": {"enable": true, "name": "SUBQUERY_CARD_PULL_ANCHOR", "enable_parameterized_subquery": false}, "RECORD_PULL_ANCHOR": {"enable": true, "name": "RECORD_PULL_ANCHOR"}, "EXECUTION_TIME_PULL_ANCHOR": {"enable": true, "name": "EXECUTION_TIME_PULL_ANCHOR"}}, "enableTerminate": false, "enableReceiveData": true, "port": 45997, "url": "localhost", "tid": "140202409346816"} pilotscope*/ SELECT COUNT(*) FROM votes as v, posts as p, badges as b, users as u WHERE u.Id = v.UserId AND u.Id = p.OwnerUserId AND u.Id = b.UserId AND p.Score>=0 AND p.Score<=30 AND p.CommentCount=0 AND p.CreationDate>=1280215831 AND p.CreationDate<=1409823910;
2024-09-23 16:29:11.994 CST [105862] LOG:  terminating any other active server processes
2024-09-23 16:29:11.994 CST [105867] WARNING:  terminating connection because of crash of another server process
2024-09-23 16:29:11.994 CST [105867] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2024-09-23 16:29:11.994 CST [105867] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2024-09-23 16:29:11.999 CST [108827] FATAL:  the database system is in recovery mode
2024-09-23 16:29:12.000 CST [105862] LOG:  all server processes terminated; reinitializing
2024-09-23 16:29:13.272 CST [108841] LOG:  database system was interrupted; last known up at 2024-09-23 16:28:56 CST
2024-09-23 16:29:13.389 CST [108841] LOG:  database system was not properly shut down; automatic recovery in progress
2024-09-23 16:29:13.391 CST [108841] LOG:  redo starts at 0/FF75EF0
2024-09-23 16:29:13.391 CST [108841] LOG:  invalid record length at 0/FF75F28: wanted 24, got 0
2024-09-23 16:29:13.391 CST [108841] LOG:  redo done at 0/FF75EF0
2024-09-23 16:29:13.571 CST [105862] LOG:  database system is ready to accept connections

And the python side error is:

psycopg2.OperationalError: server closed the connection unexpectedly . This probably means the server terminated abnormally before or while processing the request.
psycopg2.OperationalError: connection to server at "localhost" (::1), port 5555 failed: FATAL:  the database system is in recovery mode

Interestingly, everything works well when I compile PostgreSQL with the -O0 or -O1 flag. I am curious if I overlooked any crucial points or if there might be a bug in your modified PostgreSQL code. I eagerly await your kind response. Thank you!

[spiceandwolf]

同一样的问题，在docker环境下无论使用-O0-O1-O2都不能解决