[Bug] exec test_truncate blocking system

[faridkhasianov16]

What happened?

exec test_truncate
blocking system CREATE PROCEDURE test_truncate AS BEGIN RAISERROR('Before TRUNCATE', 0, 1) WITH NOWAIT;

truncate table #state_list;  

RAISERROR('After TRUNCATE', 0, 1) WITH NOWAIT;

END

RAISERROR not print truncate table #state_list in batch worked normal

Version

BABEL_5_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

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

Ubuntu (Default)

Relevant log output

Code of Conduct

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

[robverschoor]

I reproduced the issue. it is being looked into.

[faridkhasianov16]

create procedure test_truncate AS BEGIN

RAISERROR('Before TRUNCATE', 0, 1) С NOWAIT;

delete from #state_list;

RAISERROR('After TRUNCATE', 0, 1) WITH NOWAIT; end worked normal

[tanscorpio7]

@faridkhasianov16 Thanks for reporting the issue.

This was likely broken when PostgreSQL community made some changes related to sys cache when they introduced SearchSysCacheLockedCopy1 functions. Babelfish did handle this problem here for babelfish temp tables but looks like it was not a complete fix.

As a workaround you can try creating the temp tables inside the same procedure that executes the truncate.