Re: Dubugging an intermittent foreign key insert error with csvlog

On 9/16/19 3:16 PM, Jeff Ross wrote:
> 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.

Yes, RETURNING makes this sort of thing so much easier it pays to invest 
the time in making it part of the query.

> 
> Jeff
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com