View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0000157||Pgpool-II||Enhancement||public||2015-11-21 03:25||2016-05-13 09:06|
|Target Version||Fixed in Version|
|Summary||0000157: pgpool routes queries with "SET TRANSACTION READ ONLY" to master|
|Description||Version pgpool-II 3.4.3|
We have PostgreSQL master+slave setup and pgpool as connection pooler and load balancer.
We set up server weights in a way to route almost all read queries to slave (1 to 1000).
Now, this transaction correctly routes to slave:
But this transaction always routes to master:
SET TRANSACTION READ ONLY;
I used pg_sleep so it is easy to check where query is running.
Why do we need transactions around select? To set statement_timeout.
Why do we need "SET TRANSACTION READ ONLY"? To not increment XIDs on readonly requests.
Why don't we use BEGIN READ ONLY (which works correctly with pgpool)? Perl driver doesn't expose this functionality. We could call this "behind driver back" but don't like this option.
pgpool already have a hint to always route query to master. Besided fixing described behaviour it would be nice to have a hint to route to slave.
|Tags||No tags attached.|
Pgpool-II is dumb in this area. If any SET command is issued, pgpool-II starts to route SELECT to master node. This is because pgpool-II thinks that non SELECT query including SET command modifies data in the database (apparently not true in your case), subsequent SELECTs are better to run on the master node to avoid replication delay. Will fix.
In the mean time for the purpose "to have a hint to route to slave", may be we should have some new hints which tells pgpool-II to route a query to standby?
t-ishii - thanks for the fix!
I poked and experimented a little more. Turns out that "SET LOCAL statement_timeout TO ..." routes query to master too. Even inside "BEGIN READ ONLY". And this SET is exactly why I have to use transactions.
Could this be resolved too?
Maybe by routing "BEGIN READ ONLY" (or "SET TRANSACTION READ ONLY") always to slave.
Maybe by fixing another single case - "SET LOCAL statement_timeout TO ...".
Maybe by implementing the hint "route this query to slave no matter what".
And may I ask when you are planning to release next version?
Yes, "SET LOCAL statement_timeout TO ..." should not trigger "this transaction is writing" flag. Will fix.
In the future, maybe we should implement our own SET command to control query routing behavior.
SET query_route TO 'standby'; -- from now on all queries should be routed to one of standby servers if possible (write queries will be routed to master)
SET query_route TO 'standby[1-3]'; -- route to standby1, standby2, and standby3
Aren't standby's always read-only?
So if transaction explicitly declared as read-only (or there was a hint) it could be always routed to standby. And in case it's actually tries to perform a write PostgreSQL will just return an error.
Moreover, if transaction was declared as read-only (via "BEGIN READ ONLY" or "SET TRANSACTION READ ONLY") - PostgreSQL would not allow to run any write queries inside it.
So they are really safe to always route to standby.
I don't think so. Surely standbys are read only, but it does not necessarily means every read only query should go standby because primary server can accept read only query as well.
If you want to route any read only query to standby, you could always use weight parameter to set the primary's weight to 0.
Sorry for misformulation.
I meant that these transactions are really read only and safe to route anywhere. They should't be marked as write transactions no matter what.
||Oh ok. Totally agreed.|
May we offer some kind of bounty for prompt fixing and releasing this functionality?
||Thanks for the offering. I would like to contact you regarding this via email because this forum may not be appropriate for this kind of discussion. Or you could contact me via email. My email is email@example.com.|
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.
||Can you please create a new ticket for this?|
|2015-11-21 03:25||skaurus||New Issue|
|2015-11-22 16:42||t-ishii||Note Added: 0000597|
|2015-11-22 16:42||t-ishii||Assigned To||=> t-ishii|
|2015-11-22 16:42||t-ishii||Status||new => assigned|
|2015-12-02 22:01||t-ishii||Changeset attached||=> pgpool2 master f90dd79f|
|2015-12-02 22:01||t-ishii||Changeset attached||=> pgpool2 V3_1_STABLE ec3859b3|
|2015-12-02 22:01||t-ishii||Changeset attached||=> pgpool2 V3_0_STABLE 3dd89c17|
|2015-12-02 22:02||t-ishii||Note Added: 0000601|
|2015-12-02 22:03||t-ishii||Status||assigned => resolved|
|2015-12-08 01:28||skaurus||Note Added: 0000606|
|2015-12-08 01:28||skaurus||Status||resolved => feedback|
|2015-12-08 01:28||skaurus||Resolution||open => reopened|
|2015-12-08 10:56||t-ishii||Note Added: 0000607|
|2015-12-08 21:02||skaurus||Note Added: 0000608|
|2015-12-08 21:02||skaurus||Status||feedback => assigned|
|2015-12-09 00:54||skaurus||Note Added: 0000609|
|2015-12-09 07:49||t-ishii||Note Added: 0000610|
|2015-12-09 08:04||skaurus||Note Added: 0000611|
|2015-12-10 13:20||t-ishii||Note Added: 0000612|
|2015-12-10 14:01||skaurus||Note Added: 0000613|
|2015-12-10 17:11||t-ishii||Note Added: 0000614|
|2016-01-17 22:29||t-ishii||Status||assigned => resolved|
|2016-04-16 00:10||skaurus||Note Added: 0000786|
|2016-04-16 00:10||skaurus||Status||resolved => feedback|
|2016-05-10 18:00||t-ishii||Note Added: 0000802|
|2016-05-10 21:28||skaurus||Note Added: 0000803|
|2016-05-10 21:28||skaurus||Status||feedback => assigned|
|2016-05-13 09:06||t-ishii||Status||assigned => resolved|