Implement way to reset user session environment to its initial (default) state [CORE5832] #6093

firebird-automations · 2018-05-21T13:53:29Z

Submitted by: @hvlad

Resetting user session (connection) to its initial state should be useful when session is reused by application.
I.e. instead of detach\attach application could just reuse already established connection.
To reuse connection all its user context variables, contents of temporary tables etc should be cleared and all
session-level settings should be reset to its default values.

The proposed solution is to implement new session management SQL statement

ALTER SESSION RESET

It runs as follows:
- throw error (isc_ses_reset_err) if any open transaction exist in current conneciton, except of current transaction
and prepared 2PC transactions which is allowed and ignored by this check
- system variable RESETTING is set to true
- ON DISCONNECT database triggers is fired, if present and allowed for current connection
- ROLLBACK current user transaction (if present) and issue warning if that transaction changes any table before reset
- reset DECFLOAT parameters (BIND, TRAP and ROUND) to its default values
- reset session and statement timeouts to zero
- remove all context variables in 'USER_SESSION' namespace
- restore ROLE which was passed with DPB and clear all cached security classes (if role was changed)
- clear contents of all used GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS
- ON CONNECT database triggers is fired, if present and allowed for current connection
- START new transaction with the same properties as transaction that was rolled back (if transaction was present
before reset)
- system variable RESETTING is set to false

Note, CURRENT_USER and CURRENT_CONNECTION will not be changed.

New system variable RESETTING is introduced to allow detect case when database trigger is fired due to session
reset. It is awailable in triggers only and could be used in any place when boolean predicate could be used.
Its value is TRUE is session reset is in progress and FALSE otherwise.
RESETTING is reserved word now.

Errors handling.

Error raised at ON DISCONNECT trigger aborts session reset and leave session state not changed.
Such errors reported with primary error code isc_session_reset_err and error text
"Cannot reset user session".

Errors raised after ON DISCONNECT triggers aborts both session reset statement execution and connection itself.
Such errors reported with primary error code isc_session_reset_failed and error text
"Reset of user session failed. Connection is shut down.".
Subsequent operations on connection (except of detach) will fail with isc_att_shutdown error.

See also discussion at fb-devel, thread "RFC: External Connections Pool" started at 18.05.2018

Commits: 752424d 1d7c907 672a130

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

Following tests have been created in order to check issues related to ALTER SESSION RESET:

1) functional/session/ext-conn-pool-01.fbt -- for check:
"- system variable RESETTING is set to true"
"- ON DISCONNECT database triggers is fired, if present and allowed for current connection"
"- ON CONNECT database triggers is fired, if present and allowed for current connection"
"- system variable RESETTING is set to false"

2) functional/session/alter-session-reset-rollback.fbt -- for check:
"- ROLLBACK current user transaction (if present) and issue warning if that transaction changes any table before reset"

3) functional/session/alter-session-reset-decfloat.fbt -- for check:
"- reset DECFLOAT parameters (BIND, TRAP and ROUND) to its default values"

4) functional/session/alter-session-reset-clear-timeouts.fbt -- for check:
"- reset session and statement timeouts to zero"

5) functional/session/alter-session-reset-remove-context-vars.fbt -- for check:
"- remove all context variables in 'USER_SESSION' namespace"

6) functional/session/alter-session-reset-restore-role.fbt -- for check:
"- restore ROLE which was passed with DPB and clear all cached security classes (if role was changed)"

7) functional/session/alter-session-reset-clear-gtt.fbt -- for check:
"- clear contents of all used GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS"

8) functional/session/alter-session-reset-start-new-tx.fbt -- for check:
"- START new transaction with the same properties as transaction that was rolled back (if transaction was present before reset)"

9) functional/session/alter-session-reset-raise-if-open-tx.fbt -- for check:
"throw error (isc_ses_reset_err) if any open transaction exist in current conneciton"

10) functional/session/alter-session-reset-allow-2pc-prepared.fbt -- for check:
"throw error (isc_ses_reset_err) if any open transaction exist in current conneciton, except ... prepared 2PC transactions"

The text was updated successfully, but these errors were encountered:

firebird-automations · 2018-05-21T13:53:33Z

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

firebird-automations · 2018-05-21T14:05:59Z

Commented by: @aafemt

May be also release all prepared statements and temporary BLOBs.

In documentation should be recommendation to use this statement at the end, before returning of the connection to a pool in order to release server resources ASAP.

firebird-automations · 2018-05-21T14:34:26Z

Commented by: Omacht András (aomacht)

Hi!

As we have database triggers (on connect, on disconnect) in this case we should have "on reset" database trigger too.

András

firebird-automations · 2018-05-21T15:02:44Z

Commented by: @hvlad

> May be also release all prepared statements

I don't think it is a good idea. Application could hold prepared statements handles
with cached connection handle. And ES implementation already caches few.

> and temporary BLOBs.

Temporary blob not exists without transaction.
Clean conneciton have no active transactions.
So, we should check connection for existence of active transaction before reset and
- rollback all of them, or
- raise error

In case of exeсute statement engine will close connection if reset returns error, so it will rollback
such active transactions in any case. Presence of active transaction in this scenario means some
bug in exeсute statement implementation.

Anyway we must decide what to do with active transactions on session reset in general case.

firebird-automations · 2018-05-21T15:16:29Z

Commented by: @hvlad

András,

not sure i like idea to add new database trigger, but it make sence at first look.
I think we need to discuss it at fb-devel.

firebird-automations · 2018-05-21T16:54:55Z

Commented by: @asfernandes

> So, we should check connection for existence of active transaction before reset and

I think we should not do this.

It should be valid that an executing function/procedure calls the command to reset state, and for that, they need a transaction.

firebird-automations · 2018-05-23T11:56:00Z

Commented by: @hvlad

Initial implementation is committed. It corresponds to description at this ticket.

firebird-automations · 2018-05-23T13:49:15Z

Commented by: @mrotteveel

>> So, we should check connection for existence of active transaction before reset and

> I think we should not do this.

I think it should, but not as part of the ALTER SESSION RESET, but it should be part of the connection pool handling.

firebird-automations · 2018-05-23T14:46:43Z

Commented by: @hvlad

>>> So, we should check connection for existence of active transaction before reset and

>> I think we should not do this.

> I think it should, but not as part of the ALTER SESSION RESET, but it should be part of the connection pool handling.

Connection pool closes all open statements and transactions (known to it) before external connection is
considered as not used.

But, ALTER SESSION RESET is not a part of connection pool and should be considered as independent feature.

firebird-automations · 2018-05-25T17:08:23Z

Commented by: @hvlad

Adriano,

>> So, we should check connection for existence of active transaction before reset and

> I think we should not do this.

We can't release GTT's data if active transaction made changes to it.
Else on rollback we will have big problems.

> It should be valid that an executing function/procedure calls the command to reset state, and for that, they need a transaction.

I don't see mng_statement (session management statements) at allowed at proc_statement (statements, allowed inside PSQL block).

firebird-automations · 2018-05-25T17:25:17Z

Commented by: @asfernandes

Vlad,

Please see how Oracle does it with dbms_session.modify_package_state.

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sessio.htm#CEGJHGDB

This note is important, and we can do as it: "This takes effect after the PL/SQL call that made the current invocation finishes running."

firebird-automations · 2018-05-25T18:17:35Z

Commented by: @hvlad

Adriano,

it looks like hack for me.

But. Even if we will actually apply session management statements on end current PSQL object execution:
- how\when it should affect session environment of caller PSQL object (it is not clear in ORACLE case) ?
- how it should affect active requests in other transactions of current attachment ?
- how it helps to not crash on attempt to undo changes in already truncated GTT's (due to rollback or exception\savepoint backout) ?

firebird-automations · 2018-06-01T14:48:53Z

Commented by: @hvlad

Description is updated according to latest changes.

firebird-automations · 2018-06-01T14:48:53Z

Modified by: @hvlad

description: Resetting user session (connection) to its initial state should be useful when session is reused by application.
I.e. instead of detach\attach application could just reuse already established connection.
To reuse connection all its user context variables, contents of temporary tables etc should be cleared and all
session-level settings should be reset to its default values.

The proposed solution is to implement new session management SQL statement

ALTER SESSION RESET

It will
- reset DECFLOAT parameters (BIND, TRAP and ROUND) to its default values
- reset session and statement timeouts to zero
- remove all context variables in 'USER_SESSION' namespace
- clear contents of all used GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS
- restore ROLE which was passed with DPB and clear all cached security classes (if role was changed)

Note, CURRENT_USER will not be changed.

See also discussion at fb-devel, thread "RFC: External Connections Pool" started at 18.05.2018

=>

Resetting user session (connection) to its initial state should be useful when session is reused by application.
I.e. instead of detach\attach application could just reuse already established connection.
To reuse connection all its user context variables, contents of temporary tables etc should be cleared and all
session-level settings should be reset to its default values.

The proposed solution is to implement new session management SQL statement

ALTER SESSION RESET

It requires that no active transactions were exists in connection. Therefore it should run with NULL transaction
handle (using IAttachment::execute() or isc_dsql_execute_immediate()) else error will be returned.

It will
- throw error (isc_ses_reset_err) if any open transaction exist in current conneciton
- reset DECFLOAT parameters (BIND, TRAP and ROUND) to its default values
- reset session and statement timeouts to zero
- remove all context variables in 'USER_SESSION' namespace
- clear contents of all used GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS
- restore ROLE which was passed with DPB and clear all cached security classes (if role was changed)

Note, CURRENT_USER will not be changed.

See also discussion at fb-devel, thread "RFC: External Connections Pool" started at 18.05.2018

firebird-automations · 2018-06-10T14:04:02Z

Commented by: @hvlad

Update description

firebird-automations · 2018-06-10T14:04:02Z

Modified by: @hvlad

description: Resetting user session (connection) to its initial state should be useful when session is reused by application.
I.e. instead of detach\attach application could just reuse already established connection.
To reuse connection all its user context variables, contents of temporary tables etc should be cleared and all
session-level settings should be reset to its default values.

The proposed solution is to implement new session management SQL statement

ALTER SESSION RESET

It requires that no active transactions were exists in connection. Therefore it should run with NULL transaction
handle (using IAttachment::execute() or isc_dsql_execute_immediate()) else error will be returned.

It will
- throw error (isc_ses_reset_err) if any open transaction exist in current conneciton
- reset DECFLOAT parameters (BIND, TRAP and ROUND) to its default values
- reset session and statement timeouts to zero
- remove all context variables in 'USER_SESSION' namespace
- clear contents of all used GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS
- restore ROLE which was passed with DPB and clear all cached security classes (if role was changed)

Note, CURRENT_USER will not be changed.

See also discussion at fb-devel, thread "RFC: External Connections Pool" started at 18.05.2018

=>

Resetting user session (connection) to its initial state should be useful when session is reused by application.
I.e. instead of detach\attach application could just reuse already established connection.
To reuse connection all its user context variables, contents of temporary tables etc should be cleared and all
session-level settings should be reset to its default values.

The proposed solution is to implement new session management SQL statement

ALTER SESSION RESET

It requires that no active transactions were exists in connection. Therefore it should run with NULL transaction
handle (using IAttachment::execute() or isc_dsql_execute_immediate()) else error will be returned.

It will
- throw error (isc_ses_reset_err) if any open transaction exist in current conneciton, except of prepared 2PC
transactions which is allowed and ignored by this check
- rollback current user transaction (if present) and issue warning if that transaction changes any table before reset
- reset DECFLOAT parameters (BIND, TRAP and ROUND) to its default values
- reset session and statement timeouts to zero
- remove all context variables in 'USER_SESSION' namespace
- clear contents of all used GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS
- restore ROLE which was passed with DPB and clear all cached security classes (if role was changed)
- start new transaction with the same properties as transaction that was rolled back (if transaction was present
before reset)

Note, CURRENT_USER will not be changed.

See also discussion at fb-devel, thread "RFC: External Connections Pool" started at 18.05.2018

firebird-automations · 2018-06-10T14:04:56Z

Modified by: @hvlad

description: Resetting user session (connection) to its initial state should be useful when session is reused by application.
I.e. instead of detach\attach application could just reuse already established connection.
To reuse connection all its user context variables, contents of temporary tables etc should be cleared and all
session-level settings should be reset to its default values.

The proposed solution is to implement new session management SQL statement

ALTER SESSION RESET

It requires that no active transactions were exists in connection. Therefore it should run with NULL transaction
handle (using IAttachment::execute() or isc_dsql_execute_immediate()) else error will be returned.

It will
- throw error (isc_ses_reset_err) if any open transaction exist in current conneciton, except of prepared 2PC
transactions which is allowed and ignored by this check
- rollback current user transaction (if present) and issue warning if that transaction changes any table before reset
- reset DECFLOAT parameters (BIND, TRAP and ROUND) to its default values
- reset session and statement timeouts to zero
- remove all context variables in 'USER_SESSION' namespace
- clear contents of all used GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS
- restore ROLE which was passed with DPB and clear all cached security classes (if role was changed)
- start new transaction with the same properties as transaction that was rolled back (if transaction was present
before reset)

Note, CURRENT_USER will not be changed.

See also discussion at fb-devel, thread "RFC: External Connections Pool" started at 18.05.2018

=>

Resetting user session (connection) to its initial state should be useful when session is reused by application.
I.e. instead of detach\attach application could just reuse already established connection.
To reuse connection all its user context variables, contents of temporary tables etc should be cleared and all
session-level settings should be reset to its default values.

The proposed solution is to implement new session management SQL statement

ALTER SESSION RESET

It will run as follows:
- throw error (isc_ses_reset_err) if any open transaction exist in current conneciton, except of prepared 2PC
transactions which is allowed and ignored by this check
- rollback current user transaction (if present) and issue warning if that transaction changes any table before reset
- reset DECFLOAT parameters (BIND, TRAP and ROUND) to its default values
- reset session and statement timeouts to zero
- remove all context variables in 'USER_SESSION' namespace
- clear contents of all used GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS
- restore ROLE which was passed with DPB and clear all cached security classes (if role was changed)
- start new transaction with the same properties as transaction that was rolled back (if transaction was present
before reset)

Note, CURRENT_USER will not be changed.

See also discussion at fb-devel, thread "RFC: External Connections Pool" started at 18.05.2018

firebird-automations · 2020-11-30T14:41:14Z

Commented by: @hvlad

Updated description : added details about database triggers and error handling.

firebird-automations · 2020-11-30T14:41:14Z

Modified by: @hvlad

description: Resetting user session (connection) to its initial state should be useful when session is reused by application.
I.e. instead of detach\attach application could just reuse already established connection.
To reuse connection all its user context variables, contents of temporary tables etc should be cleared and all
session-level settings should be reset to its default values.

The proposed solution is to implement new session management SQL statement

ALTER SESSION RESET

It will run as follows:
- throw error (isc_ses_reset_err) if any open transaction exist in current conneciton, except of prepared 2PC
transactions which is allowed and ignored by this check
- rollback current user transaction (if present) and issue warning if that transaction changes any table before reset
- reset DECFLOAT parameters (BIND, TRAP and ROUND) to its default values
- reset session and statement timeouts to zero
- remove all context variables in 'USER_SESSION' namespace
- clear contents of all used GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS
- restore ROLE which was passed with DPB and clear all cached security classes (if role was changed)
- start new transaction with the same properties as transaction that was rolled back (if transaction was present
before reset)

Note, CURRENT_USER will not be changed.

See also discussion at fb-devel, thread "RFC: External Connections Pool" started at 18.05.2018

=>

Resetting user session (connection) to its initial state should be useful when session is reused by application.
I.e. instead of detach\attach application could just reuse already established connection.
To reuse connection all its user context variables, contents of temporary tables etc should be cleared and all
session-level settings should be reset to its default values.

The proposed solution is to implement new session management SQL statement

ALTER SESSION RESET

It runs as follows:
- throw error (isc_ses_reset_err) if any open transaction exist in current conneciton, except of current transaction
and prepared 2PC transactions which is allowed and ignored by this check
- system variable RESETTING is set to true
- ON DISCONNECT database triggers is fired, if present and allowed for current connection
- ROLLBACK current user transaction (if present) and issue warning if that transaction changes any table before reset
- reset DECFLOAT parameters (BIND, TRAP and ROUND) to its default values
- reset session and statement timeouts to zero
- remove all context variables in 'USER_SESSION' namespace
- restore ROLE which was passed with DPB and clear all cached security classes (if role was changed)
- clear contents of all used GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS
- ON CONNECT database triggers is fired, if present and allowed for current connection
- START new transaction with the same properties as transaction that was rolled back (if transaction was present
before reset)
- system variable RESETTING is set to false

Note, CURRENT_USER and CURRENT_CONNECTION will not be changed.

New system variable RESETTING is introduced to allow detect case when database trigger is fired due to session
reset. It is awailable in triggers only and could be used in any place when boolean predicate could be used.
Its value is TRUE is session reset is in progress and FALSE otherwise.

Errors handling.

Error raised at ON DISCONNECT trigger aborts session reset and leave session state not changed.
Such errors reported with primary error code isc_session_reset_err and error text
"Cannot reset user session".

Errors raised after ON DISCONNECT triggers aborts both session reset statement execution and connection itself.
Such errors reported with primary error code isc_session_reset_failed and error text
"Reset of user session failed. Connection is shutted down.".
Subsequent operations on connection (except of detach) will fail with isc_att_shutdown error.

See also discussion at fb-devel, thread "RFC: External Connections Pool" started at 18.05.2018

firebird-automations · 2020-11-30T19:32:42Z

Modified by: @hvlad

Fix Version: 4.0 RC 1 [ 10930 ]

description: Resetting user session (connection) to its initial state should be useful when session is reused by application.
I.e. instead of detach\attach application could just reuse already established connection.
To reuse connection all its user context variables, contents of temporary tables etc should be cleared and all
session-level settings should be reset to its default values.

The proposed solution is to implement new session management SQL statement

ALTER SESSION RESET

It runs as follows:
- throw error (isc_ses_reset_err) if any open transaction exist in current conneciton, except of current transaction
and prepared 2PC transactions which is allowed and ignored by this check
- system variable RESETTING is set to true
- ON DISCONNECT database triggers is fired, if present and allowed for current connection
- ROLLBACK current user transaction (if present) and issue warning if that transaction changes any table before reset
- reset DECFLOAT parameters (BIND, TRAP and ROUND) to its default values
- reset session and statement timeouts to zero
- remove all context variables in 'USER_SESSION' namespace
- restore ROLE which was passed with DPB and clear all cached security classes (if role was changed)
- clear contents of all used GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS
- ON CONNECT database triggers is fired, if present and allowed for current connection
- START new transaction with the same properties as transaction that was rolled back (if transaction was present
before reset)
- system variable RESETTING is set to false

Note, CURRENT_USER and CURRENT_CONNECTION will not be changed.

New system variable RESETTING is introduced to allow detect case when database trigger is fired due to session
reset. It is awailable in triggers only and could be used in any place when boolean predicate could be used.
Its value is TRUE is session reset is in progress and FALSE otherwise.

Errors handling.

Error raised at ON DISCONNECT trigger aborts session reset and leave session state not changed.
Such errors reported with primary error code isc_session_reset_err and error text
"Cannot reset user session".

Errors raised after ON DISCONNECT triggers aborts both session reset statement execution and connection itself.
Such errors reported with primary error code isc_session_reset_failed and error text
"Reset of user session failed. Connection is shutted down.".
Subsequent operations on connection (except of detach) will fail with isc_att_shutdown error.

See also discussion at fb-devel, thread "RFC: External Connections Pool" started at 18.05.2018

=>

Resetting user session (connection) to its initial state should be useful when session is reused by application.
I.e. instead of detach\attach application could just reuse already established connection.
To reuse connection all its user context variables, contents of temporary tables etc should be cleared and all
session-level settings should be reset to its default values.

The proposed solution is to implement new session management SQL statement

ALTER SESSION RESET

It runs as follows:
- throw error (isc_ses_reset_err) if any open transaction exist in current conneciton, except of current transaction
and prepared 2PC transactions which is allowed and ignored by this check
- system variable RESETTING is set to true
- ON DISCONNECT database triggers is fired, if present and allowed for current connection
- ROLLBACK current user transaction (if present) and issue warning if that transaction changes any table before reset
- reset DECFLOAT parameters (BIND, TRAP and ROUND) to its default values
- reset session and statement timeouts to zero
- remove all context variables in 'USER_SESSION' namespace
- restore ROLE which was passed with DPB and clear all cached security classes (if role was changed)
- clear contents of all used GLOBAL TEMPORARY TABLE ... ON COMMIT PRESERVE ROWS
- ON CONNECT database triggers is fired, if present and allowed for current connection
- START new transaction with the same properties as transaction that was rolled back (if transaction was present
before reset)
- system variable RESETTING is set to false

Note, CURRENT_USER and CURRENT_CONNECTION will not be changed.

New system variable RESETTING is introduced to allow detect case when database trigger is fired due to session
reset. It is awailable in triggers only and could be used in any place when boolean predicate could be used.
Its value is TRUE is session reset is in progress and FALSE otherwise.
RESETTING is reserved word now.

Errors handling.

Error raised at ON DISCONNECT trigger aborts session reset and leave session state not changed.
Such errors reported with primary error code isc_session_reset_err and error text
"Cannot reset user session".

Errors raised after ON DISCONNECT triggers aborts both session reset statement execution and connection itself.
Such errors reported with primary error code isc_session_reset_failed and error text
"Reset of user session failed. Connection is shut down.".
Subsequent operations on connection (except of detach) will fail with isc_att_shutdown error.

See also discussion at fb-devel, thread "RFC: External Connections Pool" started at 18.05.2018

firebird-automations · 2021-01-17T14:24:17Z

Modified by: @hvlad

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

resolution: Fixed [ 1 ]

firebird-automations · 2021-01-19T01:33:22Z

Modified by: @pavel-zotov

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

QA Status: No test => Covered by another test(s)

Test Details: Following tests have been created in order to check issues related to ALTER SESSION RESET: