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:

RDS is also a modified version of Postgresql, just not as modified as Aurora.

On 3/29/21 7:06 PM, Steve Baldwin 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.klaver@aklaver.com
<mailto: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 <mailto:adrian.klaver@aklaver.com>

--
Angular momentum makes the world go 'round.

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.baldwin@gmail.com>
wrote: