Can this PostgreSQL code: WITH moved_rows AS ( DELETE FROM products WHERE "date" >=…
- User
Can this PostgreSQL code:
WITH moved_rows AS ( DELETE FROM products WHERE "date" >= '2010-10-01' AND "date" < '2010-11-01' RETURNING * ) INSERT INTO products_log SELECT * FROM moved_rows;
...be implemented like this on Oracle (assuming id is the PK column):
declare v_cnt number; begin for rec in ( SELECT * FROM products WHERE "date" >= '2010-10-01' AND "date" < '2010-11-01' ) loop delete from products where id = rec.id; v_cnt := SQL%ROWCOUNT; if v_cnt > 0 then insert into products_log values rec; end if; end loop; end;
Any alternative solutions?
Assistant
Excellent question. This is a very common pattern: archiving or logging data before deleting it.
Let's break down your question.
Analysis of Your Proposed Oracle Solution
First, to answer your direct question: Yes, the PL/SQL loop you've written can implement the logic. It will iterate through the target rows, delete them one by one, and insert them into the log table.
However, it is highly discouraged for several critical reasons, making it a poor translation of the efficient PostgreSQL statement.
-
Performance: This is the biggest drawback. The approach is "row-by-row" (sometimes called "slow-by-slow"). For every single row you want to move, you perform:
- A cursor fetch (from the PL/SQL engine).
- A
DELETEstatement (a context switch to the SQL engine). - An
INSERTstatement (another context switch to the SQL engine). If you are moving 1 million rows, this results in over 2 million context switches, which is incredibly inefficient compared to a single set-based operation. The PostgreSQL code executes as one atomic, set-based operation.
-
Concurrency / Data Integrity: This is a more subtle but dangerous flaw. The
SELECTstatement in your cursor (for rec in (SELECT * ...) establishes a read-consistent view of the data at the moment it starts. Consider this race condition:- Time 1: Your PL/SQL block opens the cursor and has a list of rows to process.
- Time 2: Another session
DELETEs one of those rows and commits. - Time 3: Your loop gets to that row. It fetches the data as it existed at Time 1.
- Time 4: Your
delete from products where id = rec.idstatement runs. It finds nothing to delete, soSQL%ROWCOUNTis 0. - Time 5: Your
if v_cnt > 0condition fails, and theINSERTinto the log is skipped. - Result: A row was deleted from the
productstable but was never logged, leading to data loss.
-
Verbosity and Complexity: The PL/SQL block is much more code to write, read, and maintain than a single, declarative SQL statement.
Alternative Solutions in Oracle
Here are much better ways to achieve this in Oracle, from best to worst.
Alternative 1:
DELETE ... RETURNING BULK COLLECT(The Best Practice)This is the most direct and performant equivalent to the PostgreSQL
DELETE ... RETURNINGclause. It performs the delete as a single set-based operation and efficiently captures the deleted rows into a PL/SQL collection, which can then be inserted in a single bulk operation.This method is fast, efficient, and transactionally safe, avoiding the race condition described above because the
DELETEandRETURNINGhappen in one atomic step.-- First, ensure you have a PL/SQL type that matches the table structure. -- You can define it manually or, more easily, use %ROWTYPE. DECLARE -- Define a collection type based on the table's row structure. TYPE t_products_tab IS TABLE OF products%ROWTYPE; -- Declare a variable of that collection type. v_deleted_rows t_products_tab; BEGIN -- Delete the rows in a single SQL statement, and return all columns -- of the deleted rows into our collection in one go. DELETE FROM products WHERE "date" >= DATE '2010-10-01' AND "date" < DATE '2010-11-01' RETURNING id, name, "date", price -- list all columns explicitly BULK COLLECT INTO v_deleted_rows; -- Now, bulk-insert the entire collection of deleted rows -- into the log table in a single, highly efficient operation. FORALL i IN 1 .. v_deleted_rows.COUNT INSERT INTO products_log VALUES v_deleted_rows(i); -- Optionally, print how many rows were moved. DBMS_OUTPUT.PUT_LINE('Moved ' || SQL%ROWCOUNT || ' rows.'); END; /Note: Using
DATE '2010-10-01'is the standard, unambiguous way to write a date literal in SQL.Alternative 2: Two-Step SQL Statements
This approach uses two simple, set-based SQL statements. It is much better than the row-by-row loop but has a potential concurrency issue, though different from the one in your PL/SQL loop.
-- Step 1: Copy the rows you are about to delete into the log table. INSERT INTO products_log (id, name, "date", price) SELECT id, name, "date", price FROM products WHERE "date" >= DATE '2010-10-01' AND "date" < DATE '2010-11-01'; -- Step 2: Delete those same rows from the original table. DELETE FROM products WHERE "date" >= DATE '2010-10-01' AND "date" < DATE '2010-11-01'; -- Important: Both statements must be in the same transaction. -- A COMMIT should only happen after both succeed. COMMIT;- Caveat: This method is also subject to a race condition. If a new row matching the
WHEREclause is inserted by another session after theINSERT(Step 1) but before theDELETE(Step 2), that new row will be deleted without ever being logged. This is generally only safe if you can guarantee no concurrent modifications or if you first lock the table (LOCK TABLE products IN EXCLUSIVE MODE;), which can severely impact concurrency.
Alternative 3: Using a Trigger (Architectural Approach)
You could also solve this by creating a trigger that automatically logs any deleted row.
CREATE OR REPLACE TRIGGER trg_log_deleted_products AFTER DELETE ON products FOR EACH ROW BEGIN -- :OLD contains the values of the row that was just deleted. INSERT INTO products_log (id, name, "date", price) VALUES (:OLD.id, :OLD.name, :OLD."date", :OLD.price); END; /With this trigger in place, your original operation becomes just a simple
DELETEstatement:DELETE FROM products WHERE "date" >= DATE '2010-10-01' AND "date" < DATE '2010-11-01'; COMMIT;- Pros: The logic is automatic and enforced for all deletes, preventing mistakes.
- Cons: It adds overhead to every single delete operation on the
productstable, whether you want it logged or not. It also makes the logic less explicit; someone reading theDELETEstatement may not be aware that a logging operation is happening in the background.
Summary and Recommendation
Method Performance Concurrency Safety Simplicity Recommendation PostgreSQL CTE Excellent Excellent Excellent N/A (Source) Your PL/SQL Loop Very Poor Poor (Prone to data loss) Poor Do Not Use DELETE RETURNING BULK COLLECTExcellent Excellent Good Highly Recommended Two-Step SQL Good Poor (Prone to data loss) Excellent Use with caution, only in controlled environments AFTER DELETETriggerGood Excellent Good Recommended if logging is a permanent business rule Conclusion: The best and most direct translation of your PostgreSQL code into Oracle is Alternative 1:
DELETE ... RETURNING BULK COLLECT. It matches the performance, efficiency, and transactional safety of the original.-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.