On Tue, May 28, 2013 at 4:26 PM, Joshua D. Drake <j...@commandprompt.com> wrote: > > On 05/28/2013 02:18 PM, Bruce Momjian wrote: > >>> I would like to see the ability to define if a query is read only at >>> the protocol level, so that load balances that speak libpq can know >>> what to do with the query without parsing it. >> >> >> Sounds nice, but how would we do that? That would require libpq to know >> it, right? Do we pass anything back after parsing but before execution? >> Could it be optional? What about functions that modify the database >> --- isn't that only known at execution time? > > > I can't speak to the actual C code that would be required but from a user > space, I could see something like this: > > con = psycopg2.connect(database='testdb', user='test', transaction-type='r') > > Thus when the connection is made, before anything else is done, we know it > is a read only connection and therefore any load balancer speaking libpq > would also know it is a read only. The default of course would be r/w and > you would use a different connection handler for r/w or w queries. >
you can do that today already, kind-of create an entry in pgbouncer that connect to host=read-only.servers.dns and make read-only.servers.dns to point to more than 1 ip. then when the application wants to do load balancing, just connect to the entry that points to read-only.servers.dns and let the magic happens which would be great is this to happen transparently to the application > The other option would be to do it on query execute but that doesn't seem as > efficient as it would have to be parsed each time. Although it would still > be better than reading the actual SQL. > another idea, as someone else mentioned, and i think has been discussed bedore is a function that says if the query is r-o or not... maybe even exporting the plan so we don't need to replan again... Not sure if that is possible, just hand waving... -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitaciĆ³n Phone: +593 4 5107566 Cell: +593 987171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers