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

From: ダンミンフーン <dangminh-huong(at)mxm(dot)nes(dot)nec(dot)co(dot)jp>
To: <pgsql-bugs(at)postgresql(dot)org>
Cc: "'Akio Iwaasa'" <iwaasa(at)mxs(dot)nes(dot)nec(dot)co(dot)jp>
Subject: After promote, DB can not UPDATE with SAVEPOINT (could not access status of transaction 1409172)
Date: 2013-08-22 13:17:40
Message-ID: 008301ce9f39$fa67c520$ef374f60$@mxm.nes.nec.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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/

Attachment Content-Type Size
subtrans.patch application/octet-stream 630 bytes

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andres Freund 2013-08-22 13:30:35 Re: After promote, DB can not UPDATE with SAVEPOINT (could not access status of transaction 1409172)
Previous Message Bruce Momjian 2013-08-21 11:33:40 Re: [BUGS] BUG #8335: trim() un-document behaviour