Summary of Changes

Hello @rahim-kanji, I'm Gemini Code Assist¹! I'm currently reviewing this pull request and will post my feedback shortly. In the meantime, here's a summary to help you and other reviewers quickly get up to speed!

This pull request addresses a client hang issue with PostgreSQL after a ProxySQL restart by improving the internal handling of PostgreSQL-related threads and global resources. The changes involve refactoring thread management within the PostgreSQL Host Group Manager, introducing a dedicated mutex for PostgreSQL global thread variables, correcting mutex usage in the PostgreSQL thread, and enhancing the overall shutdown procedure to ensure proper cleanup and deallocation of PostgreSQL components. These updates aim to prevent client hangs and facilitate smoother reconnection attempts.

Highlights

• Refactored PostgreSQL Thread Management: The management of PostgreSQL-related threads has been refactored within PgSQL_HostGroups_Manager, removing direct std::thread members and their associated join/delete calls. This suggests a shift in how these threads are managed or their responsibilities within the system.
• New Dedicated Mutex for PostgreSQL Threads: A new pthread_mutex_t, ext_glopth_mutex, has been introduced and initialized. This mutex is specifically designed to protect global variables related to PostgreSQL threads, ensuring thread-safe access.
• Corrected Mutex Usage in PgSQL Thread: The PgSQL_Thread::refresh_variables() method now correctly uses the new ext_glopth_mutex instead of the MySQL-specific ext_glomth_mutex. This resolves a potential synchronization issue by ensuring that PostgreSQL global thread variables are protected by the appropriate mutex.
• Improved PostgreSQL Component Shutdown: The shutdown procedure in main.cpp has been enhanced to properly deallocate GloPTH (PostgreSQL Thread Handler) and PgHGM (PostgreSQL Host Group Manager). This includes acquiring the new ext_glopth_mutex for GloPTH cleanup and explicitly calling PgHGM->shutdown() before deletion, ensuring a cleaner and more robust shutdown process for PostgreSQL components.

Code Review

This pull request correctly addresses a potential client hang on ProxySQL restart by implementing a proper shutdown sequence for the PostgreSQL modules. The introduction of a dedicated mutex for the PostgreSQL thread handler (ext_glopth_mutex) and centralizing the shutdown logic in main.cpp are excellent improvements for robustness and preventing race conditions. The changes are logical and well-targeted. I have one suggestion to further improve the consistency and safety of the shutdown code.