Hello, list! I've run into a problem I don't know the proper solution. I have pgpool-II running with 2 backends. Ocasionally, I receive the following from a cron job:
Warning: pg_query(): Query failed: ERROR: pgpool detected difference of the number of update tuples HINT: check data consistency between master and other db node in ...... I checked the line, and it's a simple update query: UPDATE public.transporte SET ativo = false, ts = CURRENT_TIMESTAMP WHERE age(CURRENT_TIMESTAMP,ts) > '2 hours' So, I thought the only reason this query would update a different number of tuples would be if the timestamps on the server would be different. This table is relatively small (about 50 or so inserts every hour). Just to make sure of the reason, I tried to create a small table (with just a timestamp, defaulting to current_timestamp) and then inserting into it from pgpool. Now, when I connect to the backends, I see a difference in the timestamp, in the order of 100ms. And surely enough, if the timestamp on the table has this difference, by the time the update query is run, there will also be a time difference that might make it not update a few tuples. Both servers run ntp, and the difference between them is about 50ms (one is at -29ms and the other at +23ms). I know this problem would be best fixed at the OS level (using ntp), and not on pgpool, but still, this could pose a problem to the database. So, here goes my 2 questions: - Is there a way to make sure both servers get the same time from ntp? They are getting their time from an internal server, which gets its time from a stratum 2 public ntp. I don't know much about ntp configuration, but until now, I thought a 50ms difference would be fine. - Would there be a way, on pgpool, to make sure the timestamps would be exactly the same? I mean, before sending an insert to both servers, pgpool could check the timestamp on one of them, and then modify the query to make any default timestamp fields get the value previously checked. This way, they would get exactly the same, because it was explicitly inserted, and not let to default. I know this might cause a myriad of other problems, and not even sure it is possible to solve it like this. So, what's the advice on this one? Thanks! -- Bruno Lustosa <[EMAIL PROTECTED]> http://www.lustosa.net/ _______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
