PostgreSQL中MSSQL STATIC CURSOR等效方案及行删除问题咨询

Great question! Let's break this down clearly to cover both parts of your query:

Insensitive Cursors: PostgreSQL's "Static" Alternative #

PostgreSQL doesn't have a direct 1:1 named equivalent to MSSQL's STATIC CURSOR, but it offers functionality that delivers the exact same end result using insensitive cursors:

• In MSSQL, a STATIC CURSOR creates a temporary table to store the full query result set; all subsequent cursor operations rely on this copy, ignoring any later changes to the underlying table.
• In PostgreSQL, an INSENSITIVE CURSOR uses a transaction snapshot instead. When you declare the cursor, it captures a snapshot of the database state at that moment in your transaction. Any cursor traversal will only see data from that snapshot—completely isolated from later inserts, updates, or deletes on the underlying table. While the implementation (snapshot vs. temp table) differs, the user-facing behavior matches MSSQL's static cursor perfectly.

You can declare an insensitive cursor explicitly like this:

DECLARE my_static_cursor INSENSITIVE CURSOR FOR
SELECT column1, column2 FROM my_target_table;

Even if you omit the INSENSITIVE keyword, PostgreSQL treats cursors as insensitive by default unless you add a locking clause like FOR UPDATE or FOR NO KEY UPDATE. So if you're not modifying data via the cursor, you get the "static" behavior automatically.

Behavior When Underlying Rows Are Deleted Before Traversal #

This outcome depends entirely on whether your cursor is insensitive (static-like) or sensitive:

• Insensitive Cursors: The cursor will still return the deleted row when you reach its position. Since it's tied to the transaction snapshot taken at cursor declaration, it doesn't care if the row was deleted later in another (or even the same) transaction. You'll see the row exactly as it existed when you created the cursor.
• Sensitive Cursors (with locking clauses): If you declared the cursor with FOR UPDATE (or similar) to lock rows for modification, it's sensitive to underlying changes. When you traverse to a row that's since been deleted, the cursor will simply skip that row—you won't get any data returned for it, and the cursor will move to the next existing row in the result set.

For example, a sensitive cursor declaration looks like this:

DECLARE my_sensitive_cursor CURSOR FOR
SELECT column1, column2 FROM my_target_table FOR UPDATE;

If another transaction deletes a row that's in this cursor's result set, your traversal will just skip over that deleted entry.

内容的提问来源于stack exchange，提问作者Slack Groverglow