Sebastiano, thanks for mentioning that. About 30 minutes after I posted that, I slapped my head and yelled "Doh!" for mentioning redo logs instead of the more correct rollback segment.
Like Jerry, I'd like to find out more about the underlying PostgreSQL algorithms for this. In particular, what is PostgreSQL's behavior on something like this:
SQL> DROP TABLE t;
Table dropped.
SQL> CREATE TABLE t AS SELECT table_name FROM user_tables
2 WHERE table_name like 'DR$DOCUMENT%';
Table created.
SQL>
SQL> SELECT COUNT(*) FROM t;
COUNT(*)
----------
12
SQL>
SQL> set serveroutput on size 1000000
SQL> DECLARE
2 CURSOR v_cur IS
3 SELECT table_name from t;
4 v_row v_cur%ROWTYPE;
5 v_count NUMBER DEFAULT 0;
6 BEGIN
7 OPEN v_cur;
8
9 DELETE FROM t;
10 COMMIT;
11
12 LOOP
13 FETCH v_cur INTO v_row;
14 IF v_cur%NOTFOUND THEN
15 EXIT;
16 END IF;
17 dbms_output.put_line( v_row.table_name );
18 v_count := v_count + 1;
19 END LOOP;
20 dbms_output.put_line( TO_CHAR(v_count) || ' rows retrieved');
21
22 CLOSE v_cur;
23 END;
24 /
DR$DOCUMENT_BODY$I
DR$DOCUMENT_BODY$K
DR$DOCUMENT_BODY$N
DR$DOCUMENT_BODY$R
DR$DOCUMENT_SUBJECT$I
DR$DOCUMENT_SUBJECT$K
DR$DOCUMENT_SUBJECT$N
DR$DOCUMENT_SUBJECT$R
DR$DOCUMENT_URL$I
DR$DOCUMENT_URL$K
DR$DOCUMENT_URL$N
DR$DOCUMENT_URL$R
12 rows retrieved
PL/SQL procedure successfully completed.
SQL>
If you're wondering why this code is special, notice the COMMIT prior to retrieving anything from the cursor.
