Re: Transaction question

On 7/10/12 6:21 PM, Adrian Klaver wrote:
> On 07/10/2012 01:06 PM, Jeff Ross wrote:
>> Hi all,
>>
>> I have an anomaly on my hands that I'm at a loss to understand.
>>
>> We recently ran a small survey where participants were required to
>> answer all the questions. After validation for skipped questions,
>> mis-ranking answers that had to be ranked and so on, I did all of the
>> inserts to the survey_answers table inside a transaction block followed
>> by a commit. Immediately after, I updated the survey_response table
>> and set a timestamp to show the submitted time.
>>
>> In 3 of the 38 responses, the logs show the inserts with no database
>> errors followed by the update statement, however, there are no entries
>> for that person in the survey_answers table. It is as if the
>> transaction rolled back, but if so, that fact is not in the logs.
>
>>
>> After finding these anomalies, I cut and pasted the insert statements
>> from the logs into a file and inserted them manually with psql -f. No
>> errors, so I can't see why the transaction should have rolled back, if
>> indeed that is what happened. Is it possible for a transaction to
>> silently fail?
>>
>> This is 9.1.3 running on OpenBSD and on a BBU RAID 1 mirror. It is the
>> master in a hot-standy setup. Logging is set to "all". I have no
>> reason to believe (yet, anyway) that this is some sort of hardware
>> problem as I see no indication of that anywhere else.
>>
>> Thanks for any and all ideas!
>
> For your initial attempt everything was done in one session?

All the inserts were done in one session, yes.
>
> I am also confused by this:
> 2012-06-19 15:37:36.258912500 <www%wykids> LOG: statement: commit
> 2012-06-19 15:29:11.573396500 <www%wykids> LOG: statement: update
> survey_response set srv_resp_submitted = now() where srv_resp_srv_id =
> 2 and srv_resp_pp_id = 25399
>
> Note the time stamps. Is this the actual log?

Crap. I noticed that I'd somehow got another update statement when I
pasted into the e-mail so I altered the srv_resp_pp_id to match rather
than go get the real entry from the logs again. That is for sure my bad
and I apologize! The sequence of events are as I described, though.
The inserts happen inside a transaction, the update happens immediately
after the commit.

Jeff
>
>>
>> Jeff Ross
>> Wyoming Children's Action Alliance
>>
>
>