WithMultiStatement - creates an extra process for all queries that doesn't resolve until all queries are resolved. Takes up extra space and blocks other requests.

[nazlimine]

Issue description

I am using gosnowflake v1.6.17
Running it dockerized with alpine:3.15.0

I have a file that has multiple sql statements

Ex:
Select * from x_table LIMIT 5;
Select * from y_table;
Select * from z_table LIMIT 5;

And I am using this code snippet to execute them

ctx := context.Background()
New context, _ := gosnowflake.WithMultiStatement(ctx, 0)
_, err := db.ExecContext(msContext, queryStr)

In the snowflake activity tab, I was expecting to see 3 queries running but I am seeing 4 for this example, one for each query and one for all of the queries.

Processes:

1- Select * from x_table LIMIT 5; - COMPLETED
2- Select * from y_table; - RUNNING
3-Select * from z_table LIMIT 5; -COMPLETED
4-Select * from x_table LIMIT 5; -RUNNING
Select * from y_table;
Select * from z_table LIMIT 5;

The issue for me is the following:
Since the second query is not finished, the 4th query also seems to be not finished. And since I have multiple files like this, the 4th query that seems to be running takes up unnecessary space and blocks my other snowflake requests.

Is there way to not run the 4th query?

[sfc-gh-dszmolka]

hi and thank you for raising this Issue. What you're seeing, is the normal and expected behaviour - of Snowflake, not related to the gosnowflake driver. It's how the engine itself works; with all the other drivers. For a multi-statement query; you'll see:

• one multi-statement queryID ('main' query)
• one individual queryID for each of the individual queries launched within a multi-statement query

Indeed the 'main' multi-statement query only resolves once all of the child queries resolved OR if any of the child queries errored out. (Reference: https://docs.snowflake.com/en/developer-guide/sql-api/submitting-multiple-statements#handling-errors-when-specifying-multiple-statements-in-a-request)

Not entirely sure about the 'taking up extra space' part, but if you want to submit queries which don't need to wait for each other, you can submit them as an asynchronous query (and then you'll need to make sure to poll for their status). Reference: https://pkg.go.dev/github.com/snowflakedb/gosnowflake#hdr-Asynchronous_Queries

edit: also there's an example in async.go

Hope this helps but if you have any other questions, feel free to post. Also if you're all set, mark this issue as closed please.

[nazlimine]

Thank you for the quick response!

Actually we are currently using snowsql but we want to switch to gosnowflake. In snowsql because we give it the file directly, it runs the contents one by one and doesn’t open an extra query as I mentioned above. But we encountered this situation when we wanted to transfer the same logic to gosnowflake and wondered if we could achieve the same behavior.

We will also examine the async solutions you mentioned now. I'm closing the issue for now, but maybe it can be supported with parameters in the future for this use case.