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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
