I have two transactions (two inserts to a table with the column "ins" which has DEFAULT values as result of executing function NOW(), to receive timestamp of inserting), and also I have an ETL process to load data from this table to another place.
A transaction could appear with delay (for example 15-60 seconds).
For ETL I use parameter "last_ins" which equal MAX(ins) from the previous execution:
SELECT MAX(ins) as last_ins
FROM public.pgsql_novice_example
WHERE ins > :last_ins
In this case, I can miss a delayed transaction.
To solve this problem, I see a few ways:
- Increase the level of isolation (but for my high load system it's not the best solution);
- Add a trigger on this table to store all changes, and then use this new table for ETL process (but it increases I/O operations);
- Use an output plugin for logical decoding to receive sequentially changes from WAL files (it looks like too hard to implement, and why I need to parse all WAL files if I want to send only one table);
- Load data from my table with the postponement (for example ":last_ins - interval '30 minutes'") which will be bigger than time of executing a transaction (but I want to have data near to real-time).
I would be thankful for any suggestions or help. Maybe I could use a function which will show not start of a transaction, but the end?