Execute metadata queries in snapshot mode
Details
| Detail name | Value |
|---|---|
| Changelog Number | 7160 |
| Type | New Feature |
| Status | Resolved |
| Fix Versions | Exasol 7.0.0, Exasol 6.2.5 |
| Resolution Date | 2020-09-11 |
Problem to solve
BI tools / frontends need to get metadata from Exasol, e.g. to display information about existing tables.
If there is database specific information which cannot be retrieved by standard metadata calls or if the tool doesn't use standard metadata calls for any reason, SQL queries have to be used (SELECT from system tables). The problem with normal SQL queries is that they may lead to locks.
That is why we need a way to query metadata using a lock-free SQL query.
Solution
Instead of sending a normal SQL statement in the Execute command, we start the statement with a magic phrase that tells the database to execute the following command in snapshot mode. Snapshot mode means that the transaction is read-only and that the newest object version is always used. Writing data is not supported in snapshot mode, e.g. by using an INSERT statement.
Developers of 3rd party SQL clients only need to add this magic phrase in front of their statements.
Structure of the statement:
/*snapshot execution*/ <sql statement>
"/*snapshot execution*/" is the magic phrase at the beginning of the statement.
This is followed by an SQL statement.
If the SQL process finds the magic phrase at the beginning of the statement, it will start the snapshot mode. After the execution the snapshot mode is stopped again.
If this phrase is used in older database versions that do not support the snapshot mode, the statement will be executed without using the snapshot mode.
Use snapshot mode only to perform SELECT from system tables.
Snapshot mode is not supported for prepared statements.
