| Subject: | BEGIN READ ONLY |
| Date: | Fri, 20 Nov 2015 21:41:12 +0300 |
| To: | bug-DBD-Pg [...] rt.cpan.org |
| From: | Дмитрий Шалашов <skaurus [...] gmail.com> |
Hi,
could you expose BEGIN READ ONLY functionality?
Why do we need this. We have following setup:
* PostgreSQL 9.4 master + slave
* pgpool-II 3.4.3 as connection pooler and load balancer
* pgpool set up in a way that almost all read queries are balanced to slave.
Problem lies within pgpool detection whether query writes or not (former
ones can't be routed to slave).
This query routes to slave:
BEGIN;
SELECT pg_sleep(15); -- pg_sleep used so it is easy to check where query is
running
COMMIT;
But this query routes to master:
BEGIN;
SET TRANSACTION TO READ ONLY; -- we need this to don't grow XIDs on
readonly queries
SELECT pg_sleep(15);
COMMIT;
And this query routes correctly (to slave) again:
BEGIN READ ONLY;
SELECT pg_sleep(15);
COMMIT;
I hesitate calling DB directly like this:
$dbh->do('BEGIN READ ONLY');
$dbh->do('SELECT pg_sleep(15));
$dbh->do('COMMIT');
because DBD::Pg have a lot going under its hood and I feel kinda insecure
missing all this. Maybe I'm wrong here?
Dmitry Shalashov, surfingbird.ru & relap.io