Re: Planning incompatibilities for Postgres 10.0

On Tue, May 28, 2013 at 03:39:10PM -0700, Joshua D. Drake wrote:
>
> On 05/28/2013 03:36 PM, Bruce Momjian wrote:
>
> >>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.
> >
> >Well, you could do SET TRANSACTION READ ONLY, and that would prevent any
> >write transactions. You could assume it is a read query, and get the
> >error and resubmit on the master if that happens, but that sounds
> >inefficient. I thought you were asking for something where you could
> >submit a query and it would report back as read/write or read-only.
>
> No I am suggesting something that before anything happens with the
> parser, the protocol knows what is up. So things like pgpool-ii
> don't even need a parser, it just knows it is a read only query
> because the protocol says so.

Oh, that is an interesting idea. The application is indicating it is
read-only via the protocol, and poolers can optimize that. Don't we
have the ability to pass arbitrary GUC values back through the protocol,
e.g. transaction_read_only? If not, that might be a way to do this
cleanly.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +