View Issue Details

IDProjectCategoryView StatusLast Update
0000195Pgpool-IIEnhancementpublic2016-06-10 15:24
ReporterskaurusAssigned Tot-ishii 
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionopen 
PlatformLinuxOSOS Version
Product Version 
Target VersionFixed in Version 
Summary0000195: pgpool routes queries with "SET TRANSACTION READ ONLY" to master
DescriptionI feel that issue 157 is not fully addressed yet.
pgpool still fails to recognize some clearly read-only queries as such.

I'm playing with pgpool-II 3.5.1 and while I see that this transaction may route to standby:

BEGIN; SET TRANSACTION READ ONLY; SELECT pg_sleep(10); COMMIT;

this is always routed to master:

BEGIN; SET TRANSACTION READ ONLY; SET LOCAL statement_timeout TO 15000; SELECT pg_sleep(10); COMMIT;

But any transaction which was declared as READ ONLY is safe to route to standby, since PostgreSQL itself would disallow any data change in this transaction.

I hope this could be improved.
TagsNo tags attached.

Activities

t-ishii

2016-05-16 14:31

developer   ~0000820

This is because "SET LOCAL statement_timeout TO 15000;" was called. pgpool-II recognizes current transaction as "writing" if non SELECT command (except "SET RANSACTION READ ONLY;") is used. And load balancing is off in a writing transaction.

skaurus

2016-05-16 21:47

reporter   ~0000822

I thought we agreed in previous ticket that transaction with "SET TRANSACTION READ ONLY" or started via "BEGIN READ ONLY" is in fact read only no matter what. Because Postgres itself will enforce it.

t-ishii

2016-05-19 05:54

developer   ~0000827

I just wanted to say that if "SET TRANSACTION READ ONLY" (or its variants) should make pgpool-II to treat every SELECT to be read only, we need to change a lot of current pgpool-II behavior. SET command is just one of such examples.

One of other examples is, "black_function_list" and "white_function_list". If function "a" is defined as a write function in the black_function_list or the white_function_list, should we load balance it if "SET TRANSACTION READ ONLY" is issued?

skaurus

2016-05-19 06:17

reporter   ~0000828

Too bad :(

t-ishii

2016-06-10 15:24

developer   ~0000855

I have started a discussion our mailing list.

http://www.pgpool.net/pipermail/pgpool-hackers/2016-June/001616.html

So close this item now...

Issue History

Date Modified Username Field Change
2016-05-10 21:28 skaurus New Issue
2016-05-16 11:34 t-ishii Assigned To => t-ishii
2016-05-16 11:34 t-ishii Status new => assigned
2016-05-16 14:31 t-ishii Note Added: 0000820
2016-05-16 14:35 t-ishii Status assigned => feedback
2016-05-16 21:47 skaurus Note Added: 0000822
2016-05-16 21:47 skaurus Status feedback => assigned
2016-05-19 05:54 t-ishii Note Added: 0000827
2016-05-19 05:55 t-ishii Status assigned => feedback
2016-05-19 06:17 skaurus Note Added: 0000828
2016-05-19 06:17 skaurus Status feedback => assigned
2016-06-10 15:24 t-ishii Note Added: 0000855
2016-06-10 15:24 t-ishii Status assigned => closed