Обсуждение: Why is a newly created index contains the invalidLSN?
Hello hackers.
I have a small question. While working on an incremental backup I noticed a
strange thing.
Newly created index is contains the invalid LSN (0/0).
Exmaple:
postgres=# select lsn from page_header(get_raw_page('test_a_idx2',0));
lsn
-----
0/0
(1 row)
Can you explain me why?
Thanks.
--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
On Thu, Aug 25, 2016 at 10:25 AM, Yury Zhuravlev
<u.zhuravlev@postgrespro.ru> wrote:
> I have a small question. While working on an incremental backup I noticed a
> strange thing.
> Newly created index is contains the invalid LSN (0/0).
> Exmaple:
> postgres=# select lsn from page_header(get_raw_page('test_a_idx2',0));
> lsn -----
> 0/0
> (1 row)
>
> Can you explain me why?
Why not?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Thu, Aug 25, 2016 at 1:13 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Thu, Aug 25, 2016 at 10:25 AM, Yury Zhuravlev
> <u.zhuravlev@postgrespro.ru> wrote:
>> I have a small question. While working on an incremental backup I noticed a
>> strange thing.
>> Newly created index is contains the invalid LSN (0/0).
>> Exmaple:
>> postgres=# select lsn from page_header(get_raw_page('test_a_idx2',0));
>> lsn -----
>> 0/0
>> (1 row)
>>
>> Can you explain me why?
>
> Why not?
Hmm, maybe I can do better than that. In general, the reason why we
set the page LSN is to prevent the page from being written before the
WAL record that most recently modified it is flushed to disk; this is
a necessary invariant of write-ahead logging. But for an index build
we don't need to generate any WAL records: if the system crashes, the
entire transaction will be considered to have aborted and the
relfilenode in which the new index was being written will be ignored,
so it doesn't matter whether we recover any of the contents of that
file. Since there's no WAL being generated, there's no need to set
LSNs on the pages.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Thu, Aug 25, 2016 at 7:55 PM, Yury Zhuravlev
<u.zhuravlev@postgrespro.ru> wrote:
> Hello hackers.
>
> I have a small question. While working on an incremental backup I noticed a
> strange thing.
> Newly created index is contains the invalid LSN (0/0).
> Exmaple:
> postgres=# select lsn from page_header(get_raw_page('test_a_idx2',0));
> lsn -----
> 0/0
> (1 row)
>
> Can you explain me why?
>
For some of the indexes like btree which are built outside shared
buffers, we don't write WAL unless wal_level >= REPLICA. I think
Robert has explained it very well how we handle the crash recovery
situation for such indexes. However, for some other indexes which
don't bypass shared buffers like BRIN, GIN we do write WAL for such
cases as well, so you must see LSN for those type of indexes. I am
less sure, if there will be any problem, if don't write WAL for those
indexes as well when wal_level < REPLICA.
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
Amit Kapila wrote: > On Thu, Aug 25, 2016 at 7:55 PM, Yury Zhuravlev > <u.zhuravlev@postgrespro.ru> wrote: >> Hello hackers. >> >> I have a small question. While working on an incremental >> backup I noticed a >> strange thing. >> Newly created index is contains the invalid LSN (0/0). >> Exmaple: ... > > For some of the indexes like btree which are built outside shared > buffers, we don't write WAL unless wal_level >= REPLICA. I think > Robert has explained it very well how we handle the crash recovery > situation for such indexes. However, for some other indexes which > don't bypass shared buffers like BRIN, GIN we do write WAL for such > cases as well, so you must see LSN for those type of indexes. I am > less sure, if there will be any problem, if don't write WAL for those > indexes as well when wal_level < REPLICA. > Thanks all. Now understand LSN strongly connected with WAL. However how difficult put last system LSN instead 0? It's not so important but will allow make use LSN more consistent. -- Yury Zhuravlev Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
On Fri, Aug 26, 2016 at 10:46 AM, Yury Zhuravlev <u.zhuravlev@postgrespro.ru> wrote: > Now understand LSN strongly connected with WAL. > However how difficult put last system LSN instead 0? > It's not so important but will allow make use LSN more consistent. There might be performance implications when flushing the index buffers, due to the need to check each one against the WAL flush point, where we now skip that check. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2016-08-26 18:46:42 +0300, Yury Zhuravlev wrote: > Thanks all. > Now understand LSN strongly connected with WAL. > However how difficult put last system LSN instead 0? > It's not so important but will allow make use LSN more consistent. Maybe explain why you're interested in page lsns, that'd perhaps allow us to give more meaningful feedback.
On 8/26/16 4:17 PM, Andres Freund wrote: > On 2016-08-26 18:46:42 +0300, Yury Zhuravlev wrote: >> Thanks all. >> Now understand LSN strongly connected with WAL. >> However how difficult put last system LSN instead 0? >> It's not so important but will allow make use LSN more consistent. > > Maybe explain why you're interested in page lsns, that'd perhaps allow > us to give more meaningful feedback. Yeah, especially since you mentioned this being for backups. I suspect you *want* those WAL records marked with 0, because that tells you that you can't rely on WAL when you back that data up. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461
Jim Nasby wrote: > Yeah, especially since you mentioned this being for backups. I > suspect you *want* those WAL records marked with 0, because that > tells you that you can't rely on WAL when you back that data up. Thanks, you right if you doing incremental backup you try compare every page LSN with last backup LSN. For my page tracking system (ptrack) it is secondary cheks but for classic pg_arman algorithm it is main approach. If Invalid LSN will be realy sign of broken page header it help for third-party applications. -- Yury Zhuravlev Postgres Professional: http://www.postgrespro.com The Russian Postgres Company