> On 11 Jan 2024, at 17:43, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 1/11/24 07:06, Alban Hertroijs wrote: >> Hi all, > >> In the above, I worked around the issue using a couple of user-defined >> functions in PG. That should give a reasonable idea of the desired >> functionality, but it's not an ideal solution to my problem: >> 1). The first function has as a drawback that it changes the time zone for >> the entire transaction (not sufficiently isolated to my tastes), while >> 2). The second function has the benefit that it doesn't leak the time zone >> change, but has as drawback that the time zone is now hardcoded into the >> function definition, while > > I don't think the set_config and SET are acting the way you think they are: > > set_config(https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SET) > > " > set_config ( setting_name text, new_value text, is_local boolean ) → text > > Sets the parameter setting_name to new_value, and returns that value. If > is_local is true, the new value will only apply during the current > transaction. If you want the new value to apply for the rest of the current > session, use false instead. This function corresponds to the SQL command SET. > > set_config('log_statement_stats', 'off', false) → off" > "
I tried this like so: select ToDatetimeOffset(current_timestamp, 'Europe/Amsterdam'), to_char(current_timestamp, ‘YYYY-MM-DD HH24:MI:SS.SU0 TZH:TZM’). The result of the second call was based on time zone ‘Europe/Amsterdam’, where it wasn’t when called outside the transaction (when it was based on UTC corresponding to the server time zone). So the time zone set with set_config(…, …, true) appeared to leak out of function scope and applied to transaction scope (as described in the quoted text). For brevity I could run that query tomorrow when I’m back at work. > SET(https://www.postgresql.org/docs/current/sql-set.html) > > "If SET (or equivalently SET SESSION) is issued within a transaction that is > later aborted, the effects of the SET command disappear when the transaction > is rolled back. Once the surrounding transaction is committed, the effects > will persist until the end of the session, unless overridden by another SET. > > The effects of SET LOCAL last only till the end of the current transaction, > whether committed or not. A special case is SET followed by SET LOCAL within > a single transaction: the SET LOCAL value will be seen until the end of the > transaction, but afterwards (if the transaction is committed) the SET value > will take effect. It says transaction again here. > The effects of SET or SET LOCAL are also canceled by rolling back to a > savepoint that is earlier than the command. > > If SET LOCAL is used within a function that has a SET option for the same > variable (see CREATE FUNCTION), the effects of the SET LOCAL command > disappear at function exit; that is, the value in effect when the function > was called is restored anyway. This allows SET LOCAL to be used for dynamic > or repeated changes of a parameter within a function, while still having the > convenience of using the SET option to save and restore the caller's value. > However, a regular SET command overrides any surrounding function's SET > option; its effects will persist unless rolled back. > " I didn’t succeed in calling SET LOCAL TIMEZONE from within the function. Could be I missed something, then Google (stackoverflow) pointed me to set_config(). I did manage to apply it to the second function header, which I think behaves such that the time zone change stays within function scope. Right now I’m not 100% sure that I verified that. More to check tomorrow. Frankly, I do hope that you’re right here, that would make my work easier. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
