New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Wrong behavour FOR <select_stmt> [AS CURSOR cursorname] with next update and delete [CORE5794] #6057
Comments
Commented by: @hvlad Since Firebird3 implicit PSQL cursors are stable, it means that cursor doesn't see the changes made by "inner" statements. |
Modified by: Sergey Borisov (bsv)Version: 3.0.4 [ 10863 ] Version: 3.0.3 [ 10810 ] => |
Commented by: @hvlad Few more notes: - i failed to see in standard how cursor should work with rows updated by "positioned update statement" identified by the same cursor - about "positioned delete statement" i see: - there is (implementation defined) workaround: if you need that "positioned update" or "positioned delete" statement could see Execute block
-- error handler is added to force savepoints around statements inside begin\end block Probably we should add this to the engine to avoid dependence of positioned statements on presence of additional savepoints... |
Commented by: Sergey Borisov (bsv) IMHO, this is not the usual behavior. If cursor is stable and "does not see" the changes made in inner statments, then must be the "lock conflict" on last delete statement. |
Commented by: @hvlad > IMHO, this is not the usual behavior. Firebird 2.5, for example, performs both UPDATE and DELETE and row is deleted finally. > If cursor is stable and "does not see" the changes made in inner statments, then must be the "lock conflict" on last delete statement. > With the current implementation, statement DELETE remove row, which no exists. I agree that positioned UPDATE\DELETE should not depend on savepoint presence, but this is a bit different question. |
Commented by: Sergey Borisov (bsv) Ok, we need to get used to the new behavior of the cursor. |
Modified by: Sean Leyne (seanleyne)status: Open [ 1 ] => Resolved [ 5 ] resolution: Won't Fix [ 2 ] |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Modified by: @pavel-zotovstatus: Closed [ 6 ] => Closed [ 6 ] QA Status: No test => Done successfully Test Details: See also test for CORE3362 ("Cursors should ignore changes made by the same statement"). |
Submitted by: Sergey Borisov (bsv)
CREATE EXCEPTION TEST_EXCEPTION 'TEST';
CREATE TABLE TEST_TABLE (
ID INTEGER,
VAL INTEGER
);
INSERT INTO TEST_TABLE (ID, VAL) VALUES (1, 10);
SET TERM ^ ;
CREATE OR ALTER TRIGGER TEST_TABLE_BD FOR TEST_TABLE
ACTIVE BEFORE DELETE POSITION 0
as
begin
IF (OLD.Val >0) THEN EXCEPTION TEST_EXCEPTION 'It is forbidden to delete row with Val>0 (ID = '||Coalesce(http://OLD.ID, 'null')||', Val='||Coalesce(old.Val,'null')||')';
end^
Execute block
as
declare variable curVal integer;
declare variable curID integer;
begin
for select ID, VAL
from TEST_TABLE
where VAL>0
into curID, CurVal
as cursor TmpCursor
do begin
end
end^
====== Test Details ======
See also test for CORE3362 ("Cursors should ignore changes made by the same statement").
The text was updated successfully, but these errors were encountered: