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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs