View Issue Details

IDProjectCategoryView StatusLast Update
0000157Pgpool-IIEnhancementpublic2016-05-13 09:06
ReporterskaurusAssigned Tot-ishii 
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionreopened 
PlatformLinuxOSCentosOS Version7
Product Version 
Target VersionFixed in Version 
Summary0000157: pgpool routes queries with "SET TRANSACTION READ ONLY" to master
DescriptionVersion 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:
BEGIN;
SELECT pg_sleep(15);
COMMIT;

But this transaction always routes to master:
BEGIN;
SET TRANSACTION READ ONLY;
SELECT pg_sleep(15);
COMMIT 1;

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.
TagsNo tags attached.

Activities

t-ishii

2015-11-22 16:42

developer   ~0000597

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

2015-12-02 22:02

developer   ~0000601

Fix done.

skaurus

2015-12-08 01:28

reporter   ~0000606

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?

Thanks again!

t-ishii

2015-12-08 10:56

developer   ~0000607

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

skaurus

2015-12-08 21:02

reporter   ~0000608

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.

skaurus

2015-12-09 00:54

reporter   ~0000609

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.

t-ishii

2015-12-09 07:49

developer   ~0000610

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.

skaurus

2015-12-09 08:04

reporter   ~0000611

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.

t-ishii

2015-12-10 13:20

developer   ~0000612

Oh ok. Totally agreed.

skaurus

2015-12-10 14:01

reporter   ~0000613

Great!
May we offer some kind of bounty for prompt fixing and releasing this functionality?

t-ishii

2015-12-10 17:11

developer   ~0000614

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 ishii@sraoss.co.jp.

skaurus

2016-04-16 00:10

reporter   ~0000786

Hi again!

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.

t-ishii

2016-05-10 18:00

developer   ~0000802

Can you please create a new ticket for this?

skaurus

2016-05-10 21:28

reporter   ~0000803

Sure. http://www.pgpool.net/mantisbt/view.php?id=195

Issue History

Date Modified Username Field Change
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