[Pgpool-general] Time differences

[Bruno Lustosa]

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 <bruno at lustosa.net>
http://www.lustosa.net/