Ok, I believe I have found an explanation, and it is due to a logic error, not due to anything funky happening with the database. Please excuse the noise.
Steve On Tue, Mar 30, 2021 at 11:06 AM Steve Baldwin <steve.bald...@gmail.com> wrote: > Thanks Adrian. This is 'vanilla' postgres as far as I know (unlike their > postgres-flavoured Aurora product). > > b2bc_owner@b2bcreditonline=> select version(); > version > > --------------------------------------------------------------------------------------------------------- > PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 > 20150623 (Red Hat 4.8.5-11), 64-bit > > Steve > > On Tue, Mar 30, 2021 at 10:52 AM Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > >> On 3/29/21 4:39 PM, Steve Baldwin wrote: >> > Hi all, >> > >> > I know this is going to sound weird/unbelievable, but I'm trying to >> come >> > up with an explanation for what I've observed. >> > >> > First, a couple of data points. The instance is running on AWS RDS and >> > is on version 13.1. All my timestamps and elapsed times were taken from >> > the postgres log (converted to my local tz). >> > >> > 2021-03-30 05:47:40.989+11 Session A begins a new transaction >> > 2021-03-30 05:47:41.006+11 Session A inserts a single row into table A >> > 2021-03-30 05:47:41.031+11 Session A inserts two rows into table B >> > 2021-03-30 05:47:41.039+11 Session A commits (duration = 3.022 ms) >> > >> > 2021-03-30 05:47:41.082+11 Session B begins a new transaction >> > 2021-03-30 05:47:41.083+11 Session B fetches one of the inserted rows >> > from table B >> > 2021-03-30 05:47:41.085+11 Session B attempts to fetch the inserted row >> > from table A using the primary key. Fetch returns zero rows. >> > 2021-03-30 05:47:41.087+11 Session B aborts the transaction with >> rollback >> > >> > 2021-03-30 05:47:42.143+11 Session C begins a new transaction >> > 2021-03-30 05:47:42.146+11 Session C fetches the same row as session B >> above >> > 2021-03-30 05:47:42.228+11 Session C attempts the same query on table A >> > as session B above. The fetch returns 1 row, and session C continues >> > processing. >> > >> > I can't imagine how Session B could fail to fetch the row from table A >> > given that the commit has completed prior to Session B starting its >> > transaction. >> > >> > Any suggestions? >> >> Ask AWS support. >> >> > >> > Thanks, >> > >> > Steve >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> >
