Row not immediately visible after commit

2021-03-29 Thread Steve Baldwin 
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?

Thanks,

Steve

Re: Row not immediately visible after commit

2021-03-29 Thread Adrian Klaver 

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

Re: Row not immediately visible after commit

2021-03-29 Thread Steve Baldwin 
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 
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
>

Re: Row not immediately visible after commit

2021-03-29 Thread Ron 

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 > 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 



--
Angular momentum makes the world go 'round.

Re: Row not immediately visible after commit

2021-03-29 Thread Steve Baldwin 
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 
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 
> 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
>>
>