Skip to content

Babelfish 3.4.0 for PostgreSQL 15.5
Compare
Choose a tag to compare
@shardgupta shardgupta released this 09 Feb 12:46
BABEL_3_4_0__PG_15_5
a2ab3b3
This commit was created on GitHub.com and signed with GitHub’s verified signature. The key has expired.
GPG key ID: 4AEE18F83AFDEB23
Expired
Learn about vigilant mode.

Overview

Babelfish 3.4.0 contains enhancements and fixes that improve the user experience, including:

New features

  • Added support for Logical Database level and Instance level dump and restore between the same Babelfish versions
  • Added support for TSQL Isolation Level SERIALIZABLE and REPEATABLE READ with PostgreSQL semantics
  • Added support for enable / disable triggers
  • Added support for TSQL functions DATETRUNC(), DATE_BUCKET(), SWITCHOFFSET(), TODATETIMEOFFSET(), and AT TIME ZONE clause
  • Added support for TSQL functions TYPE_ID(), TYPE_NAME(), COL_LENGTH(), COL_NAME()
  • Added support for DEFAULT keyword in calls to stored procedures and functions
  • Added support for casting DATETIME to numeric types
  • Added support for DBCC CHECKIDENT for ability to reset IDENTITY columns
  • Added support for PRIMARY KEY NOT NULL IDENTITY clause in CREATE/ALTER TABLE
  • Added support for double-quoted strings containing single-quote and embedded double quotes in a double-quoted string
  • Added support for ALTER AUTHORIZATION syntax to change database owner
  • Added support for TSQL KILL command
  • Added support for TSQL Information_schema.key_column_usage view
  • Added support of variable as input for SET ROWCOUNT and SET DATEFIRST
  • Added support for sys.server_role members catalog
  • Added support for IDENTITY() function in a Select-Into statement. In Babelfish, column specified as Identity will always be the last column in the new table. Due to this slight difference compared with SQL server, this feature needs to be used as an escape hatch babelfishpg_tsql.escape_hatch_identity_function. User-defined datatypes for IDENTITY() function are not currently supported.
  • Added support for ALTER USER...WITH LOGIN syntax
  • Added support of variable as input for SET ROWCOUNT and SET DATEFIRST
  • Added support for change in transaction isolation from inside transaction block with well defined behavior
  • Added support for casting datetime and smalldatetime to numeric types
  • Added support for PIVOT in limited scope (not supported when used in a view definition, a common table expression, or a join)

Security enhancements

  • Fixed permission issue for view sys.server_principals.

Critical stability enhancements

  • Fixed an issue where ISNULL function may return incorrect data type.
  • Fixed an issue where condition may be evaluated incorrectly for conditional statement (e.g., IF)
  • Fixed an error "database ... does not exist" that may be observed when parallel query is enforced.
  • Fixed handling of Table Variable or Temp Table when Parallel worker is enforced.
  • Fixed unexpected error "lost connection to parallel worker" occurring when parallel worker enforced
  • Fixed an issue with multiple parentheses in select columns. (Contributed by staticlibs)
  • Fixed an issue with handling of column name alias which may cause client to hang if column name alias contains string of length more than 64 bytes, for example, select col as '您对“数据一览“中的车型，颜色，内饰，选装, '
  • Fixed datatype of information_schema_tsql.tables.TABLE_TYPE column
  • Fixed the error - “column ... does not exist” when using table.column with alias defined for table or schema_name.table.column in set clause of update queries
  • Fixed issue of incorrect schema resolution for multiple functions in query statement
  • Fixed an issue for a few variants of DELETE with OUTPUT clause combined with table alias returns an error
  • Fixed performance issue while expanding stored procedures in SSMS Object Explorer
  • Fixed a crash when UNION with NULL values not cast to fixed-length types
  • Fixed SESSION_USER/SYSTEM_USER in SET/PRINT/DECLARE variable assignment returning wrong result/error
  • Fixed issue of blocking of UNIQUE constraint/index on nullable column not implemented consistently
  • Fix a crash with T-SQL OPENQUERY() and four-part object name when T-SQL keywords are used as server name.
  • Fixed the issue of update with TOP, OUTPUT and join failing with error ‘unrecognized node type’
  • Fixed the issue of VALUES clause with mixed types gives error containing the clause ‘Please use an explicit CAST or CONVERT’
  • Fixed an issue of different assignments of identity values compared with SQL Server when order by is used with select into statement
  • Fix incorrect schema resolution where multiple functions are called in single statement

High Priority stability enhancements

  • Fixed type conversion between Varchar and Binary datatype with use of proper encoding.
  • Fixed an issue where upper/lower case may not be preserved for column name aliases
  • Fixed crash in queries involving money data-type in parallel query mode
  • Fixed failure in MVU with non-default server collation name
  • Fixed the issue of information_schema vs. sys.objects WHERE type IN ('U', 'V') giving different result in Babelfish
  • Fixed issue of sp_columns and sp_columns_100 incorrectly show NULL radix for decimal columns
  • Fixed issue in queries involving sys.format() function in parallel query mode returning error “cannot start subtransactions during a parallel operation”.
  • Fixed unexpected error “could not access file "pg_hint_plan": No such file or directory" while using pg_hint_plan in parallel query mode
  • Fixed the issue of getting error ‘duplicate key value violates unique constraint ...' when re-creating the previously dropped view with the same name.
  • Fix infinite recursion in yyfree. (Contributed by staticlibs)

Additional improvements and enhancements

  • Improved performance for stored procedure sp_describe_undeclared_parameters
  • Fixed performance issue for DATEADD(), DATEDIFF()
  • SSMS - Fixed issue of Stored Procedure takes long time to load in Object Explorer
  • SSMS - Fixed performance issue of enumerating tables and views in SSMS Object Explorer
  • Fixed performance issue after create/upgrade of Babelfish extension by running ANALYZE after Babelfish extension creation and upgrade
  • Fixed the issue of index not used when query has an unnecessary cast to bigint
  • Fixed an issue when stored procedures starting with (sp_*) are invoked with a dbo. or sys. prefix.
  • Fixed the issue with default_schema_name column of the catalog sys.babelfish_authid_user_ext in case of "guest" user.
  • Fixed issue of orphan entries in Babelfish view def catalog table
  • Fixed an issue with UNION and fixed-length types
  • Fixed performance issue with '+' operator in concatenation operation
  • Fixed performance issue by optimizing use of internal function during index creation and usage in queries
  • Fixed an issue when comparing BIT and VARCHAR types
  • Performance improvements for create/drop database with large number of databases
  • Added sort operators for Babelfish datatypes, so that MAX/MIN aggregation on index column can have a query plan candidate of LIMIT 1 and index scan
  • Fixed nulls order of Babelfish indexes, so that TOP 1 clause on index column can have a query plan candidate of LIMIT 1 and index scan
  • Fixed a crash with SSMS in Table properties dialog box while clicking on Permissions page
  • Restricted use of view as a target with OUTPUT INTO clause

Upgrading to the new version

If you’re running Babelfish in a managed environment, please check your vendor’s instructions for upgrading.

MINOR VERSION UPGRADE

Babelfish 3.4.0 is compatible with 3.3.0, 3.2.0 and 3.1.0 versions of Babelfish; a dump/restore is not required to upgrade to the newer version from previous releases. To upgrade:

  1. Install the new binaries; for details, see [Compiling Babelfish From Source] (https://babelfishpg.org/docs/installation/compiling-babelfish-from-source/).
  2. Start the new server.
  3. Update the modified extensions.

To update the extensions that have changed since the last release, connect to the PostgreSQL endpoint of the Babelfish database and execute the following statements from a single session in this order:

ALTER EXTENSION "babelfishpg_common" UPDATE;
ALTER EXTENSION "babelfishpg_tsql" UPDATE;

Please note that the extension owner must invoke ALTER EXTENSION. For this release, the babelfish_tds and babelfish_money extensions don’t include any SQL-level changes and need not be updated.
After the extensions are updated, you can check the extension versions from the PostgreSQL port:

babelfish_db=# \dx
List of installed extensions
Name        | Version |   Schema   |   Description
--------------------+---------+------------+-------------------------------------------------
babelfishpg_common | 3.3.0   | public     | Transact SQL Datatype Support
....
babelfishpg_tsql   | 3.4.0   | public     | Transact SQL Compatibility
....

MAJOR VERSION UPGRADE

Babelfish 3.4.0 provides the ability to upgrade from some 2.x.x versions of Babelfish. This process is called major version upgrade (MVU). It consists of two steps. First, you need to perform a major version upgrade on the PostgreSQL server to upgrade it to the target version. Second, you need to update all Babelfish extensions to the target version. Currently, Babelfish only supports upgrade using the pg_upgrade module. Other upgrade methods such as dump/restore and replication are not supported.

Assets 4