The SQL Server event queue store in the arrow-odbc adapter wraps CREATE INDEX in an existence check that never matches. The index DDL runs again every time the store's DDL is applied, and fails if the index already exists.
Where
sqlspec/adapters/arrow_odbc/events/store.py: re.search(r"CREATE INDEX\s+(\S+)\s+ON\s+(\S+)", ...)
What happens
For CREATE INDEX idx_app_events_channel_status ON app_events(channel, status, available_at), the second \S+ captures app_events(channel, as the table name. The rendered guard is:
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'idx_app_events_channel_status'
AND object_id = OBJECT_ID(N'[dbo].[app_events(channel,]')) BEGIN CREATE INDEX ... END
OBJECT_ID(N'[dbo].[app_events(channel,]') is always NULL, so NOT EXISTS is always true.
Expected
OBJECT_ID(N'[dbo].[app_events]'). Stop the table-name capture at ( or whitespace, for example ON\s+([^\s(]+).
Note
tests/unit/adapters/test_arrow_odbc/test_tsql_stores.py currently asserts the rendered SQL as it is today, including OBJECT_ID(N'[dbo].[app_events(channel,]'). Update that assertion along with the fix.
The SQL Server event queue store in the arrow-odbc adapter wraps
CREATE INDEXin an existence check that never matches. The index DDL runs again every time the store's DDL is applied, and fails if the index already exists.Where
sqlspec/adapters/arrow_odbc/events/store.py:re.search(r"CREATE INDEX\s+(\S+)\s+ON\s+(\S+)", ...)What happens
For
CREATE INDEX idx_app_events_channel_status ON app_events(channel, status, available_at), the second\S+capturesapp_events(channel,as the table name. The rendered guard is:OBJECT_ID(N'[dbo].[app_events(channel,]')is alwaysNULL, soNOT EXISTSis always true.Expected
OBJECT_ID(N'[dbo].[app_events]'). Stop the table-name capture at(or whitespace, for exampleON\s+([^\s(]+).Note
tests/unit/adapters/test_arrow_odbc/test_tsql_stores.pycurrently asserts the rendered SQL as it is today, includingOBJECT_ID(N'[dbo].[app_events(channel,]'). Update that assertion along with the fix.