Re: Can Postgres Not Do This Safely ?!?

On Fri, Oct 29, 2010 at 8:58 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> 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(dot)klaver(at)gmail(dot)com
>

--
Karl Pickett