[Pgpool-general] R: Time differences

[Simone Tregnago]

> 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'

sure: the where clause with the timestamp could return different results from 
different servers
 
> So, here goes my 2 questions:
> - Is there a way to make sure both servers get the same time from
> ntp?

I don't know if you could sync your servers better, but I think that doesn't
solve the problem.

> 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.

No, you can't expect that different servers returns exactly the same time
because:
1. the server's clocks could always have a difference of few ms
2. the two servers should receive and process the query at the same time:
   you can understand that this is impossible

> - 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,

mmm, I'm not sure, but I think there isn't

> So, what's the advice on this one?

Don't know, if you can split the query in two, with the first query you
get the timestamp with a simple select (wich is executed only on one server),
then you'll put this value in the second query

Regards,
Simone Tregnago