Re: Transaction question - Mailing list pgsql-general

On 7/11/12 2:07 PM, Adrian Klaver wrote:
> On 07/11/2012 07:01 AM, Jeff Ross wrote:
>> On 7/10/12 8:39 PM, Adrian Klaver wrote:
>>> On 07/10/2012 07:30 PM, Jeff Ross wrote:
>>>> On 7/10/12 6:21 PM, Adrian Klaver wrote:
>>>>> On 07/10/2012 01:06 PM, Jeff Ross wrote:
>>>>>> Hi all,
>>>>>>
>>>
>>>>>>
>>>>>> 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.
>
>>>
>>> So would it be possible to see the actual log sequence?
>>>
>> Absolutely.
>>
>
>> 2012-06-19 15:37:36.257256500 <www%wykids> LOG:  statement: INSERT INTO
>> survey_answers
>>
(srv_answers_srv_id,srv_answers_pp_id,srv_answers_question_id,srv_answers_answer,srv_answers_answer_text,srv_answers_rank,srv_answers_sub_question_id)

>>
>> VALUES ('2','25399','20','1',NULL,NULL,NULL)
>> 2012-06-19 15:37:36.258912500 <www%wykids> LOG: statement: commit
>> 2012-06-19 15:37:36.283752500 <www%wykids> LOG: statement: update
>> survey_response set srv_resp_submitted = now() where srv_resp_srv_id = 2
>> and srv_resp_pp_id = 25399
>
> Hmm, nothing stands out. Some bottom of the bag ideas:
>
> 1) Is there more than one survey_answers table in the database, in
> different schema?

No, just the public schema in this database.
>
> 2) When you are looking for the survey answers in the table are you
> connecting to the parent or child database in the replication setup?
>

Parent.

In answer to your next question, no, there are no insert triggers.

After finding this I looked at the other 2 surveys we've done and found
similar anomalies in each,  Each of those surveys have had 250 or so
respondents.  In the first, 2 records were updated with a submit time,
but with no corresponding inserts in survey_answers, in the second it
was 3.  The first survey was done at the end of last year and the logs
have rotated out for those transactions.  The second survey is yet
ongoing so I was able to do the same fix as before.

My worry is that if these transactions are failing silently, if indeed
that is what is happening, how many other transactions to other tables
are also silently failing?  This proved relatively easy to find because
the update statement was outside the transaction and when the number of
people with submitted entries did not match the number of people with
answers in survey_answers I started digging. Most of the time, though, I
trust transactions to either succeed or fail obviously with an error.

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