> No. The function call mechanism will ensure that timezone goes back
> to its previous state at function exit.
> An actual rollback would undo the effects of set_config, yes. You
> only need this function wrapper to ensure that subsequent operations
> in the same transaction don't see the setting change.
Excellent, thank you. So just to be explicit here, I could either run this
function, or set/run my query/set back, with the same behavior/safety
guarantees as if I was using the generate_series function with timezone
from v16?
On Wed, Oct 4, 2023 at 12:54 PM Tom Lane <t...@sss.pgh.pa.us> wrote:
> Lincoln Swaine-Moore <lswainemo...@gmail.com> writes:
> >>> create function generate_series(timestamptz, timestamptz, interval,
> text)
> >>> returns setof timestamptz
> >>> strict immutable language plpgsql as
> >>> $$
> >>> begin
> >>> perform set_config('timezone', $4, true);
> >>> return query select generate_series($1, $2, $3);
> >>> end
> >>> $$ set timezone = 'UTC';
>
> > This is a nice suggestion, and in fact, it would be fine from my
> > perspective to reset to UTC every time. My concern is only around the
> > safety of the final `set timezone`. Under what circumstances/when can I
> > count on that being set? E.g. if a query using that function was
> cancelled
> > before finishing, would the connection timezone remain as $4?
>
> No. The function call mechanism will ensure that timezone goes back
> to its previous state at function exit. (In the case of an error
> exit, that's actually handled by the transaction abort logic, but the
> result is the same.) Because of that, I think it doesn't really
> matter whether the set_config call says "true" or "false", but saying
> that it's a local setting seems less confusing.
>
> > Does that mean I could run this
> > outside this context of a function, and expect the setting to go back to
> > UTC on a rollback?
>
> An actual rollback would undo the effects of set_config, yes. You
> only need this function wrapper to ensure that subsequent operations
> in the same transaction don't see the setting change.
>
> regards, tom lane
>
--
Lincoln Swaine-Moore
