1

What I am trying to do

I am developing a web service, which runs in multiple server instances, all accessing the same RDBMS (PostgreSQL). While the database is needed for persistence, it contains very little data, which is why every server instance has a cache of all the data. Further the application is really simple in that it only ever inserts new rows in rather simple tables and selects that data in a scheduled fashion from all server instances (no updates or changes... only inserts and reads).

The way it is currently implemented

basically I have a table which roughly looks like this:

id BIGSERIAL,
creation_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- further data columns...

The server is doing something like this every couple of seconds (pseudocode):

get all rows with creation_timestamp > lastMaxTimestamp
lastMaxTimestamp = max timestamp for all data just retrieved
insert new rows into application cache

The issue I am running into

The application skips certain rows when updating the caches. I analyzed the issue and figured out, that the problem is caused in the following way:

  1. one server instance is creating a new row in the context of a transaction. An id for the new row is retrieved from the associated sequence (id=n) and the creation_timestamp (with value ts_1) is set.
  2. another server does the same in the context of a different transaction. The new row in this transaction gets id=n+1 and a creation_timestamp ts_2 (where ts_1 < ts_2).
  3. transaction 2 finishes before transaction 1
  4. one of the servers executes a "select all rows with creation_timestamp > lastMaxTimestamp". It gets row n+1, but not n1. It sets lastMaxTimestamp to ts_2.
  5. transaction 1 completes
  6. some time later the server from step 4 executes "select all rows with creation_timestamp > lastMaxTimestamp" again. But since lastMaxTimestamp=ts_2 and ts_2>ts_1 the row n will never be read on that server.

Note: CURRENT_TIMESTAMP has the same value during a transaction, which is the transaction start time.

So the application gets inconsistent data into its cache and can't get new rows based on the insertion timestamp OR based on the sequence id. Transaction isolation levels don't really change anything about the situation, since the problem is created in essence by transaction 2 finishing before transaction 1.

My question

Am I missing something? I am thinking there must be a straightforward way to get all new rows of a RDBMS, but I can't come up with a simple solution... at least with a simple solution that is consistent. Extensive locking (e.g. of tables) wouldn't be acceptable because of performance reasons. Simply trying to ensure to get all ids from that sequence seems like a) a complicated solution and b) can't be done easily, since rollbacks during transactions can happen (which would lead to sequence ids not being used).

Anyone has the solution?

Wolf
  • 892
  • 2
  • 8
  • 22
  • Use a `serial`/`generated always as identity` column instead. – Gordon Linoff Mar 19 '20 at 19:07
  • 1
    @GordonLinoff: As mentioned in the question, the application already uses BIGSERIAL for the id, which doesn't provide a possible solution. When selecting for "id > maxId" the same issue occur, since a row with id=n+1 can be created _before_ the row with id=n. – Wolf Mar 19 '20 at 19:22
  • If you don't like the fact that `current_timestamp` represents the start of the transaction, then why not use `clock_timestamp()` –  Mar 19 '20 at 21:20
  • 1
    @a_horse_with_no_name: would it prevent the error scenario from happening? Sure, the timestamp might not get locked at the beginning of a transaction, but that doesn't really change anything or does it? You could still have transaction 2 setting the timestamp after transaction 1, but commiting before transaction 1. – Wolf Mar 19 '20 at 22:41

1 Answers1

0

After a lot of searching, I found the right keywords to google for... "transaction commit timestamp" to leads to all sorts of transaction timestamp tracking and system columns like xmin:

https://dba.stackexchange.com/questions/232273/is-there-way-to-get-transaction-commit-timestamp-in-postgres

This post has some more detailed information:

Questions about Postgres track_commit_timestamp (pg_xact_commit_timestamp)

In short:

  • you can turn on a postgresql option to track timestamps of commits and compare those instead of the current_timestamps/clock_timestamps inside the transaction
  • it seems though, that it is only tracked when a transaction is completed - not when it is commited, which makes the solution not bullet proof. There are also further issue to consider like transaction id (xmin) rollover for example
  • logical decoding / replication is something to look into for a proper solution

Thanks to everyone trying to help me find an answer. I hope this summary is useful to someone in the future.

Wolf
  • 892
  • 2
  • 8
  • 22