[pgpool-hackers: 4525] SQL level query cache invalidation

[Tatsuo Ishii]

I have proposed a new pcp command to invalidate entire query cache[1].
This is useful for pgpool admin but for users I feel we need more
restricted form of similar functionality make the admin's life easier.

- Allow a user to remove query cache only created by the user.

- The infrastructure to provide such a service should be SQL level
  pgpool command because the user has been already authenticated.

So I would like to propose a new SQL level command:
"PGPOOL INVALIDATE QUERY CACHE 'sql_statement'"

The command tries to remove the query cache created by the session
user in the current database.  If it successfully remove the query
cache, it emits a notice message "query cache successfully removed". If
the requested query cache was not found, it emits a notice message
"query cache not found".

The sql_statement must exactly matches the query string when the query
cache was created.

There are some limitations:

- This only works for query cache created by simple query protocol
  without SQL level PREPARE, or with PREPARE but without any
  parameter.

- This only works for query cache created by extended query protocol
  without any parameter.

- If SET ROLE or SESSION AUTHORIZATION command was executed in the
  session, the command does not work because it will disable query
  cache feature in the current session.
  
Comments/suggestions are welcome.

[1] https://www.pgpool.net/pipermail/pgpool-hackers/2024-October/004525.html

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp