PostgreSQL visibility of records changes from other transaction inside the current one
I was assuming that these new records will not be visible to the current transaction, but proved wrong on testing.


That's not the case in the default READ COMMITTED isolation. Changes from committed transactions become visible at the start of the next statement in a transaction. Each statement still has a snapshot, so you can't have rows appear within a statement.

You can use SERIALIZABLE isolation to get that behaviour, but it has a performance impact and can lead to table bloat on fast-changing tables.

See the documentation on transaction isolation to understand what is going on.