search

yandex / odyssey

Scalable PostgreSQL connection pooler
BSD 3-Clause "New" or "Revised" License
3.2k stars 160 forks source link

Support read-only requests forwarding to a replica set #12

Open pmwkaa opened 6 years ago

Vladikamira commented 5 years ago

is there any progress on it? It will be very useful :)

x4m commented 5 years ago

@Vladikamira this functionality is not under development yet. First, I'm working on GA version. Second - I'll finish PAUSE\RESUME. Third - prepared statements. And only then - RW\RO routing.

Vladikamira commented 5 years ago

I see, thank you. Can't wait for this feature :)

ssi444 commented 2 months ago

I see, thank you. Can't wait for this feature :)

It looks like you won't be able to wait for her, alas

@Vladikamira this functionality is not under development yet. First, I'm working on GA version. Second - I'll finish PAUSE\RESUME. Third - prepared statements. And only then - RW\RO routing.

when should I expect this feature?

x4m commented 2 months ago

Hi! support for prepared statements, and, AFAIR, target session attrs for storages were shipped in 1.3.

ssi444 commented 2 months ago

The public is more interested in RW\RO routing, i think

reshke commented 2 months ago

The real question is how you expect this to work. There is no reliable way to determine if query executing would modify data or not. You cannot simply say that if user running SELECT - that's going to be RO. It is untrue for many cases, e.g. select func(), whehe func() actually modifies data, or Query rewrite (INSTEAD OF SELECT ...).

So, the only other way is to accept hint from user, like spqr does, or configure it statically. In odyssey, we support latter. You can configure default target session attr for storage, and odyssey will only pick non-primary nodes to execute statement against.

ssi444 commented 2 months ago

It may be worth sending only those requests to replicas that are guaranteed to be read. If this is a function (select func() ), then by default it is assumed that it can change the data. And how to make a parameter in pgpool2 so that the user can set safe functions himself, which are exactly reading. To begin with, do some minimum, and then, as experience and feedback accumulate, add functionality.

reshke commented 2 months ago

So you basically suggest query parsing and analyze inside Odyssey? Thats an huge change. We have done it in SPQR and we actually know what to do, but this requires really big amount of work to be done. At very first, we need to rework Odyssey internal logic for client-server attaching. We need to postpone this step until first query within transaction which we can decide where to route to. That requires us to handle situations like 

BEGIN;
SELECT * FROM myTable;

So, in case where no pipelining enabled, Odyssey should send CommandComplete & ReadyForQuery messages first, answering for BEGIN stmt. This involves enhanced transaction state handling logic, which is not here yet.