Re: Planning incompatibilities for Postgres 10.0

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.

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.

Sincerely,

Joshua D. Drake

--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats