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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
