Re: Planning incompatibilities for Postgres 10.0

On Tue, May 28, 2013 at 4:26 PM, Joshua D. Drake <jd(at)commandprompt(dot)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