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@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +