I run tests with a debugger against the db using transactions. Sometimes, when I hit a breakpoint, I would want to look around in the database using the same transaction my test currently is using, see what its code is seeing. I know, I can access the db via the debugger, but some straightforward SQL sometimes would be less complicated.
Is this possible, somehow?
This is a reasonable wish, and it could be handy, but there is no way to do that in PostgreSQL.
You can inspect pg_stat_activity table to see all queries currently being processed. With the right priviliges you can also list the contents of pg_temp schemas and see what temp objects are created as well as some of their metadata. You can let your code inject tags and other information into comments in your queries, by which you'll be able to link a query seen in pg_stat_activity to a particular worker process.
But you cannot hijack a session or inspect the contents of tables in pg_temp schemas belonging to other sessions, even with elevated privilages - I think it's a purposefully maintained technical limitation.
If you're using PL/pgSQL, you can modify your functions to save debugging information somewhere.
For test runs, you can also switch from temp tables to unlogged tables that will give you pretty much the same performance, but won't be deleted after the session ends. Keep in mind that it can cause conflicts: if two or more sessions used to operate each on its own temp table, after switching to unlogged they'll have to use different names or schemas.
