On Fri, Oct 29, 2010 at 8:58 AM, Adrian Klaver <adrian.klaver@xxxxxxxxx> wrote: > On Thursday 28 October 2010 7:04:48 pm Karl Pickett wrote: >> Hello Postgres Hackers, >> >> We have a simple 'event log' table that is insert only (by multiple >> concurrent clients). It has an integer primary key. We want to do >> incremental queries of this table every 5 minutes or so, i.e. "select >> * from events where id > LAST_ID_I_GOT" to insert into a separate >> reporting database. The problem is, this simple approach has a race >> that will forever skip uncommitted events. I.e., if 5000 was >> committed sooner than 4999, and we get 5000, we will never go back and >> get 4999 when it finally commits. How can we solve this? Basically >> it's a phantom row problem but it spans transactions. >> >> I looked at checking the internal 'xmin' column but the docs say that >> is 32 bit, and something like 'txid_current_snapshot' returns a 64 bit >> value. I don't get it. > > http://www.postgresql.org/docs/8.4/interactive/functions-info.html#FUNCTIONS-TXID-SNAPSHOT-PARTS > "The internal transaction ID type (xid) is 32 bits wide and wraps around every 4 > billion transactions. However, these functions export a 64-bit format that is > extended with an "epoch" counter so it will not wrap around during the life of > an installation. The data type used by these functions, txid_snapshot, stores > information about transaction ID visibility at a particular moment in time. Its > components are described in Table 9-53. " > > So: > Current snapshot: > > test=> SELECT txid_current_snapshot(); > txid_current_snapshot > ----------------------- > 5098:5098: > > xmin of snapshot: > test=> SELECT txid_snapshot_xmin(txid_current_snapshot()); > txid_snapshot_xmin > -------------------- > 5098 > (1 row) So what happens when txid_snapshot_xmin() goes over 4 billion, and the table's xmin doesn't? You can't compare a 32 bit value that rolls over to a 64 bit that doesn't. > > >> All I want to is make sure I skip over any >> rows that are newer than the oldest currently running transaction. >> Has nobody else run into this before? >> >> Thank you very much. >> >> -- >> Karl Pickett > > > > -- > Adrian Klaver > adrian.klaver@xxxxxxxxx > -- Karl Pickett -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
