Forum OpenACS Q&A: Response to something like vacuum analyze or compact database for Oracle?

David, your conclusion is my point. Even though the rows are gone, the cursor is still able to get the rows.

Taking my previous example as a starting point, I broke it up into two separate sessions. (Session 1 and Session 2 are completely different connections to the database. Oracle sees them as two concurrent users.)

--================
-- Session 1
--================

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
r


--================
-- Session 2
--================

SQL> variable x refcursor
SQL> begin
  2  open :x for select * from t;
  3  end ;
  4  /

PL/SQL procedure successfully completed.


--================
-- Session 1
--================

SQL> delete from t;

12 rows deleted.

SQL> commit;

Commit complete.

SQL> 


--================
-- Session 2
--================

SQL> print x

TABLE_NAME
--------------------------------
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

TABLE_NAME
--------------------------------
DR$DOCUMENT_URL$R

12 rows selected.

The above steps were performed in the order indicated. Databases that offer transaction control but don't perform some kind of multi-versioning (as Oracle does, and, as I'm starting to think PG does), would not have allowed Session 1 to delete the rows, as the shared locks held by Session 2 would have prevented it. Alternatively (assuming a lower isolation level in the same non-versioning database), Session 1 might have been able to delete the rows, but then what would Session 2 see? For non-versioning databases, where would the data in the final action come from? The rows are gone (having been committed), but non-versioning databases don't keep track of what the data looked like at the start of the query. (That is, they don't attempt to get things out of a rollback segment.) That's why they block in these situations at normal isolation levels (repeatable read in particular).

None of this really affects PG in any way. I posted my first comment to shed some light on Oracle's behavior. (Many people still work with an understanding of transactions and concurrency that is the textbook "shared locks block the acquisition of exclusive locks by other sessions" model.) The original code sample I posted is exactly the behavior expected of Oracle (and perhaps of PG, but no one's answered that question). But it is emphatically not the behavior one expects out of, say, Sybase (circa System X anyway). Most transactional databases still use non-versioning concurrency control. (PG increasingly looks to be the exception, a prospect I find exciting. Sadly, I've not found a PostgreSQL Concepts manual comparable to Oracle's.)

I'm looking forward to seeing how PG handles some of these samples. Don's right in that multi-versioning is what dramatically increases concurrency.

But at this point, I'm getting confused as to what, if any issue, there is with what I said? Sebastiano's tone is one of correction, but the statement "So it's normal that the cursor retrieves the rows from the table even if they've been deleted immediately after the open statement," doesn't apply to all databases universally. That fact is why I went on to explain Oracle's reasoning behind using the rollback segment in the first place.