Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qo59g-00064N-NM for pgsql-general@arkaria.postgresql.org; Wed, 04 Oct 2023 16:55:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qo58f-001Loh-RD for pgsql-general@arkaria.postgresql.org; Wed, 04 Oct 2023 16:54:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qo58f-001Lnw-Fl for pgsql-general@lists.postgresql.org; Wed, 04 Oct 2023 16:54:22 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qo58e-0000la-0T for pgsql-general@lists.postgresql.org; Wed, 04 Oct 2023 16:54:21 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 394GsITO2602232; Wed, 4 Oct 2023 12:54:18 -0400 From: Tom Lane To: Lincoln Swaine-Moore cc: Marian Wendt , pgsql-general@lists.postgresql.org Subject: Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones In-reply-to: References: <2582288.1696428710@sss.pgh.pa.us> <13eb1e62-9c00-4878-bb82-ef003eca884c@yahoo.com> Comments: In-reply-to Lincoln Swaine-Moore message dated "Wed, 04 Oct 2023 11:58:05 -0400" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2602230.1696438458.1@sss.pgh.pa.us> Date: Wed, 04 Oct 2023 12:54:18 -0400 Message-ID: <2602231.1696438458@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Lincoln Swaine-Moore 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