davi vidal wrote:
> On Wed, Jan 21, 2015 at 1:07 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
> wrote:
>
> > davividal@gmail.com wrote:
> >
> > > My scenario: I have a daily physical backup from my production server
> > > (9.1).
> > > I create a 9.1 cluster from it and upgrade it to 9.4 daily. After that, I
> > > deploy a bunch of changes using sqitch (sqitch.org). Again: I do it on a
> > > daily basis, but this is the first time I faced this issue:
> > >
> > > $ sqitch deploy -t sandbox views/sistema.sf_guard_user@HEAD
> > > Deploying changes through views/sistema.sf_guard_user to sandbox
> > > + data_migration/rate_plan.payment_policies ...............
> > > psql:sql/deploy/data_migration/rate_plan.payment_policies.sql:21: ERROR:
> > > could not access status of transaction 116940611
> > > DETAIL: Could not read from file "pg_subtrans/06F8" at offset 90112:
> > > Success.
> > > CONTEXT: while updating tuple (6302,20) in relation
> > "rate_daily_policies"
So the mechanism for this error is that heap_update calls
XactLockTableWait (either directly or through MultiXactIdWait), which in
turn calls SubTransGetParent(). That's the source of the error (you can
see because the error context callback is installed with an oper code of
XLTW_Update, which is what generates that precise wording.)
Question is why can't it read from the file? Presumably the file hasn't
been extended to that point yet, or maybe it doesn't even exist. Maybe
a glitch in pg_upgrade? I don't know how does the new cluster acquire
its pg_subtrans files after pg_upgrade. Can you paste a listing of
$PGDATA/pg_subtrans?
The file should be created by ExtendSUBTRANS(), but this routine only
works at the first ID of each page. I guess if you upgrade and the XID
counter ends up in the middle of a page, then try to read the parent XID
of some subxact, the file extension won't have happened. To workaround
the problem, I think, you could just read a few hundred transactions
until the next pg_subtrans page extension takes place.
I haven't tested this hypothesis.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
