Babelfish escape hatches
To better deal with statements that might fail, Babelfish allows you to define escape hatches. An escape hatch is a flag that can be adjusted by the user and specifies the behavior of Babelfish when it encounters an unsupported feature or syntax.
You can use the sp_babelfish_configure stored procedure to display or change the settings of each escape hatch:
- If an escape hatch is set to
ignore, Babelfish will suppress the error that the corresponding syntax would otherwise cause. By default, the change applies to the current session only. Include theserverkeyword to apply the changes persistently on the cluster level as well. - If an escape hatch is set to
default, Babelfish will restore the installation default settings for escape hatches. - If an escape hatch is set to
strict, you may encounter error messages if your SQL code contains unsupported syntax. To suppress these error messages, set all escape hatches toignoreby invoking the following command on the TDS port:
sp_babelfish_configure '%', 'ignore', 'server', 'default'
The following table lists the escape hatches and their default settings:
| Escape hatch | Description | Default |
|---|---|---|
babelfishpg_tsql.escape_hatch_constraint_name_for_default | Controls Babelfish behavior related to default constraint names. | strict |
babelfishpg_tsql.escape_hatch_database_misc_options | Controls Babelfish behavior related to the following options on CREATE or ALTER DATABASE: CONTAINMENT, DB_CHAINING, TRUSTWORTHY, PERSISTENT_LOG_BUFFER. | ignore |
babelfishpg_tsql.escape_hatch_for_replication | Controls Babelfish behavior related to the [NOT] FOR REPLICATION clause when creating or altering a table. | strict |
babelfishpg_tsql.escape_hatch_fulltext | Controls Babelfish behavior related to FULLTEXT features, such as DEFAULT_FULLTEXT_LANGUAG in CREATE/ALTER DATABASE, CREATE FULLTEXT INDEX, or sp_fulltext_database. | strict |
babelfishpg_tsql.escape_hatch_index_clustering | Controls Babelfish behavior related to the CLUSTERED or NONCLUSTERED keywords for indexes and PRIMARY KEY or UNIQUE constraints. When CLUSTERED is ignored, the index or constraint is still created as if NONCLUSTERED was specified. | ignore |
babelfishpg_tsql.escape_hatch_index_columnstore | Controls Babelfish behavior related to the COLUMNSTORE clause. If you specify ignore, Babelfish creates a regular B-tree index. | strict |
babelfishpg_tsql.escape_hatch_ignore_dup_key | Controls Babelfish behavior related to CREATE/ALTER TABLE and CREATE INDEX. When escape_hatch_ignore_dup_key is set to strict, Babelfish raises an error or ignores the error when set to ignore. This escape hatch applies to Babelfish version 1.2.0 and higher. | strict |
babelfishpg_tsql.escape_hatch_join_hints | Controls the behavior of keywords in a JOIN operator: LOOP, HASH, MERGE, REMOTE, REDUCE, REDISTRIBUTE, REPLICATE. | ignore |
babelfishpg_tsql.escape_hatch_language_non_english | Controls Babelfish behavior related to languages other than English for onscreen messages. Babelfish currently supports only us_english for on-screen messages. SET LANGUAGE might use a variable containing the language name, so the actual language being set can only be detected at run time. | strict |
babelfishpg_tsql.escape_hatch_login_hashed_password | HASHED password is not supported for CREATE LOGIN and ALTER LOGIN. | strict |
babelfishpg_tsql.escape_hatch_login_misc_options | This deals with various other unsupported options for CREATE LOGIN and ALTER LOGIN | strict |
babelfishpg_tsql.escape_hatch_login_old_password | The OLD_PASSWORD option of ALTER LOGIN is not supported. | strict |
babelfishpg_tsql.escape_hatch_login_password_must_change | The MUST_CHANGE password option is not supported for CREATE LOGIN and ALTER LOGIN. | strict |
babelfishpg_tsql.escape_hatch_login_password_unlock | The UNLOCK password option is not supported for CREATE LOGIN and ALTER LOGIN. | strict |
babelfishpg_tsql.escape_hatch_nocheck_add_constraint | Controls Babelfish behavior related to the WITH CHECK or NOCHECK clause for constraints. | strict |
babelfishpg_tsql.escape_hatch_nocheck_existing_constraint | Controls Babelfish behavior related to FOREIGN KEY or CHECK constraints. | strict |
babelfishpg_tsql.escape_hatch_query_hints | Controls Babelfish behavior related to query hints. When this option is set to ignore, the server ignores hints that use the OPTION (...) clause to specify query processing aspects. Examples include SELECT FROM ... OPTION(MERGE JOIN HASH, MAXRECURSION 10)). | ignore |
babelfishpg_tsql.escape_hatch_rowguidcol_column | Controls Babelfish behavior related to the ROWGUIDCOL clause when creating or altering a table. | strict |
babelfishpg_tsql.escape_hatch_schemabinding_function | Controls Babelfish behavior related to the WITH SCHEMABINDING clause. By default, the WITH SCHEMABINDING clause is ignored when specified with the CREATE or ALTER FUNCTION command. | ignore |
babelfishpg_tsql.escape_hatch_schemabinding_procedure | Controls Babelfish behavior related to the WITH SCHEMABINDING clause. By default, the WITH SCHEMABINDING clause is ignored when specified with the CREATE or ALTER PROCEDURE command. | ignore |
babelfishpg_tsql.escape_hatch_schemabinding_trigger | Controls Babelfish behavior related to the WITH SCHEMABINDING clause. By default, the WITH SCHEMABINDING clause is ignored when specified with the CREATE or ALTER TRIGGER command. | ignore |
babelfishpg_tsql.escape_hatch_schemabinding_view | Controls Babelfish behavior related to the WITH SCHEMABINDING clause. By default, the WITH SCHEMABINDING clause is ignored when specified with the CREATE or ALTER VIEW command. | ignore |
babelfishpg_tsql.escape_hatch_session_settings | Controls Babelfish behavior toward unsupported session-level SET statements. | ignore |
babelfishpg_tsql.escape_hatch_showplan_all | Controls Babelfish behavior toward SET SHOWPLAN_ALL and SET STATISTICS PROFILE. When set to ignore, the SET SHOWPLAN_ALL and SET STATISTICS PROFILE will behave like SET BABELFISH_SHOWPLAN_ALL and SET BABELFISH_STATISTICS PROFILE; when set to strict, SET SHOWPLAN_ALL and SET STATISTICS PROFILE are silently ignored. Query plan information is displayed PostgreSQL-style, rather than SQL Server-style. For more information, see Reviewing a Query Plan. | strict |
babelfishpg_tsql.escape_hatch_storage_on_partition | Controls Babelfish behavior related to the ON partition_scheme column clause when defining partitioning. Babelfish currently doesn’t implement partitioning. | strict |
babelfishpg_tsql.escape_hatch_storage_options | Escape hatch on any storage option used in CREATE/ALTER for DATABASE, TABLE and INDEX. This includes the clauses (LOG) ON, TEXTIMAGE_ON, FILESTREAM_ON that define storage locations (partitions, filegroups) for tables, indexes, and constraints, and also for a database. This escape hatch setting applies to all of these clauses (including ON PRIMARY and ON DEFAULT). The exception is when a partition is specified for a table or index with ON partition_scheme (column). When escape_hatch_storage_options is set to ignore, no errors are raised for the ON clause in CREATE DATABASE. When escape_hatch_storage_options is set to ignore, no errors are raised for options SORT_IN_TEMPDB, DROP_EXISTING, and ONLINE in CREATE INDEX | ignore |
babelfishpg_tsql.escape_hatch_table_hints | Controls the behavior of table hints specified using the WITH (...) clause. | ignore |
babelfishpg_tsql.escape_hatch_unique_constraint | Controls Babelfish behavior toward unsupported session-level SET statements. Set to ignore to allow creation of nullable columns on which a UNIQUE index or UNIQUE constraint is defined. | strict |