Can I select data inserted in the same uncommited transaction?
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 TransactionId
s 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?
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.