Re: Dubugging an intermittent foreign key insert error with csvlog

On 9/16/19 4:07 PM, Adrian Klaver wrote:
> On 9/16/19 1:46 PM, Jeff Ross wrote:

>>
>> The csvlog snip shows what I believe are 2 simultaneous but separate 
>> sessions and the session that attempts to insert into the cargo_det 
>> table is not the same session that inserted into the load_det table. 
>> That's what my hunch is but what is unclear to me is if those separate 
>> sessions are also in separate transactions.
> 
> To me it looks like the INSERT into load_det and into cargo_det are 
> occurring in the same transaction(934281062). The part that would 
> concern me is that:
> 
> select last_value from load_det_id_seq
> 
> occurs in different transactions and sessions. From here:
> 
> https://www.postgresql.org/docs/11/sql-createsequence.html
> 
> "Also, last_value will reflect the latest value reserved by any session, 
> whether or not it has yet been returned by nextval."
> 
> Especially as the error is coming from a different 
> transaction(934281063) and session then the INSERTs. I'm guessing that 
> there is cross talk on the sequence number fetch and application to 
> cargo_det.
> 

Thank you Adrian--I think my hunch was basically correct then.  Now all 
I need to do is figure out why we have multiple sessions and 
transactions.  Or it might be time to skip ahead and get rid of the 
last_value query.

Jeff