Here's how we solved the XID indexing problem at Skype.  We took
Slony-s xxid module and made it output 8-byte numbers by keeping
track of wraparound count.  Thus having stable relationship
between values.

It would be good to have such functionality officially in PostgreSQL
so that all replication (and other) projects can benefit from it.
Either as a contrib module or in core.

Actually I have started porting the module into core, because
I feel the administration will be easier that way.  I hoped
to post a preview of it and then finalize at Code Sprint, but
seems I won't get much time for random hacking before Summit.
Thus this post.

txid - 8 byte transaction ID's

Based on xxid module from Slony-I.  

The goal is to make PostgreSQL internal transaction ID and snapshot
data usable externally.  They cannot be used directly as the
internal 4-byte value wraps around and thus breaks indexing.

This module extends the internal value with wraparound cound (epoch).
It uses relaxed method for wraparound check.  There is a table
txid.epoch (epoch, last_value) which is used to check if the xid
is in current, next or previous epoch.  It requires only occasional
read-write access - ca. after 100k - 500k transactions.

Also it contains type 'txid_snapshot' and following functions:

get_current_txid() returns int8

  Current transaction ID

get_current_snapshot() returns txid_snapshot

  Current snapshot

get_snapshot_xmin( [snap] ) returns int8      -- 

  Smallest TXID in snapshot.  TXID's smaller than this
  are all visible in snapshot.  Without argument uses
  current snapshot.

get_snapshot_xmax( [snap] ) returns int8

  Largest TXID in snapshot.  TXID's starting from this one are
  all invisible in snapshot.  Without argument uses current snapshot.
get_snapshot_values( [snap] ) setof int8

  List of uncommitted TXID's in snapshot, that are invisible
  in snapshot.  Values are between xmin and xmax.  Without
  argument uses current snapshot.

txid_in_snapshot(id, snap) returns bool

  Is TXID visible in snapshot?

txid_not_in_snapshot(id, snap) returns bool

  Is TXID invisible in snapshot?


- it breaks when there are more than 2G tx'es between calls.
  this can only be solved by moving it into PostgreSQL core.
- functions that create new txid's should be 'security definers'
  thus better protecting txid_epoch table.

- After loading database from backup you should do:

  UPDATE txid.epoch SET epoch = epoch + 1,
                        last_value = (get_current_txid() & 0xFFFFFFFF);

  Again, if the txid could be in core, the support could be
  integraded into pg_dump that could dump function call:

    select txid_set_epoch(old_epoch, old_xid)

  that bumps current epoch if needed, thus guaranteeing that
  new txids will be greater than old ones.

Attachment: txid.tgz
Description: GNU Zip compressed data

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?


Reply via email to