I wonder how PG manages to allow the change in the table while you iterate it at the same time.
In special, I looking for how this is done at the low-level/internals of the engine, because I wish to replicate the idea.
DROP TABLE IF EXISTS test;
CREATE TABLE test (
id INTEGER PRIMARY KEY,
value TEXT NOT NULL
);
INSERT INTO test (id, value) values (1, 'a');
DO
$function$
DECLARE
row RECORD;
BEGIN
-- Can add while iterating?
FOR row IN SELECT * FROM test
LOOP
RAISE NOTICE 'ADD ROW..:%', row;
INSERT INTO test (id, value) values (2, 'b');
END LOOP;
-- Yes, and the new row is not visible in the loop
-- Can update while iterating?
FOR row IN SELECT * FROM test
LOOP
RAISE NOTICE 'UP ROW..:%', row;
UPDATE test SET value = 'c' WHERE id = row.id;
END LOOP;
-- Yes, and the updated rows do not affect the iteration and you see the old value
-- Can update and see the new row?
FOR row IN SELECT * FROM test
LOOP
RAISE NOTICE 'UP ROW..:%', row;
UPDATE test SET value = 'd' WHERE id = row.id;
SELECT * FROM test WHERE id = row.id AND value ='d' INTO row;
IF row IS NOT NULL THEN
RAISE NOTICE 'FOUND ROW..:%', row;
END IF;
END LOOP;
-- Yes, we can see the change if run another query
--Can remove while iterating?
FOR row IN SELECT * FROM test
LOOP
RAISE NOTICE 'DEL ROW..:%', row;
DELETE FROM test;
END LOOP;
--Yes, and the deleted rows do not affect the iteration
END
$function$;
It looks to me that PG is loading all the rows then iterating on memory, similar to this Rust code:
// instead of:
fn main() {
let mut table = vec![1, 2];
for row in table {
table.push(3);
dbg!(row);
}
// is doing
let query = table.clone();
for row in query {
table.push(3);
dbg!(row);
}
}
However, that must be very inefficient. In special, I found interesting is how this is possible:
-- Can update and see the new row?
FOR row IN SELECT * FROM test
LOOP
RAISE NOTICE 'UP ROW..:%', row;
UPDATE test SET value = 'd' WHERE id = row.id;
SELECT * FROM test WHERE id = row.id AND value ='d' INTO row;
IF row IS NOT NULL THEN
RAISE NOTICE 'FOUND ROW..:%', row;
END IF;
END LOOP;
-- Yes, we can see the change if run another query
Because this point to the idea PG is having 2 different cursors(?) that see different section of the transaction changes.
So, I imagine PG is doing something like this:
| tx_id | data |
|---|---|
| 1 | (1,a) |
| 2 | (1,b) |
And in the loop it get something like SELECT * FROM data where tx_id <=1 but when you run the next query it gets SELECT * FROM data where tx_id <=2?