[Bucardo-general] Design of new txntime field

[Jon Jensen]

On Thu, 7 Sep 2017, Greg Sabino Mullane wrote:

> So the problem is that timestamp has a maximum precision of 6, which in 
> rare cases can lead to two processes generating the same value for 
> now(), and thus creating identical rows in bucardo_delta - which really 
> confuses Bucardo, as it expects txntime to be unique.

Remind me: Do we need to use the transaction start timestamp, that is, 
now()? Or should we be using clock_timestamp() to get the wallclock time 
at each invocation?

> A higher resolution timestamp would work,

Postgres doesn't offer any higher-resolution timestamp, does it? I thought 
microsecond was it.

I suppose you could ask the OS for a nanosecond time, and pass it in.

> Thus, the current best idea I have is to combine the timestamp
> with txid_current, which basically adds some precision to the
> timestamp, e.g. now() || txid_current()::text
>
> This means we have to store it as a text, and not a timestamp field,
> but it still sorts, and we don't do any other time-based queries on
> the field, other than the vac process, but that can be changed.

That won't sort unless you pad txid_current(), right? As a stringified 
integer with varying digits, it'll depend on number of digits.

And actually the timestamp won't sort right as a string either, since it 
will have fewer digits sometimes when the microseconds end with 0 and are 
truncated, leaving fewer string digits.

Both fixable problems, of course.

Jon


-- 
Jon Jensen
End Point Corporation
https://www.endpoint.com/