[SNOW-1791241] Add a session parameter to disable scoped read only temp table

[sfc-gh-yzou]

1. Which Jira issue is this PR addressing? Make sure that there is an accompanying issue to your PR.

SNOW-1791241

2. Fill out the following pre-review checklist:

   • I am adding a new automated test(s) to verify correctness of my new code
     • If this test skips Local Testing mode, I'm requesting review from @snowflakedb/local-testing
   • I am adding new logging messages
   • I am adding a new telemetry message
   • I am adding new credentials
   • I am adding a new dependency
   • If this is a new feature/behavior, I'm adding the Local Testing parity changes.
   • I acknowledge that I have ensured my changes to be thread-safe. Follow the link for more information: Thread-safe Developer Guidelines

3. Please describe how your code solves the related issue.
   scoped read only table doesn't work with dynamic pivot in notebook today, investigation needed.
   We added a flag to turns the scoped temp read only table off for snowpark padnas.

Follow up: https://snowflakecomputing.atlassian.net/browse/SNOW-1793002 enable the flag at server side for native app

[sfc-gh-deliu]

If it's for native app, I think it's easier to look up from self._conn._conn._session_parameters['<param_name>'], similar to what we did for ENABLE_FIX_1375538, WDYT @sfc-gh-sfan?

[sfc-gh-yzou]

oh, i believe that function does look into the _conn._session_parameters, here is the implementation

    def _get_client_side_session_parameter(self, name: str, default_value: Any) -> Any:
        """It doesn't go to Snowflake to retrieve the session parameter.
        Use this only when you know the Snowflake session parameter is sent to the client when a session/connection is created.
        """
        return (
            self._conn._session_parameters.get(name, default_value)
            if self._conn._session_parameters
            else default_value
        )

[sfc-gh-deliu]

Cool! I think you need to define a server side parameter too, similar to PYTHON_SNOWPARK_ENABLE_THREAD_SAFE_SESSION above: https://sourcegraph.c2.us-central1.gcp-dev.app.snowflake.com/github.com/snowflakedb/snowflake/-/blob/GlobalServices/src/main/java/com/snowflake/core/parameters/SnowparkEcosystemParameters.java?L774:42

And default it to false.

[sfc-gh-yzou]

@sfc-gh-deliu i will follow up with you on this, i though you mentioned that i don't need a server side parameter, and you can set this up in the session automatically

[sfc-gh-yzou]

@sfc-gh-deliu i double checked with @sfc-gh-sfan , we do not need a definition for this parameter at server side. but he suggest we have a parameter control for turning this on for native app. I think we can do that when doing the server side change

[sfc-gh-helmeleegy]

I think we should have a changelog entry for this.

[sfc-gh-yzou]

@sfc-gh-helmeleegy added a changelog as part of fix.

[sfc-gh-helmeleegy]

Let's wait for the response of Rich Murnane to our question here - to be able to assess the impact - before we go ahead and merge this.

[sfc-gh-yzou]

@sfc-gh-helmeleegy I don't think we need to wait for the native app response, our impact on notebook is much bigger than the native app at this moment, the native app user can stay with 1.24.0 if they would like, or turn on the flag if the user would like to.

[sfc-gh-helmeleegy]

@sfc-gh-helmeleegy I don't think we need to wait for the native app response, our impact on notebook is much bigger than the native app at this moment, the native app user can stay with 1.24.0 if they would like, or turn on the flag if the user would like to.

SGTM. I will approve. The native app response when it comes (hopefully soon) can help us decide if there are users we need to communicate with to turn off the flag.

[sfc-gh-helmeleegy]

LGTM.

[sfc-gh-aalam]

I would suggest we describe the kind of error this would bubble earlier. For example

Suggested change

	- Disable creation of scoped read only table to mitigate dynamic pivot unfound table issue in notebook env.
	- Disable creation of scoped read only table to mitigate `TableNotFoundError` when using dynamic pivot in notebook environment.

[sfc-gh-yzou]

udpated