Re: modification time & transaction synchronisation problem

On 19/04/2010 4:51 PM, Craig Ringer wrote:

> There might be another possible approach that uses the system
> "xmin/xmax" fields of each tuple. That'd permit your incremental dumps
> to be done read-only, saving you a whole lot of expensive I/O and bloat.
> I'm just not sure what I'm thinking of will work yet.

Yeah. You can use a SERIALIZABLE transaction and
txid_current_snapshot(), which almost magically solves your problem. In
a single call it provides all the details about active and committed
transactions at the time of snapshot creation that you need. It even
gives you a list of transaction IDs for uncommitted transactions between
those ranges so old uncommitted transactions don't force you to
repeatedly dump data. That's all you need to know to do intelligent
incremental backup of a table.

I haven't written the actual test code, but what you should need to do
(according to my probably flawed understanding) is:

- Begin a read only SERIALIZABLE transaction
- Record txid_current_snapshot(), which you will need
for the next run. We'll call the value of the last
run's txid_current_snapshot() call 'txprev'.
- SELECT all rows that have:
tablename.xmin > current_snapshot_xmax(txprev) OR
tablename.xmin IN (txid_snapshot_xip(txprev))

( The above doesn't consider deletion. Deletion is never a fun
thing to handle in incremental backups/dumps. I'm not presently
sure how it should be handled or if it *can* be handled without
help from VACCUM and/or an ON DELETE trigger ).

If I get a chance, I'll play with this and see if it works in practice.

See:

http://www.postgresql.org/docs/current/interactive/functions-info.html
Table 9-52. Transaction IDs and snapshots

http://www.postgresql.org/docs/current/interactive/storage-page-layout.html

\df pg_catalog.tx*

--
Craig Ringer