Re: Hot standby, running xacts, subtransactions

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.

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.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support