[Bug]: SYS.INDEXES does not work properly

[kewinbrand]

What happened?

we use the SYS.INDEXES table during our update process, but it seems that babelfish is not converting the names correctly. see example

Script

use [master]
drop database if exists index_testes
create database index_testes

use [index_testes]

CREATE TABLE [dbo].[ESTOQUE_EMPRESA](
	[R_E_C_N_O_] [int] IDENTITY(1,1) NOT NULL,
	[CODIGO] [varchar](50) NULL,
	[Id] [uniqueidentifier] NOT NULL,
	CONSTRAINT [PK_ESTOQUE_EMPRESA] PRIMARY KEY CLUSTERED 
	(
		[R_E_C_N_O_] ASC
	),
	UNIQUE NONCLUSTERED 
	(
		[Id] ASC
	)
)
GO

CREATE NONCLUSTERED INDEX [IX_ESTOQUE_EMPRESA_CODIGO] ON ESTOQUE_EMPRESA ([CODIGO])
GO

IF NOT EXISTS (SELECT TOP 1 1 FROM SYS.INDEXES WHERE NAME = 'IX_ESTOQUE_EMPRESA_CODIGO') 
BEGIN
  CREATE NONCLUSTERED INDEX [IX_ESTOQUE_EMPRESA_CODIGO] ON ESTOQUE_EMPRESA ([CODIGO])
END
GO

use [master]
GO

fails with Msg 2714, Level 16, State 1, Line 27 relation "ix_estoque_empresa_codigoestoquab8ae8734a5c39898c705577384d1f72" already exists

it is also worth to mention that we are using BABEL_4_X_DEV branch

Version

BABEL_3_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

Which flavor of Linux are you using when you see the bug?

Ubuntu (Default)

Relevant log output

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct.

[staticlibs]

Hi, the last command should work in this form (computing actual index name, see details in wiki):

IF NOT EXISTS (SELECT TOP 1 1 FROM SYS.INDEXES WHERE NAME = sys.babelfish_truncate_identifier(lower('IX_ESTOQUE_EMPRESA_CODIGO' + 'ESTOQUE_EMPRESA') + md5(lower('IX_ESTOQUE_EMPRESA_CODIGO')))) 
BEGIN
  CREATE NONCLUSTERED INDEX [IX_ESTOQUE_EMPRESA_CODIGO] ON ESTOQUE_EMPRESA ([CODIGO])
END
GO

[staticlibs]

Minor correction, to get correct mangled index or constraint name it is necessary to apply babelfish_truncate_identifier to index and table names before concatenation:

select sys.babelfish_truncate_identifier(
    sys.babelfish_truncate_identifier(lower('idx_or_constraint_name')) +
    sys.babelfish_truncate_identifier(lower('table_name')) +
    md5(sys.babelfish_truncate_identifier(lower('idx_or_constraint_name'))))

[PauloHMattos]

This is also affecting my application (we are using BABEL_4_X_DEV).

While the workaround provided by @staticlibs works, it's not ideal for my case, as the migration scripts need to be shared between a Babelfish instance and a SQL Server one.

A fix is planned for this issue or this workaround is the way for now?

[staticlibs]

@PauloHMattos portable DB migration scripts for both Babelfish and MSSQL can work reasonably well with current name mangling, see details in these issues - 1, 2.

[tanscorpio7]

Fixed in https://github.com/babelfish-for-postgresql/babelfish_extensions/pull/3429