Re: Row not immediately visible after commit

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(dot)baldwin(at)gmail(dot)com>
wrote:

> Thanks Adrian. This is 'vanilla' postgres as far as I know (unlike their
> postgres-flavoured Aurora product).
>
> b2bc_owner(at)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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>>
>