Re: Comparing txid and xmin (under BDR)

On 2015-05-12 06:06, Craig Ringer wrote:
> On 11 May 2015 at 21:10, Peter Mogensen <apm(at)one(dot)com> wrote:
>
>> So ... I can easily get the current txid of the SELECT transaction by
>> calling txid_current().
>>
> Note that by doing so, you force txid allocation for a read-only query that
> might otherwise not need one, which increases your txid burn rate and
> decreases time until you need to do wraparound-protection vacuuming.

Good point.

> Or, rather, it doesn't do anything different to what PostgreSQL its
> self does. I'm still not at all convinced that your desire to use
> transaction IDs for the cache invalidation stuff you're doing makes
> sense or will work sensibly even in a single-node environment. It's
> certainly not going to when comparing between nodes, especially in an
> async system.

You could be right.

Let's recap....

So - for an external cache in a system with high read-to-write ratio we
can get very high cache hit ratios by having no TTL, but doing cache
invalidation.
(about 98% i practice, so xid burn rate is probably not as bad as is may
sound)

Invalidation events have 1 problem. (apart from how they are generated).
There's a race condition.

We need to ensure that this sequence of events does not happen:
1) cache miss
2) DB read
3) DB invalidation event
4) cache invalidation applied
5) caching of the value read in 2)

In a standard PostgreSQl setup, we can stop 5) from happening, by
attaching txid_current() to the invalidation event in 3) and
txid_snapshot_xmin() to the cache read and put a tombstone in the cache
when doing invalidations.
(with a relative long TTL)

So ... when the value in 5) is about to get cached and if hits a
tombstone in the cache it is only cached if the tombstone txid is older
than the txid_snapshot_xmin of the new read - ie. if were sure that the
invalidation took place before the value we're about to cache.

This scheme should work with standard Postgres. Also read-only slaves.
But it won't work with BDR, since each node got its' own txid namespace.
Attaching txid_current() to an invalidation event has no meaning on
other nodes.

On the other hand. ... it the invalidation event is stored in a BDR
replicated table, then the xmin of the event row will have a meaning to
the local node. Which was the only way I found to get the invalidation
event placed in the local sequence of transactions.

/Peter