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(dot)klaver(at)aklaver(dot)com