On 11/23/21 5:22 AM, Dilip Kumar wrote: > On Tue, Nov 23, 2021 at 9:30 AM Tomas Vondra > <tomas.von...@enterprisedb.com> wrote: > >> On 11/22/21 9:42 PM, Jeremy Schneider wrote: >>> On 11/22/21 12:31, Tom Lane wrote: >>>> "Bossart, Nathan" <bossa...@amazon.com> writes: >>>>> I periodically hear rumblings about this behavior as well. At the >>>>> very least, it certainly ought to be documented if it isn't yet. I >>>>> wouldn't mind trying my hand at that. Perhaps we could also add a new >>>>> configuration parameter if users really want to take the performance >>>>> hit. >>>> >>>> A sequence's cache length is already configurable, no? >>>> >>> >>> Cache length isn't related to the problem here. >>> >>> The problem is that PostgreSQL sequences are entirely unsafe to use from >>> a durability perspective, unless there's DML in the same transaction. >>> >>> Users might normally think that "commit" makes things durable. >>> Unfortunately, IIUC, that's not true for sequences in PostgreSQL. >>> >> >> That's not what the example in this thread demonstrates, though. There's >> no COMMIT in that example, so it shows that we may discard the nextval() >> in uncommitted transactions. I fail to see how that's less durable than >> any other DML (e.g. we don't complain about INSERT not being durable if >> you don't commit the change). >> >> If you can show that the sequence goes back after a commit, that'd be an >> actual durability issue. > > I think at this thread[1], which claimed to get this issue even after > commit, I haven't tried it myself though. > > [1] > https://www.postgresql.org/message-id/flat/ea6485e3-98d0-24a7-094c-87f9d5f9b18f%40amazon.com#4cfe7217c829419b769339465e8c2915 >
I did try, and I haven't been able to reproduce that behavior (on master, at least). I see Tom speculated we may not flush WAL if a transaction only does nextval() in that other thread, but I don't think that's true. AFAICS if the nextval() call writes stuff to WAL, the RecordTransactionCommit will have wrote_xlog=true and valid XID. And so it'll do the usual usual XLogFlush() etc. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
