How to nest stored procedures that use one stream in Snowflake?

Question

I have the following architecture:

• Main stored procedure main_sproc
• Nested stored procedure nested_sproc

The task we have is processing data from a stream in Snowflake but have to do it from a nested approach.

Thus this would be the code:

create or replace procedure nested_sproc()
returns varchar
language sql
as
begin
  begin transaction;

  insert into table1
  select * from stream; -- it will be more complex than this and multiple statements

  commit;
end;


create or replace procedure main_sproc()
returns varchar
language sql
as
begin
  begin transaction;

  insert into table1
  select * from stream; -- it will be more complex than this and multiple statements

  call nested_sproc();

  commit;
end;

When I try to run call main_sproc() I notice that the first statements goes through but when it reaches call nested_sproc() the transaction is blocked. I guess it's because I have in both procedures a begin transaction and commit but without them I get an error stating that I need to define the scope of the transaction. I need to deploy this final procedure on a task that runs on a schedule but without having to merge the queries from both procedures and the ability to still process the current data inside the stream.

Is there a way of achieving this?

Answer 1

As per the comments by @NickW, you can't have two processes reading the same stream simultaneously as the running a DML command is altering that stream - so your outer SP is selecting from the stream and will lock it until the outer SP transaction completes. You'll need to find another approach to achieve your end result e.g. in the outer SP write the stream to a table and use that table in the inner SP. If you use a temporary table it should be available when either SP is running (as they will be part of the same session) and then will get automatically dropped when the session ends - as long as you don't need this data outside of the 2 SPs this might be more convenient as you have less maintenance.