Re: Planning incompatibilities for Postgres 10.0

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Craig Ringer <craig(at)2ndquadrant(dot)com>, Simon Riggs <simon(at)2ndQuadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Stephen Frost <sfrost(at)snowman(dot)net>, Josh Berkus <josh(at)agliodbs(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Planning incompatibilities for Postgres 10.0
Date: 2013-05-28 21:26:06
Message-ID: 51A520EE.9090102@commandprompt.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2013-05-28 21:43:22 FIX: auto_explain docs
Previous Message Bruce Momjian 2013-05-28 21:20:02 Re: Planning incompatibilities for Postgres 10.0