Re: Transaction question

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?

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?

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


--
Adrian Klaver
adrian.klaver@gmail.com