Re: Can Postgres Not Do This Safely ?!?

On 10/29/2010 10:04 AM, 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.

Essentially, in a table populated by concurrent inserts by many
transactions which may commit out of order, you want a way to say "get
me all tuples inserted since I last asked". Or, really "get me all
tuples that became visible since I last looked".

I've never found a good answer for this. If there is one, it'd be
wonderful for trigger-free, efficient replication of individual tables
using batches. The problem is that - because of commit ordering - there
doesn't seem to be any way to match a *range* of transactions, you have
to match a *list* of individual transaction IDs that committed since you
last ran. And you need a way to generate and maintain that list,
preferably only including transactions that touched the table of interest.

> 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.   All I want to is make sure I skip over any
> rows that are newer than the oldest currently running transaction.

Oh, so you don't care if you get the same tuple multiple times if
there's some old, long running transaction? You're just trying to avoid
repeatedly grabbing the REALLY old stuff?

In that case xmin is what you want. You may have to be aware of xid
wraparound issues, but I don't know much more about dealing with them
than the term.

--
Craig Ringer