Note that AS OF SYSTEM TIME is designed for historical queries, and trying to use it for times too close to the current time can result in certain anomalies. You’ve seen one of them, that you may reach a node whose clock is running a bit behind and it complains about the time “in the future”. More subtly, if you do a write and then a read with AOST now()
, you’re not guaranteed to see the value you just wrote (because you may do the read on a node with a slower clock than the one you wrote too).
The latter case is why read-only transactions sometimes need to retry: by default we detect possible reorderings like this and that may force us to change results we’ve already given to you. AOST suppresses this detection, so you’re not guaranteed to see the absolute latest writes even if you’re not giving it a time in the past.
With the exception of retries, read-only transactions don’t have any performance overhead. Doing these two reads in a transaction will be just as fast as doing them separately. I would generally recommend using transactions instead of linking up two separate statements using AOST. Using new syntax in the upcoming 19.1 release, you can combine AOST and transactions to avoid restarts:
BEGIN AS OF SYSTEM TIME '-1s';
or, if you’re using a framework doesn’t let you customize the BEGIN statement,
BEGIN;
SET TRANSACTION AS OF SYSTEM TIME '-1s';
Then you can do your two selects in a transaction, which is far enough in the past to avoid the risk of retries.
Occasionally you may still want to run separate statements at the same timestamp (for example, if you want to run them in parallel). In this case it’s best to select a timestamp first and pass it in to all the queries. Getting the timestamp at which the first query ran is possible, but subtle, and it doesn’t avoid the “timestamp in future” problem (we deliberately don’t document the function involved because it’s surprisingly difficult to make robust use of it).
Have you considered a subselect?
SELECT * FROM bar WHERE bar_id IN (SELECT bar_ids FROM foo WHERE foo_id='some_id')
If you can’t find a way to express what you want as a JOIN, it’s often easier to write it as a subselect. This will get everything in one statement so you don’t have to worry about either transactions or AOST, and it should be at least a little faster. It will at least avoid the extra round trip to send bar_ids
back to the client and come back with the second query. In the best case the optimizer will be clever and see some way to optimize the combined query an a way that it couldn’t do with two separate queries.