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

Jump to bottom

Rolling back a transaction that creates a function or procedure keeps a cached version of that function or procedure when subsequently re-creating it. [CORE5652] #5918

Open
firebird-automations opened this issue Oct 27, 2017 · 9 comments
Open

Rolling back a transaction that creates a function or procedure keeps a cached version of that function or procedure when subsequently re-creating it. [CORE5652] #5918

firebird-automations opened this issue Oct 27, 2017 · 9 comments
Labels
affect-version: 3.0.2 component: engine priority: major type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Ob-serve (observe)

1. Execute the following test script:

SET TERM ^ ;
CREATE FUNCTION fnTest ()
RETURNS INTEGER
AS
BEGIN
RETURN 1;
END^
SET TERM ; ^

SELECT fnTest() FROM RDB$DATABASE;

2. Then rollback the transaction / script.

3.. Now execute:

SET TERM ^ ;
CREATE FUNCTION fnTest ()
RETURNS INTEGER
AS
BEGIN
RETURN 2;
END^
SET TERM ; ^

SELECT fnTest() FROM RDB$DATABASE;

You should find that the value 1 is still returned incorrectly.

The same is also true for procedures as you can test by running:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE spTest
RETURNS (
Result INTEGER
)
AS
BEGIN
:Result = 1;
SUSPEND;
END^
SET TERM ; ^

SELECT * FROM spTest;

Rollback the transaction / script.

Now run:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE spTest
RETURNS (
Result INTEGER
)
AS
BEGIN
:Result = 2;
SUSPEND;
END^
SET TERM ; ^

SELECT * FROM spTest;

If you disconnect from the database between rollback and running the modified function / procedure then all works correctly. Suspect there is a compiled / cached version of the function / procedure which is not being cleared correctly on rollback.

UPDATE 04/12/2017 SIMILAR ISSUE
--------------------------------------------------

1. Perform an operation on a table that would violate a constraint.

2. Note error message involving violation of constraint 'constraint name'.

3. Rollback the transaction.

4. Drop the constraint

5. Perform the same operation on the table that would have violated the constraint.

6. Note the error message involving violation of constraint 'unknown'.

If you repeat the procedure but close & re-open the database connection between steps 3 & 4, you'll find that step 5. now completes successfully.
@firebird-automations
Copy link
Collaborator Author

Modified by: Ob-serve (observe)

description: 1. Execute the following test script:

SET TERM ^ ;
CREATE FUNCTION fnTest ()
RETURNS INTEGER
AS
BEGIN
RETURN 1;
END^
SET TERM ; ^

SELECT fnTest() FROM RDB$DATABASE;

2. Then rollback the transaction / script.

3.. Now execute:

SET TERM ^ ;
CREATE FUNCTION fnTest ()
RETURNS INTEGER
AS
BEGIN
RETURN 2;
END^
SET TERM ; ^

SELECT fnTest() FROM RDB$DATABASE;

You should find that the value 1 is still returned incorrectly.

The same is also true for procedures as you can test by running:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE spTest
RETURNS (
Result INTEGER
)
AS
BEGIN
:Result = 1;
SUSPEND;
END^
SET TERM ; ^

SELECT * FROM spTest;

Rollback the transaction / script.

Now run:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE spTest
RETURNS (
Result INTEGER
)
AS
BEGIN
:Result = 2;
SUSPEND;
END^
SET TERM ; ^

SELECT * FROM spTest;

If you disconnect from the database between rollback and running the modified function / procedure then all works correctly. Suspect there is a compiled / cached version of the function / procedure which is not be cleared correctly on rollback.

=>

1. Execute the following test script:

SET TERM ^ ;
CREATE FUNCTION fnTest ()
RETURNS INTEGER
AS
BEGIN
RETURN 1;
END^
SET TERM ; ^

SELECT fnTest() FROM RDB$DATABASE;

2. Then rollback the transaction / script.

3.. Now execute:

SET TERM ^ ;
CREATE FUNCTION fnTest ()
RETURNS INTEGER
AS
BEGIN
RETURN 2;
END^
SET TERM ; ^

SELECT fnTest() FROM RDB$DATABASE;

You should find that the value 1 is still returned incorrectly.

The same is also true for procedures as you can test by running:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE spTest
RETURNS (
Result INTEGER
)
AS
BEGIN
:Result = 1;
SUSPEND;
END^
SET TERM ; ^

SELECT * FROM spTest;

Rollback the transaction / script.

Now run:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE spTest
RETURNS (
Result INTEGER
)
AS
BEGIN
:Result = 2;
SUSPEND;
END^
SET TERM ; ^

SELECT * FROM spTest;

If you disconnect from the database between rollback and running the modified function / procedure then all works correctly. Suspect there is a compiled / cached version of the function / procedure which is not being cleared correctly on rollback.

@firebird-automations
Copy link
Collaborator Author

Modified by: Ob-serve (observe)

description: 1. Execute the following test script:

SET TERM ^ ;
CREATE FUNCTION fnTest ()
RETURNS INTEGER
AS
BEGIN
RETURN 1;
END^
SET TERM ; ^

SELECT fnTest() FROM RDB$DATABASE;

2. Then rollback the transaction / script.

3.. Now execute:

SET TERM ^ ;
CREATE FUNCTION fnTest ()
RETURNS INTEGER
AS
BEGIN
RETURN 2;
END^
SET TERM ; ^

SELECT fnTest() FROM RDB$DATABASE;

You should find that the value 1 is still returned incorrectly.

The same is also true for procedures as you can test by running:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE spTest
RETURNS (
Result INTEGER
)
AS
BEGIN
:Result = 1;
SUSPEND;
END^
SET TERM ; ^

SELECT * FROM spTest;

Rollback the transaction / script.

Now run:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE spTest
RETURNS (
Result INTEGER
)
AS
BEGIN
:Result = 2;
SUSPEND;
END^
SET TERM ; ^

SELECT * FROM spTest;

If you disconnect from the database between rollback and running the modified function / procedure then all works correctly. Suspect there is a compiled / cached version of the function / procedure which is not being cleared correctly on rollback.

=>

1. Execute the following test script:

SET TERM ^ ;
CREATE FUNCTION fnTest ()
RETURNS INTEGER
AS
BEGIN
RETURN 1;
END^
SET TERM ; ^

SELECT fnTest() FROM RDB$DATABASE;

2. Then rollback the transaction / script.

3.. Now execute:

SET TERM ^ ;
CREATE FUNCTION fnTest ()
RETURNS INTEGER
AS
BEGIN
RETURN 2;
END^
SET TERM ; ^

SELECT fnTest() FROM RDB$DATABASE;

You should find that the value 1 is still returned incorrectly.

The same is also true for procedures as you can test by running:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE spTest
RETURNS (
Result INTEGER
)
AS
BEGIN
:Result = 1;
SUSPEND;
END^
SET TERM ; ^

SELECT * FROM spTest;

Rollback the transaction / script.

Now run:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE spTest
RETURNS (
Result INTEGER
)
AS
BEGIN
:Result = 2;
SUSPEND;
END^
SET TERM ; ^

SELECT * FROM spTest;

If you disconnect from the database between rollback and running the modified function / procedure then all works correctly. Suspect there is a compiled / cached version of the function / procedure which is not being cleared correctly on rollback.

UPDATE 04/12/2017 SIMILAR ISSUE
--------------------------------------------------

1. Perform an operation on a table that would violate a constraint.

2. Note error message involving violation of constraint 'constraint name'.

3. Rollback the transaction.

4. Drop the constraint

5. Perform the same table that would have violated the constraint.

6. Note the error message involving violation of constraint 'unknown'.

If you repeat the procedure but close & re-open the database connection between steps 3 & 4, you'll find that step 5. now completes successfully.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

1. Execute the following test script:

SQL> SET TERM ^ ;
SQL> CREATE FUNCTION fnTest ()
CON> RETURNS INTEGER
CON> AS
CON> BEGIN
CON> RETURN 1;
CON> END^
SQL> SET TERM ; ^
SQL>
SQL> SELECT fnTest() FROM RDB$DATABASE;

  FNTEST

============
1

2. Then rollback the transaction / script.
SQL> ROLLBACK;
SQL>

3.. Now execute:

SQL> SET TERM ^ ;
SQL> CREATE FUNCTION fnTest ()
CON> RETURNS INTEGER
CON> AS
CON> BEGIN
CON> RETURN 1;
CON> END^
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-CREATE FUNCTION FNTEST failed
-Function FNTEST already exists
SQL> SET TERM ; ^
SQL>
SQL> SELECT fnTest() FROM RDB$DATABASE;

  FNTEST

============
1

SQL> exit;

It is fully expected result.

Check AUTODDL settings in your isql session and put COMMIT's where necessary.

@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

Commented by: @hvlad

I missed that script was executed with FlameRobin.
Now, with isql and AUTODDL OFF

SQL>
SQL> SET AUTODDL OFF;
SQL>
SQL> SET TERM ^ ;
SQL> CREATE FUNCTION fnTest ()
CON> RETURNS INTEGER
CON> AS
CON> BEGIN
CON> RETURN 1;
CON> END^
SQL> SET TERM ;^
SQL>
SQL> SELECT fnTest() FROM RDB$DATABASE;

  FNTEST

============
1

SQL> ROLLBACK;
SQL> SELECT fnTest() FROM RDB$DATABASE;

  FNTEST

============
1

SQL> DROP FUNCTION fnTest;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-DROP FUNCTION FNTEST failed
-Function FNTEST not found
SQL>exit;

connect again:

SQL> SELECT fnTest() FROM RDB$DATABASE;
Statement failed, SQLSTATE = 39000
Dynamic SQL Error
-SQL error code = -804
-Function unknown
-FNTEST
SQL>

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Won't Fix [ 2 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

In any case, DLL statements must be committed before any DML on affected objects.
This is old, well known and documented since IB times.

Of course, Firebird should fix usage of non-committed objects and correctly remove it from metadata cache on rollback.
I.e. bug is confirmed.

@firebird-automations
Copy link
Collaborator Author

Modified by: Ob-serve (observe)

description: 1. Execute the following test script:

SET TERM ^ ;
CREATE FUNCTION fnTest ()
RETURNS INTEGER
AS
BEGIN
RETURN 1;
END^
SET TERM ; ^

SELECT fnTest() FROM RDB$DATABASE;

2. Then rollback the transaction / script.

3.. Now execute:

SET TERM ^ ;
CREATE FUNCTION fnTest ()
RETURNS INTEGER
AS
BEGIN
RETURN 2;
END^
SET TERM ; ^

SELECT fnTest() FROM RDB$DATABASE;

You should find that the value 1 is still returned incorrectly.

The same is also true for procedures as you can test by running:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE spTest
RETURNS (
Result INTEGER
)
AS
BEGIN
:Result = 1;
SUSPEND;
END^
SET TERM ; ^

SELECT * FROM spTest;

Rollback the transaction / script.

Now run:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE spTest
RETURNS (
Result INTEGER
)
AS
BEGIN
:Result = 2;
SUSPEND;
END^
SET TERM ; ^

SELECT * FROM spTest;

If you disconnect from the database between rollback and running the modified function / procedure then all works correctly. Suspect there is a compiled / cached version of the function / procedure which is not being cleared correctly on rollback.

UPDATE 04/12/2017 SIMILAR ISSUE
--------------------------------------------------

1. Perform an operation on a table that would violate a constraint.

2. Note error message involving violation of constraint 'constraint name'.

3. Rollback the transaction.

4. Drop the constraint

5. Perform the same table that would have violated the constraint.

6. Note the error message involving violation of constraint 'unknown'.

If you repeat the procedure but close & re-open the database connection between steps 3 & 4, you'll find that step 5. now completes successfully.

=>

1. Execute the following test script:

SET TERM ^ ;
CREATE FUNCTION fnTest ()
RETURNS INTEGER
AS
BEGIN
RETURN 1;
END^
SET TERM ; ^

SELECT fnTest() FROM RDB$DATABASE;

2. Then rollback the transaction / script.

3.. Now execute:

SET TERM ^ ;
CREATE FUNCTION fnTest ()
RETURNS INTEGER
AS
BEGIN
RETURN 2;
END^
SET TERM ; ^

SELECT fnTest() FROM RDB$DATABASE;

You should find that the value 1 is still returned incorrectly.

The same is also true for procedures as you can test by running:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE spTest
RETURNS (
Result INTEGER
)
AS
BEGIN
:Result = 1;
SUSPEND;
END^
SET TERM ; ^

SELECT * FROM spTest;

Rollback the transaction / script.

Now run:

SET TERM ^ ;
CREATE OR ALTER PROCEDURE spTest
RETURNS (
Result INTEGER
)
AS
BEGIN
:Result = 2;
SUSPEND;
END^
SET TERM ; ^

SELECT * FROM spTest;

If you disconnect from the database between rollback and running the modified function / procedure then all works correctly. Suspect there is a compiled / cached version of the function / procedure which is not being cleared correctly on rollback.

UPDATE 04/12/2017 SIMILAR ISSUE
--------------------------------------------------

1. Perform an operation on a table that would violate a constraint.

2. Note error message involving violation of constraint 'constraint name'.

3. Rollback the transaction.

4. Drop the constraint

5. Perform the same operation on the table that would have violated the constraint.

6. Note the error message involving violation of constraint 'unknown'.

If you repeat the procedure but close & re-open the database connection between steps 3 & 4, you'll find that step 5. now completes successfully.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

If it is still not clear: DDL statements *must* be committed before *any* attempt to use affected objects in *any* DML statement.
Add COMMIT after CREATE FUNCTION (or DROP CONSTRAINT) and all will be as expected\as it should be.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affect-version: 3.0.2 component: engine priority: major type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations