[pgpool-hackers: 3470] SET command and query cache in a transaction

[Tatsuo Ishii]

I am taking care of this issue:
https://www.pgpool.net/mantisbt/view.php?id=561

The user's claim is, SET command in an explicit transaction prevents
him from using query cache. That's an intended behavior. When an
non-SELECT queries run in an explicit transaction, Pgpool-II sets
"writing transaction flag". In general, the flag will be kept until
current transaction ends. The reason why we introduced the flag is
that query cache invalidation will not take effect until the
transaction commits. For example:

SELECT * FROM t1; <-- query cache entry created.
BEGIN;
INSERT INTO t1 VALUES(1); <-- pending query cache invalidation is prepared for t1 and "writing flag" is set.
SELECT * FROM t1; <-- query cache is not used since writing flag is on.
END; <-- query cache for t1 is removed.

The writing flag is set not only when DML runs but SET command
runs. This is because I thought there might be certain case that SET
command affects query cache entry. But now I am not sure I was
right. For example if we have a cache entry for "SELECT session_user"
and if we issue "SET SESSION AUTHORIZATION foo", the cache for SELECT
session_user" is not valid any more. But the session_user function is
"stable", Pgpool-II will not cache the SELECT result anyway.

In summary it seems we can safely change Pgpool-II so that SET command
does not set writing transaction flag. If so, I would like to propose
to do the change for at least upcoming 4.2 even for existing releases.

BTW, writing transaction flag affects to not only query cache, but load
balancing. If the writing flag is set, the Pgpool-II will not
distribute a ready query to load balance node. The change above will
affect the behavior of load balance.

Thoughts/Comments?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp