How Postgres avoid "iteration invalidation" and you can loop over rows and modify the data at the same time?

32 Views Asked by At

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?

0

There are 0 best solutions below