How to handle context-sensitive operations?

[jacques-n]

There are several operations in systems that use external context to inform things. A couple of simple examples:

• A user inserts a timestamp value into a timestamptz column without expressing a timezone. A system typically will interpret the timestamptz based on the users session timezone.
• A user executes the now() operation multiple times in a single plan. In this case, it is expected that the now time will be the same for all executions. This is typically done by determining a now time upfront and placing that in each operation.
• A user writes a view which depends on the on USER_NAME nilladic function. The system should adjust the operation based on which user executes the view.

It seems like we have a couple of options on how to handle this:

1. Require that all context is resolved with values before it can
2. Have a well-defined set of context properties that can be implicitly used in a plan.

While I like the simplicity of the first option, I think we probably need to do the second approach. A couple of reasons:

• Systems like Iceberg would like to save a plan without context. Saying you can't have a plan with now() in it seems awkward.
• Function argument signatures are not filled with internal concerns allowing simpler systems to just expose function signatures directly to end users.

So I propose that we create a structured specification for context and then make that available as internal arguments for functions. As part of this, I propose that the function signature that need these must declare which they need.

Thoughts?

[rdblue]

I'd prefer option 2. It seems like we can strictly define things like now() behavior or how to fetch the current user. I think those are important for cross-engine views.

That said, the spec should avoid external context where possible. The first example of a timestamp without time zone that is implicitly converted to timestamp with time zone seems like something we don't want to allow. I don't think the plan is really resolved at that point. The producer could replace it with to_timestamptz(timestamp '...', session_timezone()), convert the value using the producer's time zone, or throw an exception. The important thing is that implicit casting should not be present in plans.

[jacques-n]

The literal insert may not be the best example. I think a better one is TO_CHAR(timestamptz). Sure we can define TO_CHAR(timestamptz, timezone) function but that is similar to having to define now(timestamptz) instead of now(). The good news is I think there are few actual needs here. Calcite and Dremio both have a context concept with only a handful of things.

[westonpace]

How about

3. Define an external variable function external_variable(name, expected_type) so then session_timezone() becomes external_variable('session_timezone', string).

3 could potentially be done in combination with 2 where 2 covers the most common scenarios and 3 covers everything else. Or maybe 3 is just my phrasing of what you've called 2 (2 with custom context).

[rdblue]

Wouldn't this just push the problem to the set of environment variables? Then we would need to define environment variables that are needed for portability. I think I'd rather just define session_timezone().

[westonpace]

My thinking was that there would be no need for a defined set of environment variables. The function external_variable(name, expected_type) would be an opaque function to any query planner or transformer. Although, now that I think this through, it doesn't offer any real benefit over just leaving session_timezone() undefined and treat it as a nullary UDF.

In that case, I propose...

4. If it's important enough and special enough to have certain semantics (e.g. now()) then it is just a well defined nilary function. If it isn't important enough or special enough (e.g. let's say we decide session_timezone() isn't important enough) then it is just a udf nilary function.

...which I think is the same thing you were saying in different words.

[jacques-n]

I believe there is an important difference between 2 and 4 (if I understand your comments correctly). With 2, context can be carried across multiple steps. This guarantees that all systems in a chain use the same value for each context-dependent item. For example:

producer > planner1 > planner2 > engine. In this situation, planner1 may decide to resolve one invocation of now but not another (for many valid reasons). Then planner2 or engine must resolve the second now with the same value as the planner to get correct results. This means that you must externally define the property that will be used to resolve this information.

[westonpace]

I think there are two flavors we could take. Let's pretend we define now(time zone) (which you dismissed in a separate issue but I kind of like). I think most of the time the producer will be the one to define the value and so will send now('America/NewYork').

However, there are cases (supposedly) where the server's time zone is to be preferred. In that case you need a concept of context. So I'd prefer something like...

now(ref('server.time.zone'))

Any middleman can fill in that ref if appropriate. If the query gets to the engine, and that ref has not been resolved by a middleman, and the engine does not know how to resolve it, then it is an undefined reference error and the query fails.

With context (and now()), suddenly everything is not contained in the query. You have to send (for the usual case where producer determines the time zone) now() WITH CONTEXT { 'server.time.zone': 'America/NewYork' } as your query.

I will be content with either flavor but I feel like the former is nice because:

• Everything lives in the query.
• It's obvious (without having to consult function documentation) which references need to be filled in by a middleman.

[westonpace]

I just realized how silly now(time zone) is. 😂. The context for now() is not time zone (now does not need a time zone) but the query time which, admittedly, is a little trickier to embed in the query itself. I suppose, with that in mind, I am a little more favorable towards external context. Although the disadvantages above still hold. External context hides complexity I think.

[westonpace]

Is all context supplied by the producer? And, if not, what happens when a query is distributed across multiple systems? For example, if a producer does not supply a "session time zone" (in this case postgres would fall back to a server configuration variable) and then the query is distributed across two query engines, is it possible the context ends up with two different values?

[jacques-n]

Yes, I would expect context is created by a producer. In Dremio for example, context is generated at query subission and always includes current time, username and list of the user's associated groups. Functions can use this or not. It is propagated to all nodes as part of plan distribution for that query.