Can I select data inserted in the same uncommited transaction?

Question

Maybe this is a dumb beginner question, but I cannot find an answer anywhere. Everywhere I read about the Transaction Isolation which solves the visibility of data within the concurrent transactions. My concern is the behavior within a single transaction.

If I start a transaction, insert some data, am I going to be able to select them right after - still within the same, yet uncommitted, transaction? If yes, can this behavior be changed in a similar way like the mentioned Transaction Isolation in the case of concurrent transactions?

To be specific, I'm targetting PostgreSQL 9.4.

Answer 1

Yes.
Everything you did inside the same transaction is visible to later commands inside the same transaction. Just not to other transactions until committed. This is true for all isolation levels except Read uncommitted where "dirty reads" are possible (but that does not affect your question per se).

It's implemented with the MVCC model (Multiversion Concurrency Control) based on TransactionIds determining relative age and visibility for every table row. Every new row version written in the same transaction gets the same xmin and is considered to have happened "at the same time".

There is a corner case for multiple CTEs (Common Table Expression) in the same command. One might think those are executed sequentially, but unless one CTE references the other their sequence is arbitrary. And all of them see the same snapshot from the start of the query. That's why it's disallowed to UPDATE the same row more than once in multiple CTEs of the same query: would be ambiguous.

Example:

• Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version

Advanced example:

• Are SELECT type queries the only type that can be nested?

Answer 2

Let's try :

CREATE OR REPLACE FUNCTION public.sp_get_user()
 RETURNS json
 LANGUAGE plpgsql
AS $function$BEGIN

INSERT INTO users (name, password) VALUES ('deadeye', 'test');
RETURN row_to_json(row) FROM (SELECT name, password FROM users WHERE name = 'deadeye') row;

END;$function$

Now, let's test :

SELECT sp_get_user();
{"name":"deadeye","password":"test"}

It works! As Erwin said, everything made in a transaction is visible inside the transaction. Isolation is only between different threads.