Re: Planning incompatibilities for Postgres 10.0

On Tue, May 28, 2013 at 02:26:06PM -0700, Joshua D. Drake wrote:
> >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.

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.

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

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