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

Stale metadata for generator could be used [CORE6464] #2375

Open
firebird-automations opened this issue Jan 7, 2021 · 8 comments
Open

Stale metadata for generator could be used [CORE6464] #2375

firebird-automations opened this issue Jan 7, 2021 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

Attachments:
extpool-and-sequence.bat.7z

Steps to reproduce:

1) open firebird.conf and set ExtConnPoolLifeTime to value about 5...10 (I used 5); restart FB 4.x.

2) unpack batch from attached .7z; open it in editor and adjust following variables:

fbc // path to isql.exe from FB 4.x
dbnm // name of temporary database
usr // SYSDBA
psw // masterkey

This batch will create temporary database and .sql which does:

1) connect to this temp database;
2) run 'RECREATE SEQUENCE g_eds_test;'
3) creates procedure 'sp_increment_sequence' (its name speaks for itself)
4) RECONNECT to database;
5) run procedure 'sp_increment_sequence' with input arg. = 123.
6) check that final value of sequence 'g_eds_test' is 123

Then batch will parse firebird.conf (from !fbc!\ folder) and take value ExtConnPoolLifeTime from there (it must be uncommented, of course).

Finally, it calls just created .sql *three* times, with different interval between second and third: firs interval = (ExtConnPoolLifeTime + 1), second is (ExtConnPoolLifeTime - 1), i.e.:

t0 --> run #⁠1
t1 = return from sql (i.e. finish of run #⁠1)

.... wait for (ExtConnPoolLifeTime + 1) seconds ....

t2 = t1 + (ExtConnPoolLifeTime + 1) seconds --> run #⁠2
t3 = return from sql (i.e. finish of run #⁠2)

.... wait for (ExtConnPoolLifeTime - 1) seconds ....

t4 = t3 + (ExtConnPoolLifeTime - 1) seconds --> run #⁠3

Built-in Windows utility 'timeout' is used for pausesbetween launches of isql.

Here is the content of SQL:

set bail on;
set heading off;
set list on;
connect 'localhost:c:\temp\tmp4test.fdb' user SYSDBA password 'masterkey';
create or alter procedure sp_increment_sequence(a_inc int = null) as begin end;

recreate view v_check as
select cast('now' as timestamp) dts, coalesce( q'{YES, '}' || trim(g.rdb$generator_name) || q'{' exists.}', q'{NO, it doesn't}') as "Does sequence 'g_eds_test' exists ? =>"
from rdb$database r
left join rdb$generators g on upper(g.rdb$generator_name) = upper('g_eds_test');
commit;

select 'point-1' as msg, v.* from v_check v;
recreate sequence g_eds_test;
commit;
select 'point-2' as msg, v.* from v_check v;

select gen_id(g_eds_test, 0) as init_seq_value from rdb$database;
commit;

set term #⁠;
create or alter procedure sp_increment_sequence(a_inc int = null) returns(gen_value_before_inc int, gen_value_after__inc int) as
declare c int;
begin
gen_value_before_inc = gen_id(g_eds_test,0);
c = gen_id( g_eds_test, coalesce(a_inc,1) );
gen_value_after__inc = gen_id(g_eds_test,0);
suspend;
end
#⁠
commit
#⁠

-- #⁠#⁠#⁠ RECONNECT #⁠#⁠#⁠
connect 'localhost:c:\temp\tmp4test.fdb' user SYSDBA password 'masterkey'
#⁠

select gen_id(g_eds_test, 0) as seq_value_after_reconnect from rdb$database
#⁠
commit
#⁠

execute block returns(gen_value_before_inc int, gen_value_after__inc int) as
begin
execute statement ( 'select gen_value_before_inc, gen_value_after__inc from sp_increment_sequence( ? )' ) ( 123 )
on external 'localhost:' || rdb$get_context('SYSTEM','DB_NAME') as user 'SYSDBA' password 'masterkey'
-- role 'A' || replace(lpad('',30,uuid_to_char(gen_uuid())),'-','') --------------------------------------------- ::::: NB :::::
into gen_value_before_inc, gen_value_after__inc
;
suspend;
end
#⁠
set term ;#⁠

select iif( gen_id(g_eds_test, 0) > 0, 'Expected: ' || gen_id(g_eds_test,0), 'UNEXPECTED >>> ' || gen_id(g_eds_test,0) || ' <<< ?' ) as final_seq_value from rdb$database;
commit;

drop procedure sp_increment_sequence;
drop sequence g_eds_test;
commit;

select 'point-3' as msg, v.* from v_check v;
commit;

set list off;
set heading off;
select '=== bye-bye from SQL ===' from rdb$database;

PLEASE NOTE ON COMMENTED LINE:
" -- role 'A' || replace(lpad('',30,uuid_to_char(gen_uuid())),'-','') --------------------------------------------- ::::: NB :::::"

Run this batch.

This is result of batch when it runs with ExtConnPoolLifeTime = 5:
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
MSG point-1
DTS 2021-01-07 10:30:04.5180
Does sequence 'g_eds_test' exists ? => NO, it doesn't

MSG point-2
DTS 2021-01-07 10:30:04.5290
Does sequence 'g_eds_test' exists ? => YES, 'G_EDS_TEST' exists.

INIT_SEQ_VALUE 0

SEQ_VALUE_AFTER_RECONNECT 0

GEN_VALUE_BEFORE_INC 0
GEN_VALUE_AFTER__INC 123

FINAL_SEQ_VALUE Expected: 123

MSG point-3
DTS 2021-01-07 10:30:05.0920
Does sequence 'g_eds_test' exists ? => NO, it doesn't

=== bye-bye from SQL ===

Take delay for 6 s, i.e. #⁠#⁠#⁠ GREATER #⁠#⁠#⁠ than ECP lifetime. . .

Waiting for 6 seconds, press a key to continue ...5 4 3 2 1 0

Check result after "long delay":

MSG point-1
DTS 2021-01-07 10:30:11.5640
Does sequence 'g_eds_test' exists ? => NO, it doesn't

MSG point-2
DTS 2021-01-07 10:30:11.5790
Does sequence 'g_eds_test' exists ? => YES, 'G_EDS_TEST' exists.

INIT_SEQ_VALUE 0

SEQ_VALUE_AFTER_RECONNECT 0

GEN_VALUE_BEFORE_INC 0
GEN_VALUE_AFTER__INC 123

FINAL_SEQ_VALUE Expected: 123

MSG point-3
DTS 2021-01-07 10:30:12.1600
Does sequence 'g_eds_test' exists ? => NO, it doesn't

=== bye-bye from SQL ===

Take delay for 4 s, i.e. #⁠#⁠#⁠ SHORTER #⁠#⁠#⁠ than ECP lifetime. . .

Waiting for 4 seconds, press a key to continue ...3 2 1 0

Check result after "short delay":

MSG point-1
DTS 2021-01-07 10:30:16.2370
Does sequence 'g_eds_test' exists ? => NO, it doesn't

MSG point-2
DTS 2021-01-07 10:30:16.2560
Does sequence 'g_eds_test' exists ? => YES, 'G_EDS_TEST' exists.

INIT_SEQ_VALUE 0

SEQ_VALUE_AFTER_RECONNECT 0

GEN_VALUE_BEFORE_INC 123
GEN_VALUE_AFTER__INC 246

FINAL_SEQ_VALUE UNEXPECTED >>> 0 <<< ?

MSG point-3
DTS 2021-01-07 10:30:16.3380
Does sequence 'g_eds_test' exists ? => NO, it doesn't

=== bye-bye from SQL ===
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

As you can see, sequence is recreated every time.
But its *OLD* value (123) still visible to procedure 'sp_increment_sequence' when it is called via ES/EDS and - most important - time from previous call is less then ExtConnPoolLifeTime.

If we uncomment line "-- role 'A' || replace(lpad('',30,uuid_to_char(gen_uuid())),'-','')" and repeat than result is expected:

GEN_VALUE_BEFORE_INC 0
GEN_VALUE_AFTER__INC 123
FINAL_SEQ_VALUE Expected: 123

(using ROLE clause with random value forces EDS mechanism to create totally new connection, AFAIK).

PS. WI-V4.0.0.2307 Firebird 4.0 Release Candidate 1

PPS.
UPD, 03.02.21. Changed title after Vlad's request.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

Attachment: extpool-and-sequence.bat.7z [ 13530 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

AFAIU, this has almost nothing to do with EDS and should be reproducible with two regular attachments:

(1) create sequence, set it to 1
(2) select gen_id -- returns 1
(1) recreate sequence, set it to 2
(2) select gen_id -- using the same prepared statement as the first time! -- returns unexpected result

When the statement is prepared, generator name is converted to its internal ID (RDB$GENERATOR_ID) and later all GEN_IDs inside this statement read/write the generator using its ID (for performance reasons). RECREATE SEQUENCE drops the old generator and creates the new one, old ID becomes invalid and all subsequent GEN_IDs using the old ID will return zero (or maybe garbage -- not sure).

EDS just preserves the statement in the prepared state if the interval is less than ExtConnPoolLifeTime, thus showing you the issue.

I think the source of the issue is the lack of existence locks for generators, thus making it possible to drop the generator which is used by prepared statements -- what is usually prohibited for other objects.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> this has almost nothing to do with EDS and should be reproducible with two regular attachments

Yes, it is so (checked on recent 3.x and 4.x):

set bail on;
set list on;
shell if exist c:\temp\tmp4test.fdb del c:\temp\tmp4test.fdb;
create database 'localhost:c:\temp\tmp4test.fdb' user SYSDBA password 'masterkey';

SET KEEP_TRAN_PARAMS ON;
commit;

set transaction read committed no wait;

create sequence g; -- "(1) create sequence, set it to 1"
select gen_id(g,10) from rdb$database; -- (2) select gen_id -- returns 1

select gen_id(g,0) as initial_seq_value from rdb$database;
show sequ g;
commit;

set term #⁠;
execute block returns(iter smallint, prepared_seq_value int, changed_seq_value int) as
declare c int;
begin
for execute statement 'select row_number()over(), gen_id(g,0) from rdb$types rows 3'
into iter, prepared_seq_value
do begin
execute statement 'recreate sequence g';
execute statement 'select gen_id(g,9) from rdb$database' into changed_seq_value;
suspend;
end
end
#⁠
set term ;#⁠
commit;

select gen_id(g,0) as final_seq_value from rdb$database;
show sequ g;

Output:
#⁠#⁠#⁠#⁠#⁠#⁠

GEN_ID 10

INITIAL_SEQ_VALUE 10

Generator G, current value: 10, initial value: 1, increment: 1

ITER 1
PREPARED_SEQ_VALUE 10
CHANGED_SEQ_VALUE 9

ITER 2
PREPARED_SEQ_VALUE 10 <<< !! <<<
CHANGED_SEQ_VALUE 18

ITER 3
PREPARED_SEQ_VALUE 10 <<< !! <<<
CHANGED_SEQ_VALUE 27

FINAL_SEQ_VALUE 0 <<<<<<<<<<<<<< !!! <<<<<<<<<<<<<<

Generator G, current value: 0, initial value: 1, increment: 1

PS.
Finally we get 0 (zero). Is this because of:

> old ID becomes invalid and all subsequent GEN_IDs using the old ID will return **zero** ...

-- ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Your last test case is wrong because RECREATE SEQUENCE is not committed.
And why again EXECUTE BLOCK + EXECUTE STATEMENT ? Two python connections is perfectly enough for the test.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> Your last test case is wrong because RECREATE SEQUENCE is not committed.

But if i use ES / WITH AUTONOMOUS TRANSACTION - result is the same as above:

set bail on;
set list on;
shell if exist c:\temp\tmp4test.fdb del c:\temp\tmp4test.fdb;
create database 'localhost:c:\temp\tmp4test.fdb' user SYSDBA password 'masterkey';

SET KEEP_TRAN_PARAMS ON;
commit;

set transaction read committed no wait;

create sequence g; -- "(1) create sequence, set it to 1"
select gen_id(g,10) from rdb$database; -- (2) select gen_id -- returns 1

select gen_id(g,0) as initial_seq_value from rdb$database;
show sequ g;
commit;

set transaction READ COMMITTED NO WAIT;

set term #⁠;
execute block returns(iter smallint, prepared_seq_value int, changed_seq_value int) as
declare c int;
begin
for execute statement 'select row_number()over(), gen_id(g,0) from rdb$types rows 3'
into iter, prepared_seq_value
do begin
execute statement 'recreate sequence g'
WITH AUTONOMOUS TRANSACTION
;
execute statement 'select gen_id(g,9) from rdb$database'
WITH AUTONOMOUS TRANSACTION
into changed_seq_value;
suspend;
end
end
#⁠
set term ;#⁠
commit;

select gen_id(g,0) as final_seq_value from rdb$database;
show sequ g;

> And why again EXECUTE BLOCK + EXECUTE STATEMENT ? Two python connections is perfectly enough for the test.

Pure ISQL is better and easier :-)

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0.7 [ 10940 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Please, change the title and description to show actual issue (stale metadata for generator could be used, afaiu).

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

description: Steps to reproduce:

1) open firebird.conf and set ExtConnPoolLifeTime to value about 5...10 (I used 5); restart FB 4.x.

2) unpack batch from attached .7z; open it in editor and adjust following variables:

fbc // path to isql.exe from FB 4.x
dbnm // name of temporary database
usr // SYSDBA
psw // masterkey

This batch will create temporary database and .sql which does:

1) connect to this temp database;
2) run 'RECREATE SEQUENCE g_eds_test;'
3) creates procedure 'sp_increment_sequence' (its name speaks for itself)
4) RECONNECT to database;
5) run procedure 'sp_increment_sequence' with input arg. = 123.
6) check that final value of sequence 'g_eds_test' is 123

Then batch will parse firebird.conf (from !fbc!\ folder) and take value ExtConnPoolLifeTime from there (it must be uncommented, of course).

Finally, it calls just created .sql *three* times, with different interval between second and third: firs interval = (ExtConnPoolLifeTime + 1), second is (ExtConnPoolLifeTime - 1), i.e.:

t0 --> run #⁠1
t1 = return from sql (i.e. finish of run #⁠1)

.... wait for (ExtConnPoolLifeTime + 1) seconds ....

t2 = t1 + (ExtConnPoolLifeTime + 1) seconds --> run #⁠2
t3 = return from sql (i.e. finish of run #⁠2)

.... wait for (ExtConnPoolLifeTime - 1) seconds ....

t4 = t3 + (ExtConnPoolLifeTime - 1) seconds --> run #⁠3

Built-in Windows utility 'timeout' is used for pausesbetween launches of isql.

Here is the content of SQL:

set bail on;
set heading off;
set list on;
connect 'localhost:c:\temp\tmp4test.fdb' user SYSDBA password 'masterkey';
create or alter procedure sp_increment_sequence(a_inc int = null) as begin end;

recreate view v_check as
select cast('now' as timestamp) dts, coalesce( q'{YES, '}' || trim(g.rdb$generator_name) || q'{' exists.}', q'{NO, it doesn't}') as "Does sequence 'g_eds_test' exists ? =>"
from rdb$database r
left join rdb$generators g on upper(g.rdb$generator_name) = upper('g_eds_test');
commit;

select 'point-1' as msg, v.* from v_check v;
recreate sequence g_eds_test;
commit;
select 'point-2' as msg, v.* from v_check v;

select gen_id(g_eds_test, 0) as init_seq_value from rdb$database;
commit;

set term #⁠;
create or alter procedure sp_increment_sequence(a_inc int = null) returns(gen_value_before_inc int, gen_value_after__inc int) as
declare c int;
begin
gen_value_before_inc = gen_id(g_eds_test,0);
c = gen_id( g_eds_test, coalesce(a_inc,1) );
gen_value_after__inc = gen_id(g_eds_test,0);
suspend;
end
#⁠
commit
#⁠

-- #⁠#⁠#⁠ RECONNECT #⁠#⁠#⁠
connect 'localhost:c:\temp\tmp4test.fdb' user SYSDBA password 'masterkey'
#⁠

select gen_id(g_eds_test, 0) as seq_value_after_reconnect from rdb$database
#⁠
commit
#⁠

execute block returns(gen_value_before_inc int, gen_value_after__inc int) as
begin
execute statement ( 'select gen_value_before_inc, gen_value_after__inc from sp_increment_sequence( ? )' ) ( 123 )
on external 'localhost:' || rdb$get_context('SYSTEM','DB_NAME') as user 'SYSDBA' password 'masterkey'
-- role 'A' || replace(lpad('',30,uuid_to_char(gen_uuid())),'-','') --------------------------------------------- ::::: NB :::::
into gen_value_before_inc, gen_value_after__inc
;
suspend;
end
#⁠
set term ;#⁠

select iif( gen_id(g_eds_test, 0) > 0, 'Expected: ' || gen_id(g_eds_test,0), 'UNEXPECTED >>> ' || gen_id(g_eds_test,0) || ' <<< ?' ) as final_seq_value from rdb$database;
commit;

drop procedure sp_increment_sequence;
drop sequence g_eds_test;
commit;

select 'point-3' as msg, v.* from v_check v;
commit;

set list off;
set heading off;
select '=== bye-bye from SQL ===' from rdb$database;

PLEASE NOTE ON COMMENTED LINE:
" -- role 'A' || replace(lpad('',30,uuid_to_char(gen_uuid())),'-','') --------------------------------------------- ::::: NB :::::"

Run this batch.

This is result of batch when it runs with ExtConnPoolLifeTime = 5:
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
MSG point-1
DTS 2021-01-07 10:30:04.5180
Does sequence 'g_eds_test' exists ? => NO, it doesn't

MSG point-2
DTS 2021-01-07 10:30:04.5290
Does sequence 'g_eds_test' exists ? => YES, 'G_EDS_TEST' exists.

INIT_SEQ_VALUE 0

SEQ_VALUE_AFTER_RECONNECT 0

GEN_VALUE_BEFORE_INC 0
GEN_VALUE_AFTER__INC 123

FINAL_SEQ_VALUE Expected: 123

MSG point-3
DTS 2021-01-07 10:30:05.0920
Does sequence 'g_eds_test' exists ? => NO, it doesn't

=== bye-bye from SQL ===

Take delay for 6 s, i.e. #⁠#⁠#⁠ GREATER #⁠#⁠#⁠ than ECP lifetime. . .

Waiting for 6 seconds, press a key to continue ...5 4 3 2 1 0

Check result after "long delay":

MSG point-1
DTS 2021-01-07 10:30:11.5640
Does sequence 'g_eds_test' exists ? => NO, it doesn't

MSG point-2
DTS 2021-01-07 10:30:11.5790
Does sequence 'g_eds_test' exists ? => YES, 'G_EDS_TEST' exists.

INIT_SEQ_VALUE 0

SEQ_VALUE_AFTER_RECONNECT 0

GEN_VALUE_BEFORE_INC 0
GEN_VALUE_AFTER__INC 123

FINAL_SEQ_VALUE Expected: 123

MSG point-3
DTS 2021-01-07 10:30:12.1600
Does sequence 'g_eds_test' exists ? => NO, it doesn't

=== bye-bye from SQL ===

Take delay for 4 s, i.e. #⁠#⁠#⁠ SHORTER #⁠#⁠#⁠ than ECP lifetime. . .

Waiting for 4 seconds, press a key to continue ...3 2 1 0

Check result after "short delay":

MSG point-1
DTS 2021-01-07 10:30:16.2370
Does sequence 'g_eds_test' exists ? => NO, it doesn't

MSG point-2
DTS 2021-01-07 10:30:16.2560
Does sequence 'g_eds_test' exists ? => YES, 'G_EDS_TEST' exists.

INIT_SEQ_VALUE 0

SEQ_VALUE_AFTER_RECONNECT 0

GEN_VALUE_BEFORE_INC 123
GEN_VALUE_AFTER__INC 246

FINAL_SEQ_VALUE UNEXPECTED >>> 0 <<< ?

MSG point-3
DTS 2021-01-07 10:30:16.3380
Does sequence 'g_eds_test' exists ? => NO, it doesn't

=== bye-bye from SQL ===
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

As you can see, sequence is recreated every time.
But its *OLD* value (123) still visible to procedure 'sp_increment_sequence' when it is called via ES/EDS and - most important - time from previous call is less then ExtConnPoolLifeTime.

If we uncomment line "-- role 'A' || replace(lpad('',30,uuid_to_char(gen_uuid())),'-','')" and repeat than result is expected:

GEN_VALUE_BEFORE_INC 0
GEN_VALUE_AFTER__INC 123
FINAL_SEQ_VALUE Expected: 123

(using ROLE clause with random value forces EDS mechanism to create totally new connection, AFAIK).

PS. WI-V4.0.0.2307 Firebird 4.0 Release Candidate 1

=>

Steps to reproduce:

1) open firebird.conf and set ExtConnPoolLifeTime to value about 5...10 (I used 5); restart FB 4.x.

2) unpack batch from attached .7z; open it in editor and adjust following variables:

fbc // path to isql.exe from FB 4.x
dbnm // name of temporary database
usr // SYSDBA
psw // masterkey

This batch will create temporary database and .sql which does:

1) connect to this temp database;
2) run 'RECREATE SEQUENCE g_eds_test;'
3) creates procedure 'sp_increment_sequence' (its name speaks for itself)
4) RECONNECT to database;
5) run procedure 'sp_increment_sequence' with input arg. = 123.
6) check that final value of sequence 'g_eds_test' is 123

Then batch will parse firebird.conf (from !fbc!\ folder) and take value ExtConnPoolLifeTime from there (it must be uncommented, of course).

Finally, it calls just created .sql *three* times, with different interval between second and third: firs interval = (ExtConnPoolLifeTime + 1), second is (ExtConnPoolLifeTime - 1), i.e.:

t0 --> run #⁠1
t1 = return from sql (i.e. finish of run #⁠1)

.... wait for (ExtConnPoolLifeTime + 1) seconds ....

t2 = t1 + (ExtConnPoolLifeTime + 1) seconds --> run #⁠2
t3 = return from sql (i.e. finish of run #⁠2)

.... wait for (ExtConnPoolLifeTime - 1) seconds ....

t4 = t3 + (ExtConnPoolLifeTime - 1) seconds --> run #⁠3

Built-in Windows utility 'timeout' is used for pausesbetween launches of isql.

Here is the content of SQL:

set bail on;
set heading off;
set list on;
connect 'localhost:c:\temp\tmp4test.fdb' user SYSDBA password 'masterkey';
create or alter procedure sp_increment_sequence(a_inc int = null) as begin end;

recreate view v_check as
select cast('now' as timestamp) dts, coalesce( q'{YES, '}' || trim(g.rdb$generator_name) || q'{' exists.}', q'{NO, it doesn't}') as "Does sequence 'g_eds_test' exists ? =>"
from rdb$database r
left join rdb$generators g on upper(g.rdb$generator_name) = upper('g_eds_test');
commit;

select 'point-1' as msg, v.* from v_check v;
recreate sequence g_eds_test;
commit;
select 'point-2' as msg, v.* from v_check v;

select gen_id(g_eds_test, 0) as init_seq_value from rdb$database;
commit;

set term #⁠;
create or alter procedure sp_increment_sequence(a_inc int = null) returns(gen_value_before_inc int, gen_value_after__inc int) as
declare c int;
begin
gen_value_before_inc = gen_id(g_eds_test,0);
c = gen_id( g_eds_test, coalesce(a_inc,1) );
gen_value_after__inc = gen_id(g_eds_test,0);
suspend;
end
#⁠
commit
#⁠

-- #⁠#⁠#⁠ RECONNECT #⁠#⁠#⁠
connect 'localhost:c:\temp\tmp4test.fdb' user SYSDBA password 'masterkey'
#⁠

select gen_id(g_eds_test, 0) as seq_value_after_reconnect from rdb$database
#⁠
commit
#⁠

execute block returns(gen_value_before_inc int, gen_value_after__inc int) as
begin
execute statement ( 'select gen_value_before_inc, gen_value_after__inc from sp_increment_sequence( ? )' ) ( 123 )
on external 'localhost:' || rdb$get_context('SYSTEM','DB_NAME') as user 'SYSDBA' password 'masterkey'
-- role 'A' || replace(lpad('',30,uuid_to_char(gen_uuid())),'-','') --------------------------------------------- ::::: NB :::::
into gen_value_before_inc, gen_value_after__inc
;
suspend;
end
#⁠
set term ;#⁠

select iif( gen_id(g_eds_test, 0) > 0, 'Expected: ' || gen_id(g_eds_test,0), 'UNEXPECTED >>> ' || gen_id(g_eds_test,0) || ' <<< ?' ) as final_seq_value from rdb$database;
commit;

drop procedure sp_increment_sequence;
drop sequence g_eds_test;
commit;

select 'point-3' as msg, v.* from v_check v;
commit;

set list off;
set heading off;
select '=== bye-bye from SQL ===' from rdb$database;

PLEASE NOTE ON COMMENTED LINE:
" -- role 'A' || replace(lpad('',30,uuid_to_char(gen_uuid())),'-','') --------------------------------------------- ::::: NB :::::"

Run this batch.

This is result of batch when it runs with ExtConnPoolLifeTime = 5:
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
MSG point-1
DTS 2021-01-07 10:30:04.5180
Does sequence 'g_eds_test' exists ? => NO, it doesn't

MSG point-2
DTS 2021-01-07 10:30:04.5290
Does sequence 'g_eds_test' exists ? => YES, 'G_EDS_TEST' exists.

INIT_SEQ_VALUE 0

SEQ_VALUE_AFTER_RECONNECT 0

GEN_VALUE_BEFORE_INC 0
GEN_VALUE_AFTER__INC 123

FINAL_SEQ_VALUE Expected: 123

MSG point-3
DTS 2021-01-07 10:30:05.0920
Does sequence 'g_eds_test' exists ? => NO, it doesn't

=== bye-bye from SQL ===

Take delay for 6 s, i.e. #⁠#⁠#⁠ GREATER #⁠#⁠#⁠ than ECP lifetime. . .

Waiting for 6 seconds, press a key to continue ...5 4 3 2 1 0

Check result after "long delay":

MSG point-1
DTS 2021-01-07 10:30:11.5640
Does sequence 'g_eds_test' exists ? => NO, it doesn't

MSG point-2
DTS 2021-01-07 10:30:11.5790
Does sequence 'g_eds_test' exists ? => YES, 'G_EDS_TEST' exists.

INIT_SEQ_VALUE 0

SEQ_VALUE_AFTER_RECONNECT 0

GEN_VALUE_BEFORE_INC 0
GEN_VALUE_AFTER__INC 123

FINAL_SEQ_VALUE Expected: 123

MSG point-3
DTS 2021-01-07 10:30:12.1600
Does sequence 'g_eds_test' exists ? => NO, it doesn't

=== bye-bye from SQL ===

Take delay for 4 s, i.e. #⁠#⁠#⁠ SHORTER #⁠#⁠#⁠ than ECP lifetime. . .

Waiting for 4 seconds, press a key to continue ...3 2 1 0

Check result after "short delay":

MSG point-1
DTS 2021-01-07 10:30:16.2370
Does sequence 'g_eds_test' exists ? => NO, it doesn't

MSG point-2
DTS 2021-01-07 10:30:16.2560
Does sequence 'g_eds_test' exists ? => YES, 'G_EDS_TEST' exists.

INIT_SEQ_VALUE 0

SEQ_VALUE_AFTER_RECONNECT 0

GEN_VALUE_BEFORE_INC 123
GEN_VALUE_AFTER__INC 246

FINAL_SEQ_VALUE UNEXPECTED >>> 0 <<< ?

MSG point-3
DTS 2021-01-07 10:30:16.3380
Does sequence 'g_eds_test' exists ? => NO, it doesn't

=== bye-bye from SQL ===
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

As you can see, sequence is recreated every time.
But its *OLD* value (123) still visible to procedure 'sp_increment_sequence' when it is called via ES/EDS and - most important - time from previous call is less then ExtConnPoolLifeTime.

If we uncomment line "-- role 'A' || replace(lpad('',30,uuid_to_char(gen_uuid())),'-','')" and repeat than result is expected:

GEN_VALUE_BEFORE_INC 0
GEN_VALUE_AFTER__INC 123
FINAL_SEQ_VALUE Expected: 123

(using ROLE clause with random value forces EDS mechanism to create totally new connection, AFAIK).

PS. WI-V4.0.0.2307 Firebird 4.0 Release Candidate 1

PPS.
UPD, 03.02.21. Changed title after Vlad's request.

summary: ES/EDS and ExtConnPool problem: EDS-connection may see old value of generator if it is established twise and interval is less than ExtConnPoolLifeTime (even when generator is RECREATED every time) => Stale metadata for generator could be used

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