Re: ERRCODE_READ_ONLY_SQL_TRANSACTION

Hot Standby returns ERRCODE_READ_ONLY_SQL_TRANSACTION in most cases
for illegal actions on a standby.

There are two possible but not normally seen cases that give errors,
but don't set the correct sqlstate, which makes it difficult to
diagnose misdirected SQL from more normal SQL problems.

*Patch corrects this. Thanks to Dimitri for the report.

Backpatching to 9.0

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> Hot Standby returns ERRCODE_READ_ONLY_SQL_TRANSACTION in most cases
> for illegal actions on a standby.

> There are two possible but not normally seen cases that give errors,
> but don't set the correct sqlstate, which makes it difficult to
> diagnose misdirected SQL from more normal SQL problems.

> *Patch corrects this. Thanks to Dimitri for the report.

I don't think I like this patch: you are promoting what are and ought to
be very low-level internal sanity checks into user-facing errors (which
among other things will require translation effort for the messages).
I note that you didn't bother even to adjust the adjacent comments
saying this should never happen.

What I want to know is what code path led to these and why the read-only
ereport did not occur at a far higher level. If we have gaps in the RO
checking we should be fixing them somewhere else, not band-aiding here.

regards, tom lane

Hi,

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> Hot Standby returns ERRCODE_READ_ONLY_SQL_TRANSACTION in most cases
>> for illegal actions on a standby.
>
> I don't think I like this patch: you are promoting what are and ought to
> be very low-level internal sanity checks into user-facing errors (which
> among other things will require translation effort for the messages).

So it seems the last-9-2-CF deadline is making us a little too hasty.

Apparently as you're saying there's no way to exercise that code paths
from an SQL connection on a Hot Standby short of deploying a C coded
extension calling either GetNewTransactionId() or XLogInsert(), which
means it's out of scope.

My quest was figuring out if ERRCODE_READ_ONLY_SQL_TRANSACTION really is
trustworthy as a signal that you could transparently now redirect the
transaction to the master when seeing that in a “proxy” of some sort.

I felt that we were missing something simple here, but after review I
think we finally have all the pieces to achieve that with current 9.2
code base in fact.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Fri, Jan 13, 2012 at 8:55 AM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:

> I felt that we were missing something simple here, but after review I
> think we finally have all the pieces to achieve that with current 9.2
> code base in fact.

Good, patch revoked. No time wasted, it was worth checking.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services