Search Postgresql Archives

Re: Transaction question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



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@xxxxxxxxx



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Programming PHP]     [Kernel Newbies]     [PHP Classes]     [Find Someone Nice]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux