Refactored and Optimized Query Cache with PgSQL Support

[rahim-kanji]

Introduced separate query processors for MySQL and PostgreSQL.

• Added Query Cache Support for PgSQL:

  • Introduced new variables for PgSQL Query Cache management:

    • pgsql-query_cache_size_MB
    • pgsql-query_cache_soft_ttl_pct
    • pgsql-query_cache_stores_empty_result
    • pgsql-query_cache_handle_warnings (not yet utilized)

  • Overwriting Transaction Status:

    • Sending current transaction state to the client when a result is fetched from the query cache. At the moment, this doesn’t distinguish between normal and error transaction states—both are treated same.

  • Updated behavior:

    • Prioritizes pgsql_thread_query_cache_size_MB over pgsqlthreadthreshold_resultset_size when determining if query results should be cached. (same is now true for MySQL)

• Cache Lifetime Management:

  • Replaced manual reference counting with std::shared_ptr to simplify cache entry lifecycle management. This improves memory handling and reduces complexity.

• Unified Purging Logic:

  • Refactored the query cache purging logic to use a single thread for both MySQL and PgSQL cache entries.

• Code Cleanup:

  • Removed unnecessary methods and data members to make the code more maintainable and less cluttered.

• Performance Optimization:

  • Optimized memory management, reducing memory footprint and improving performance.

• TAP Tests:

  • pgsql-query_cache_soft_ttl_pct-t.cpp: This test verifies that query cache entries are refreshed upon reaching 'pgsql-query_cache_soft_ttl_pct' value.
  • pgsql-query_cache_test-t.cpp: This test verifies complete functionality of query cache module.
  • mysql-reg_test_4723_query_cache_stores_empty_result-t.cpp: regression test

Closes #4689 Closes #4723

[mirostauder]

Can one of the admins verify this patch?