Session with Add Privileges

The set session with add privileges option obtains a requestable privilege while connected to Ingres. A requestable privilege is defined in the privileges list of the alter profile, alter user, create profile, or create user statements, but is not defined in the corresponding default privileges list.

Session with Drop Privileges

The set session with drop privileges option removes all privileges (including default and requestable) for the current session.

Session with On_error

The set session with on_error option specifies how transaction errors are handled in the current session. To direct the DBMS Server to roll back the effects of the entire current transaction if an error occurs, specify rollback transaction. To direct the DBMS Server to rollback only the current statement (the default setting), specify rollback statement. To determine the current status of transaction error handling, issue the select dbmsinfo('on_error_state') statement.

Specifying rollback transaction reduces logging overhead, and can help performance. The performance gain is offset by the fact that, if an error occurs, the entire transaction is rolled back, not the single statement that caused the error.

The following errors always roll back the current transaction, regardless of the current transaction error-handling setting:

• Deadlock
• Forced abort
• Lock quota exceeded

To determine if a transaction was aborted as the result of a database statement error, issue the select dbmsinfo('transaction_state') statement. If the error aborted the transaction, this statement returns 0, indicating that the application is currently not in a transaction.

The set session with on_error statement cannot be issued from within a database procedure or multi-statement transaction.

Note: SQL syntax errors (including most messages beginning with E_US) do not cause a rollback. Only errors that occur during execution of the SQL statement cause rollback.

Session with On_user_error

The set session with on_user_error option enables you to specify how user errors are handled in the current session. To direct the DBMS to roll back the effects of the entire transaction if a user error occurs, specify rollback transaction. To revert back to default behavior, specify norollback.

Session with [No]Description

The set session with [no] description statement is used to identify the session to system administrators and other users who can monitor Ingres activity.

If nodescription is specified, the session description is set empty. If description is specified, text entered by the user is used as the session description.

The current session description can be displayed using iimonitor, ipm and ima. This description is visible in Ingres management and administration tools. The maximum length of a session description is 256 characters.

In ESQL, the session description can be specified using a string variable.

Session with Priority

The session_priority option sets the relative importance of various sessions. A session can have a higher (more important) or lower (less important) priority than a session with the base, or normal, priority.

Only users with the session_priority resource privilege can change their session priority, subject to the limit defined by the system administrator. Three fixed settings and one variable setting are available:

• Initial - If initial is specified, the value known at session startup time is used.
• Minimum - If minimum is specified, the lowest (least important) priority is used.
• Maximum - If maximum is specified, the highest (most important) priority is used.
• Priority (variable) - The priority variable is specified as an integer. If priority is specified, the variable used defines the requested session priority. A positive value sets a higher priority than the base priority; a negative value sets a lower priority than the base priority. Due to operating system restrictions, the number of values above and below the base priority can be limited.

Session with [No]Privileges

When an Ingres session starts, it receives a default set of privileges. The set session with [no]privileges statement can be used to add, drop or set the session effective privileges.

The following options are available:

• add privilege-The listed privileges are added to the pre-existing effective privileges for that session, provided the user has permission to use them.
• drop privilege-The listed privileges are removed from the effective privileges for that session.
• with noprivileges-The session has no effective privileges.
• with privileges=(priv {,priv})-The session privileges are replaced by the privileges listed, provided the user has permission to use them.
• with privileges=all-All granted privileges are added to the effective privileges for that session.
• with privileges=default-The session privileges are reset to the default privileges available at session start.

Note: Only one of these options can be specified. Ingres applications that require certain privileges must use this command to ensure the session has the required privileges prior to executing a privileged operation.

Session with on_logfull

When the transaction log file fills, the oldest transaction is, by default, aborted to free up log space. The set session with on_logfull statement can be used to modify the default behavior.

On_logfull can appear any number of times during a transaction, either within or without the scope of the transaction.

When specifying commit or notify, the multi-row updates can be partially committed if a logfull condition occurs. For example, using the following syntax commits the rows deleted up to the moment the logfull condition is detected.

[exec sql] delete from [schema.]table_name [corr_name]
[where search_condition]

If the transaction is aborted after the commit point, only the post-commit updates are rolled back.