Re: BUG #12617: DETAIL: Could not read from file "pg_subtrans/06F8" at offset 90112: Success.

On Thu, Jan 22, 2015 at 1:18 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> davi vidal wrote:
> > On Wed, Jan 21, 2015 at 1:07 PM, Alvaro Herrera <
> alvherre(at)2ndquadrant(dot)com>
> > wrote:
> >
> > > davividal(at)gmail(dot)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(dot)sf_guard_user(at)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 9.1 cluster is empty.

# ls -a /var/lib/pgsql/9.4/data/pg_subtrans/
. .. 06F8

# file /var/lib/pgsql/9.4/data/pg_subtrans/06F8
/var/lib/pgsql/9.4/data/pg_subtrans/06F8: data

> 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.
>
>
Sorry, I didn't understand anything.
But I can do as much tests as you want/need.

Davi