Re: Hot standby, running xacts, subtransactions

On Tuesday 03 March 2009 03:22:30 Simon Riggs wrote:
> On Mon, 2009-03-02 at 21:11 -0500, Robert Treat wrote:
> > On Wednesday 25 February 2009 16:43:54 Simon Riggs wrote:
> > > On Wed, 2009-02-25 at 13:33 -0800, Josh Berkus wrote:
> > > > > You raised that as an annoyance previously because it means that
> > > > > connection in hot standby mode may be delayed in cases of heavy,
> > > > > repeated use of significant numbers of subtransactions.
> > > >
> > > > While most users still don't use explicit subtransactions at all,
> > > > wouldn't this also affect users who use large numbers of stored
> > > > procedures?
> > >
> > > If they regularly use more than 64 levels of nested EXCEPTION clauses
> > > *and* they start their base backups during heavy usage of those stored
> > > procedures, then yes.
> >
> > We have stored procedrues that loop over thousands of records, with
> > begin...exception blocks in that loop, so I think we do that. AFAICT
> > there's no way to tell if you have it wrong until you fire up the standby
> > (ie. you can't tell at the time you make your base backup), right ?
>
> That was supposed to be a simplification for phase one, not a barrier
> for all time.
>

Understood; I only mention it because it's usually good to know how quickly we
run into some of these cases that we don't think will be common.

> I'm changing that now, though the effect will be that in some cases we
> take longer before we accept connections. The initialisation
> requirements are that we have full knowledge of transactions in progress
> before we allow snapshots to be taken.
>

That seems pretty reasonable; hopefully people aren't setting up hot standy
machines as an emergency scaling technique :-)

--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com