Content-Type: multipart/mixed; boundary="------------gogDTIAEvsi10SC20jP961SP"
Message-ID: <5774a616-8e87-d7a5-7856-6db0ff1a78b0@oss.nttdata.com>
Date: Thu, 23 Dec 2021 02:50:49 +0900
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:91.0)
 Gecko/20100101 Thunderbird/91.4.0
Subject: Re: sequences vs. synchronous replication
Content-Language: en-US
To: Tomas Vondra <tomas.vondra@enterprisedb.com>,
 Amit Kapila <amit.kapila16@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <712cad46-a9c8-1389-aef8-faf0203c9be9@enterprisedb.com>
 <CAA4eK1KN-Pqe1y5w0uXU2Ttn8DK4WfqgwAbCRbcDt4yzQ88wRA@mail.gmail.com>
 <4450cd74-1a41-ec03-83ee-fcabc183715d@enterprisedb.com>
 <6597cf88-a499-e5be-e027-62753b197500@oss.nttdata.com>
 <5c788a12-def6-daab-ac08-c006dfbaed7d@enterprisedb.com>
From: Fujii Masao <masao.fujii@oss.nttdata.com>
In-Reply-To: <5c788a12-def6-daab-ac08-c006dfbaed7d@enterprisedb.com>
Archived-At: 
 <https://www.postgresql.org/message-id/5774a616-8e87-d7a5-7856-6db0ff1a78b0%40oss.nttdata.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------gogDTIAEvsi10SC20jP961SP
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit


On 2021/12/22 21:11, Tomas Vondra wrote:
> Interesting idea, but I think it has a couple of issues :-(

Thanks for the review!

> 1) We'd need to know the LSN of the last WAL record for any given sequence, and we'd need to communicate that between backends somehow. Which seems rather tricky to do without affecting performance.

How about using the page lsn for the sequence? nextval_internal() already uses that to check whether it's less than or equal to checkpoint redo location.


> 2) SyncRepWaitForLSN() is used only in commit-like situations, and it's a simple wait, not a decision to write more WAL. Environments without sync replicas are affected by this too - yes, the data loss issue is not there, but the amount of WAL is still increased.

How about reusing only a part of code in SyncRepWaitForLSN()? Attached is the PoC patch that implemented what I'm thinking.


> IIRC sync_standby_names can change while a transaction is running, even just right before commit, at which point we can't just go back in time and generate WAL for sequences accessed earlier. But we still need to ensure the sequence is properly replicated.

Yes. In the PoC patch, SyncRepNeedsWait() still checks sync_standbys_defined and uses SyncRepWaitMode. But they should not be checked nor used because their values can be changed on the fly, as you pointed out. Probably SyncRepNeedsWait() will need to be changed so that it doesn't use them.


> 3) I don't think it'd actually reduce the amount of WAL records in environments with many sessions (incrementing the same sequence). In those cases the WAL (generated by in-progress xact from another session) is likely to not be flushed, so we'd generate the extra WAL record. (And if the other backends would need flush LSN of this new WAL record, which would make it more likely they have to generate WAL too.)

With the PoC patch, only when previous transaction that executed nextval() and caused WAL record is aborted, subsequent nextval() generates additional WAL record. So this approach can reduce WAL volume than other approach?

In the PoC patch, to reduce WAL volume more, it might be better to make nextval_internal() update XactLastRecEnd and assign XID rather than emitting new WAL record, when SyncRepNeedsWait() returns true.

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
--------------gogDTIAEvsi10SC20jP961SP
Content-Type: text/plain; charset=UTF-8; name="sequence_log.patch"
Content-Disposition: attachment; filename="sequence_log.patch"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL3NlcXVlbmNlLmMgYi9zcmMvYmFj
a2VuZC9jb21tYW5kcy9zZXF1ZW5jZS5jCmluZGV4IDcyYmZkYzA3YTQuLjM4Y2Q1NWI4MWEg
MTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL3NlcXVlbmNlLmMKKysrIGIvc3Jj
L2JhY2tlbmQvY29tbWFuZHMvc2VxdWVuY2UuYwpAQCAtMzcsNiArMzcsNyBAQAogI2luY2x1
ZGUgIm1pc2NhZG1pbi5oIgogI2luY2x1ZGUgIm5vZGVzL21ha2VmdW5jcy5oIgogI2luY2x1
ZGUgInBhcnNlci9wYXJzZV90eXBlLmgiCisjaW5jbHVkZSAicmVwbGljYXRpb24vc3luY3Jl
cC5oIgogI2luY2x1ZGUgInN0b3JhZ2UvbG1nci5oIgogI2luY2x1ZGUgInN0b3JhZ2UvcHJv
Yy5oIgogI2luY2x1ZGUgInN0b3JhZ2Uvc21nci5oIgpAQCAtNjc2LDggKzY3Nyw5IEBAIG5l
eHR2YWxfaW50ZXJuYWwoT2lkIHJlbGlkLCBib29sIGNoZWNrX3Blcm1pc3Npb25zKQogCWVs
c2UKIAl7CiAJCVhMb2dSZWNQdHIJcmVkb3B0ciA9IEdldFJlZG9SZWNQdHIoKTsKKwkJWExv
Z1JlY1B0cglwYWdlbHNuID0gUGFnZUdldExTTihwYWdlKTsKIAotCQlpZiAoUGFnZUdldExT
TihwYWdlKSA8PSByZWRvcHRyKQorCQlpZiAocGFnZWxzbiA8PSByZWRvcHRyIHx8IFN5bmNS
ZXBOZWVkc1dhaXQocGFnZWxzbikpCiAJCXsKIAkJCS8qIGxhc3QgdXBkYXRlIG9mIHNlcSB3
YXMgYmVmb3JlIGNoZWNrcG9pbnQgKi8KIAkJCWZldGNoID0gbG9nID0gZmV0Y2ggKyBTRVFf
TE9HX1ZBTFM7CmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9yZXBsaWNhdGlvbi9zeW5jcmVw
LmMgYi9zcmMvYmFja2VuZC9yZXBsaWNhdGlvbi9zeW5jcmVwLmMKaW5kZXggYmRiYzllZjg0
NC4uNTg5MzY0Y2I1OCAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvcmVwbGljYXRpb24vc3lu
Y3JlcC5jCisrKyBiL3NyYy9iYWNrZW5kL3JlcGxpY2F0aW9uL3N5bmNyZXAuYwpAQCAtMTMw
LDYgKzEzMCwzNCBAQCBzdGF0aWMgYm9vbCBTeW5jUmVwUXVldWVJc09yZGVyZWRCeUxTTihp
bnQgbW9kZSk7CiAgKiA9PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09
PT09PT09PT09PT09PT09PT09PQogICovCiAKK2Jvb2wKK1N5bmNSZXBOZWVkc1dhaXQoWExv
Z1JlY1B0ciBsc24pCit7CisJaW50CQkJbW9kZTsKKworCWlmICghU3luY1JlcFJlcXVlc3Rl
ZCgpIHx8CisJCSEoKHZvbGF0aWxlIFdhbFNuZEN0bERhdGEgKikgV2FsU25kQ3RsKS0+c3lu
Y19zdGFuZGJ5c19kZWZpbmVkKQorCQlyZXR1cm4gZmFsc2U7CisKKwltb2RlID0gTWluKFN5
bmNSZXBXYWl0TW9kZSwgU1lOQ19SRVBfV0FJVF9GTFVTSCk7CisKKwlBc3NlcnQoU0hNUXVl
dWVJc0RldGFjaGVkKCYoTXlQcm9jLT5zeW5jUmVwTGlua3MpKSk7CisJQXNzZXJ0KFdhbFNu
ZEN0bCAhPSBOVUxMKTsKKworCUxXTG9ja0FjcXVpcmUoU3luY1JlcExvY2ssIExXX1NIQVJF
RCk7CisJQXNzZXJ0KE15UHJvYy0+c3luY1JlcFN0YXRlID09IFNZTkNfUkVQX05PVF9XQUlU
SU5HKTsKKworCWlmICghV2FsU25kQ3RsLT5zeW5jX3N0YW5kYnlzX2RlZmluZWQgfHwKKwkJ
bHNuIDw9IFdhbFNuZEN0bC0+bHNuW21vZGVdKQorCXsKKwkJTFdMb2NrUmVsZWFzZShTeW5j
UmVwTG9jayk7CisJCXJldHVybiBmYWxzZTsKKwl9CisKKwlMV0xvY2tSZWxlYXNlKFN5bmNS
ZXBMb2NrKTsKKwlyZXR1cm4gdHJ1ZTsKK30KKwogLyoKICAqIFdhaXQgZm9yIHN5bmNocm9u
b3VzIHJlcGxpY2F0aW9uLCBpZiByZXF1ZXN0ZWQgYnkgdXNlci4KICAqCmRpZmYgLS1naXQg
YS9zcmMvaW5jbHVkZS9yZXBsaWNhdGlvbi9zeW5jcmVwLmggYi9zcmMvaW5jbHVkZS9yZXBs
aWNhdGlvbi9zeW5jcmVwLmgKaW5kZXggNDI2NmFmZGU4Yi4uYjA4ZjNmMzJkMSAxMDA2NDQK
LS0tIGEvc3JjL2luY2x1ZGUvcmVwbGljYXRpb24vc3luY3JlcC5oCisrKyBiL3NyYy9pbmNs
dWRlL3JlcGxpY2F0aW9uL3N5bmNyZXAuaApAQCAtODIsNiArODIsNyBAQCBleHRlcm4gY2hh
ciAqc3luY3JlcF9wYXJzZV9lcnJvcl9tc2c7CiBleHRlcm4gY2hhciAqU3luY1JlcFN0YW5k
YnlOYW1lczsKIAogLyogY2FsbGVkIGJ5IHVzZXIgYmFja2VuZCAqLworZXh0ZXJuIGJvb2wg
U3luY1JlcE5lZWRzV2FpdChYTG9nUmVjUHRyIGxzbik7CiBleHRlcm4gdm9pZCBTeW5jUmVw
V2FpdEZvckxTTihYTG9nUmVjUHRyIGxzbiwgYm9vbCBjb21taXQpOwogCiAvKiBjYWxsZWQg
YXQgYmFja2VuZCBleGl0ICovCg==
--------------gogDTIAEvsi10SC20jP961SP--