Re: sequences vs. synchronous replication

On 2021/12/23 3:49, Tomas Vondra wrote:
> Attached is a patch tweaking WAL logging - in wal_level=minimal we do the same thing as now, in higher levels we log
everysequence fetch.
 

Thanks for the patch!

With the patch, I found that the regression test for sequences failed.

+            fetch = log = fetch;

This should be "log = fetch"?

On second thought, originally a sequence doesn't guarantee that the value already returned by nextval() will never be
returnedby subsequent nextval() after the server crash recovery. That is, nextval() may return the same value across
crashrecovery. Is this understanding right? For example, this case can happen if the server crashes after nextval()
returnedthe value but before WAL for the sequence was flushed to the permanent storage. So it's not a bug that sync
standbymay return the same value as already returned in the primary because the corresponding WAL has not been
replicatedyet, isn't it?
 

BTW, if the returned value is stored in database, the same value is guaranteed not to be returned again after the
servercrash or by sync standby. Because in that case the WAL of the transaction storing that value is flushed and
replicated.

> So I think this makes it acceptable / manageable. Of course, this means the values are much less monotonous (across
backends),but I don't think we really promised that. And I doubt anyone is really using sequences like this (just
nextval)in performance critical use cases.
 

I think that this approach is not acceptable to some users. So, if we actually adopt WAL-logging every sequence fetch,
alsohow about exposing SEQ_LOG_VALS as reloption for a sequence? If so, those who want to log every sequence fetch can
setthis SEQ_LOG_VALS reloption to 0. OTOH, those who prefer the current behavior in spite of the risk we're discussing
atthis thread can set the reloption to 32 like it is for now, for example.
 

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION