Re: table versioning approach (not auditing)

On September 29, 2014 11:08:55 AM EDT, Jonathan Vanasco <postgres(at)2xlp(dot)com> wrote:
>
>- use a "transaction" log. every write session gets logged into the
>transaction table (serial, timestamp, user_id). all updates to the
>recorded tables include the transaction's serial. then there is a
>"transactions" table, that is just "transaction_serial , object_id ,
>object_action".

A newbie tangent question: how do you access the transaction serial? Is it txid_current() as listed in http://www.postgresql.org/docs/9.3/static/functions-info.html?

And how do you actually make use of that information? I know from Bruce Momjians's excellent MVCC talk <http://momjian.us/main/writings/pgsql/mvcc.pdf> that postgres internally has a secret txid column on each row; can you somehow query on the secret column? And does your implementation worry about multiple timelines?

My use case is dynamically allocated replication. Broadly, my algorithm is that for each client
1) download a full copy of the current table
2) keep the connection open and send deltas (which are just inserts and deletes, for me)

I need 2 to begin *as if immediately* after 1. txids sound like they are exactly what I need but without knowing how to handle them, I fudged it by opening a query for 1 and for 2 immediately after each other so that they should be plugged to the same txid but before reading them. There's definitely a race condition that will show under load, though. I think the correct algorithm is:

1) ask the current txid X
2) start buffering deltas with txid > X
3) download the table as of X
4) download the buffer of deltas and listen for future ones
--