Vance Maverick wrote:
I want to create a table with the semantics of a log. There may be multiple concurrent writers, because log entries will be generated by triggers. And there will be multiple concurrent readers -- mostly remote processes watching the log over time. I'd like to guarantee that each of those readers will see the same, complete sequence of changes. For a first attempt, I created a table with a serial column (actually I created the sequence ID separately, but that's a detail). The readers connect remotely, scan through the table in sequence order, and remember the ID of the last row they read. When they read again, they start from after that ID -- the query is roughly SELECT * FROM logtable WHERE id > ? ORDER BY id But there's a problem with this -- the rows may not be inserted into the log in ID order. For example, if two concurrent sessions get the sequence IDs 3 and 4, the one with ID 4 might commit first. If a reader queries the table at that moment, it will see the sequence (1, 2, 4). Later, if the other session commits, a log entry with ID 3 will be added. The IDs will now be (1, 2, 4, 3) -- but if the reader comes back to poll the log again, it will miss entry 3. In order not to miss it, the reader would have to remember much more information than just a high-water mark. (I think it would have to remember the complete set of IDs it had processed already, and then scan the whole table to find entries not in the set.) Is there an approach that will give the semantics I need? In particular, I'd like a reader that has already read up to a certain point in the log to be able to restart at or near that same point, so it wouldn't have to reread everything. (I recognize that these log semantics are closer to those provided by files, not database tables. A file is an option I'll consider, but obviously it lacks transactional semantics.)
Use a timestamp column also.
