Skip to content
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

Closed
firebird-automations opened this issue Apr 16, 2018 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

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

update TEST\_TABLE
set Val=0
where current of TmpCursor;

\-\-This expression must be executed without exception \(because now Val=0\), but an error occurs\.
delete from TEST\_TABLE
where current of TmpCursor;

end
end^

====== Test Details ======

See also test for CORE3362 ("Cursors should ignore changes made by the same statement").

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Since Firebird3 implicit PSQL cursors are stable, it means that cursor doesn't see the changes made by "inner" statements.
Fix your code, please

@firebird-automations
Copy link
Collaborator Author

Modified by: Sergey Borisov (bsv)

Version: 3.0.4 [ 10863 ]

Version: 3.0.3 [ 10810 ] =>

@firebird-automations
Copy link
Collaborator Author

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:
-----------
15.5 Effect of a positioned delete
10) Whether the current row is removed from the sequence of rows of the result set descriptor of CR is implementation-defined
-----------
our implementation of stable cursor

- there is (implementation defined) workaround: if you need that "positioned update" or "positioned delete" statement could see
the changes made by another "inner" statements - enclose it into savepoint.
For example, add WHEN handler to the current BEGIN\END block:

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

update TEST\_TABLE
set Val=0
where current of TmpCursor;

delete from TEST\_TABLE
where current of TmpCursor;

-- error handler is added to force savepoints around statements inside begin\end block
when any do
exception;
end
end

Probably we should add this to the engine to avoid dependence of positioned statements on presence of additional savepoints...

@firebird-automations
Copy link
Collaborator Author

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.
With the current implementation, statement DELETE remove row, which no exists.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

> IMHO, this is not the usual behavior.
What is "usual" behavior ?
Reference on standard would be the best hint ;)

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.
There is no reason for "lock conflict" as both UPDATE and DELETE run at the same transaction.

> With the current implementation, statement DELETE remove row, which no exists.
DELETE is positioned on current row of cursor, cursor doesn't see effect of inner UPDATE (as i said above), thus cursor position is not invalidated.

I agree that positioned UPDATE\DELETE should not depend on savepoint presence, but this is a bit different question.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sergey Borisov (bsv)

Ok, we need to get used to the new behavior of the cursor.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: 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").

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant