Thread: After promote, DB can not UPDATE with SAVEPOINT (could not access status of transaction 1409172)

Hi all,

I've get found out that issue in my project.
Update in PRIMARY while pg_basebackup is performing,
Can cause the STANDBY could not UPDATE after promote.

In Standby Node, when first XID of a page come, pg_subtrans
must be extended by ExtendSUBTRANS function.

But if that XID created while pg_basebackup (with "-x" option)
was performing, it did not extend.

Due to that, after basebackup complete, start that DB and do
promote complete. It failed in SAVEPOINT UPDATE query like follows.

---
postgres=# BEGIN;
postgres=# SAVEPOINT testsavepoint;
postgres=# UPDATE test_tbl SET name = 'test';
ERROR:  could not access status of transaction 1409172
DETAIL:  Could not read from file "pg_subtrans/0015" at offset 131072: Success.
---

I've also confirm source and realize that,

When StartupXLOG call RecordKnownAssignedTransactionIds(in the "main redo apply loop"), the "standbyState" still
was STANDBY_INITIALIZED, so it is returned without goto
ExtendSUBTRANS (to check and extend space for pg_subtrans).

Therefor, after STANDBY promote, when UPDATE query made by
SAVEPOINT is executed, the process as follow is performed
and get the above ERROR in SimpleLruReadPage function.

AssignTransactionId => SubTransSetParent => SubTransSetParent => SimpleLruReadPage

I think that ExtendSUBTRANS must be called even if "standbyState"
in STANDBY_INITIALIZED in order to avoid the case above.

I also attach a patch. Could anyone confirm for me.

Regard,

---
Dang Minh Huong
NEC Soft,Ltd.
http://www.necsoft.com/eng/

Hi,

On 2013-08-22 22:17:40 +0900, =E3=83=80=E3=83=B3=E3=83=9F=E3=83=B3=E3=83=95=
=E3=83=BC=E3=83=B3 wrote:
> I've get found out that issue in my project.
> Update in PRIMARY while pg_basebackup is performing,=20
> Can cause the STANDBY could not UPDATE after promote.
>=20
> In Standby Node, when first XID of a page come, pg_subtrans=20
> must be extended by ExtendSUBTRANS function.
>=20
> But if that XID created while pg_basebackup (with "-x" option)=20
> was performing, it did not extend.
>=20
> Due to that, after basebackup complete, start that DB and do=20
> promote complete. It failed in SAVEPOINT UPDATE query like follows.
>=20
> ---
> postgres=3D# BEGIN;
> postgres=3D# SAVEPOINT testsavepoint;
> postgres=3D# UPDATE test_tbl SET name =3D 'test';
> ERROR:  could not access status of transaction 1409172
> DETAIL:  Could not read from file "pg_subtrans/0015" at offset 131072: =
Success.
> ---
>=20
> I've also confirm source and realize that,
>=20
> When StartupXLOG call RecordKnownAssignedTransactionIds
>  (in the "main redo apply loop"), the "standbyState" still=20
> was STANDBY_INITIALIZED, so it is returned without goto=20
> ExtendSUBTRANS (to check and extend space for pg_subtrans).
>=20
> Therefor, after STANDBY promote, when UPDATE query made by=20
> SAVEPOINT is executed, the process as follow is performed=20
> and get the above ERROR in SimpleLruReadPage function.
>=20
> AssignTransactionId =3D> SubTransSetParent =3D> SubTransSetParent =3D> =
SimpleLruReadPage=20
>=20
> I think that ExtendSUBTRANS must be called even if "standbyState"=20
> in STANDBY_INITIALIZED in order to avoid the case above.
>=20
> I also attach a patch. Could anyone confirm for me.

Nice diagnosis and thanks for the patch!

I think the the issue has already been fixed while investigating a
similar issue, could you verify that the current git version works for
you?

c.f. commit 99ee15b315c187045a95db7b27fd9d866aea93e0

Greetings,

Andres Freund

--=20
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Hi Andres,

Thank you for your prompt response.

I've confirmed in REL9_1_STABLE and could not reproduce that issue.
Also confirm the source and did not find problem with it.

Regard,

---
Dang Minh Huong
NEC Soft,Ltd.
http://www.necsoft.com/eng/

> -----Original Message-----
> From: Andres Freund [mailto:andres@2ndquadrant.com]
> Sent: Thursday, August 22, 2013 10:31 PM
> To: ダンミンフーン
> Cc: pgsql-bugs@postgresql.org; 'Akio Iwaasa'
> Subject: Re: [BUGS] After promote, DB can not UPDATE with SAVEPOINT (could not access status of transaction
> 1409172)
>
> Hi,
>
> On 2013-08-22 22:17:40 +0900, ダンミンフーン wrote:
> > I've get found out that issue in my project.
> > Update in PRIMARY while pg_basebackup is performing, Can cause the
> > STANDBY could not UPDATE after promote.
> >
> > In Standby Node, when first XID of a page come, pg_subtrans must be
> > extended by ExtendSUBTRANS function.
> >
> > But if that XID created while pg_basebackup (with "-x" option) was
> > performing, it did not extend.
> >
> > Due to that, after basebackup complete, start that DB and do promote
> > complete. It failed in SAVEPOINT UPDATE query like follows.
> >
> > ---
> > postgres=# BEGIN;
> > postgres=# SAVEPOINT testsavepoint;
> > postgres=# UPDATE test_tbl SET name = 'test';
> > ERROR:  could not access status of transaction 1409172
> > DETAIL:  Could not read from file "pg_subtrans/0015" at offset 131072: Success.
> > ---
> >
> > I've also confirm source and realize that,
> >
> > When StartupXLOG call RecordKnownAssignedTransactionIds  (in the "main
> > redo apply loop"), the "standbyState" still was STANDBY_INITIALIZED,
> > so it is returned without goto ExtendSUBTRANS (to check and extend
> > space for pg_subtrans).
> >
> > Therefor, after STANDBY promote, when UPDATE query made by SAVEPOINT
> > is executed, the process as follow is performed and get the above
> > ERROR in SimpleLruReadPage function.
> >
> > AssignTransactionId => SubTransSetParent => SubTransSetParent =>
> > SimpleLruReadPage
> >
> > I think that ExtendSUBTRANS must be called even if "standbyState"
> > in STANDBY_INITIALIZED in order to avoid the case above.
> >
> > I also attach a patch. Could anyone confirm for me.
>
> Nice diagnosis and thanks for the patch!
>
> I think the the issue has already been fixed while investigating a similar issue, could you verify that the
> current git version works for you?
>
> c.f. commit 99ee15b315c187045a95db7b27fd9d866aea93e0
>
> Greetings,
>
> Andres Freund
>
> --
>  Andres Freund                       http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services