Thread: Row not immediately visible after commit

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.klaver@aklaver.com

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.klaver@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.klaver@aklaver.com