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

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