> On 05/28/2013 04:05 PM, Bruce Momjian wrote:
>>
>> 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.
>>
>
> I don't know but I don't think so. Anything that is calling SET is
> going to run through the parser.
Right. SET command needs to be parsed by the parser. However, we
already have embedded parameters in the start up packet, which needs
to be recognized by pooler anyway. See "StartupMessage" section in:
http://www.postgresql.org/docs/9.3/static/protocol-message-formats.html
I am not sure backend currently permits to have
default_transaction_read_only = on in the startup packet or not
though.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp