Re: Transaction question - Mailing list pgsql-general

On 7/11/12 3:52 PM, Adrian Klaver wrote:
> 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?
>

Here's the table definition:

jross@nirvana:/home/jross $ psql wykids
psql (9.1.4, server 9.1.3)
Type "help" for help.

wykids=# \d survey_answers
                                           Table "public.survey_answers"
            Column            |  Type | Modifiers
-----------------------------+---------+-------------------------------------------------------------------------
  srv_answers_id              | integer | not null default
nextval('survey_answers_srv_answers_id_seq'::regclass)
  srv_answers_srv_id          | integer | not null
  srv_answers_pp_id           | integer | not null
  srv_answers_question_id     | integer | not null
  srv_answers_answer          | integer |
  srv_answers_answer_text     | text    |
  srv_answers_rank            | integer |
  srv_answers_sub_question_id | integer |
Indexes:
     "survey_answers_pkey" PRIMARY KEY, btree (srv_answers_id)
Foreign-key constraints:
     "survey_answers_srv_answers_answer_fkey" FOREIGN KEY
(srv_answers_answer) REFERENCES survey_possible_answers(srv_pos_answers_id)
     "survey_answers_srv_answers_pp_id_fkey" FOREIGN KEY
(srv_answers_pp_id) REFERENCES people(pp_id)
     "survey_answers_srv_answers_question_id_fkey" FOREIGN KEY
(srv_answers_question_id) REFERENCES survey_questions(srv_question_id)
     "survey_answers_srv_answers_srv_id_fkey" FOREIGN KEY
(srv_answers_srv_id) REFERENCES surveys(srv_id)

wykids=#

I haven't re-indexed that table but somehow I find it hard to believe
that a reindex can make those rows appear. I just tried it on my
development server--no change.

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