Re: Transaction question - Mailing list pgsql-general

On 07/11/2012 02:41 PM, Jeff Ross wrote:
> 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.

Is there an index on this table?
If so have you tried a REINDEX?

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


--
Adrian Klaver
adrian.klaver@gmail.com