View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000195 | Pgpool-II | Enhancement | public | 2016-05-10 21:28 | 2016-06-10 15:24 |
| Reporter | skaurus | Assigned To | t-ishii | ||
| Priority | normal | Severity | minor | Reproducibility | always |
| Status | closed | Resolution | open | ||
| Platform | Linux | ||||
| Summary | 0000195: pgpool routes queries with "SET TRANSACTION READ ONLY" to master | ||||
| Description | I 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. | ||||
| Tags | No tags attached. | ||||
|
|
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. |
|
|
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. |
|
|
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? |
|
|
Too bad :( |
|
|
I have started a discussion our mailing list. http://www.pgpool.net/pipermail/pgpool-hackers/2016-June/001616.html So close this item now... |
| 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 |
