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 1u35lw-00AO0B-NT for pgsql-general@arkaria.postgresql.org; Fri, 11 Apr 2025 04:13:44 +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 1u35lu-004CjA-S5 for pgsql-general@arkaria.postgresql.org; Fri, 11 Apr 2025 04:13:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u35lu-004Cj0-H7 for pgsql-general@lists.postgresql.org; Fri, 11 Apr 2025 04:13:42 +0000 Received: from mail-ot1-x32e.google.com ([2607:f8b0:4864:20::32e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u35ls-004dpF-1e for pgsql-general@lists.postgresql.org; Fri, 11 Apr 2025 04:13:42 +0000 Received: by mail-ot1-x32e.google.com with SMTP id 46e09a7af769-72c14138668so428433a34.2 for ; Thu, 10 Apr 2025 21:13:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1744344818; x=1744949618; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=FoNCkS+TQ8g2iYyBcFrLlYo2t9xBVG2ILIlr2lanKh0=; b=muHVRtxhoRYfjQOOzvANuiICfmpdltjOpu+YQELiylZG9mZbGYhesauvNSvfjC2wx7 0cFKwJGu/c4CkQPHdX5JOU/rFMqXXU3GcUPRWaPdB9stBcb2U3QZtp7PSvCLt1pFYnRv Nk1XNOCZsnV9rT3tqjka/LSGfLu9CD+oj/3O6az9fMc1x5Wth4VbX9A5YL0Ykm6QbpxL dpp8xphbSJYbPiD28XrvNfIo7aNF3KAcLq2MClv3HZItEeJajOWiUu8RnW8SxifYWaxv G9JA7ggkS5dh/IFGrrseZya0h/Mim0+DsMoHEGiezhEK7sCmpM7+7z1G5W3t/wsEb/4I oGQA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1744344818; x=1744949618; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=FoNCkS+TQ8g2iYyBcFrLlYo2t9xBVG2ILIlr2lanKh0=; b=V2QI0jxStJ4pcgRb3r83NKTa13qHNrP3TuKodzaIOAuHPpSE2yQR3AJ3OswgINVVZi s+39QJRqIwF1Io3VO3S/eF3UE9cXLBWkBm2dD4aGu0dKvxgHR9Ry5f/PKczEGIu+68Dk U0hKdNtGwc1Kx+h6bwl24gXp+eG91QmPgjRz6k5f4sEH192pmm+NfAKm8euEUIn3U20Y +TBlGA6iliGcNlDKe24NirssxizKNvJ7txsNzGfbVW8RrdLZn3HNUKbim9mAw7UM1dyL Lqp6mWJoCdH8eZUhoapit6vrBNUakUuahwr4wlWpdEeRSQk/tshnTIVy3ZvUkp+Fq2RF Hunw== X-Forwarded-Encrypted: i=1; AJvYcCUBpyy+laUBbZCp95cN1sf1P5YJDRZVRiDDuMxEUtmlAA29pu9Vk6u/qTIurtESAhHBZAz6UPcw3g8Efuv3@lists.postgresql.org X-Gm-Message-State: AOJu0Yw+NgR67+yZdwsYHcOkXf7A7ZsL/fDLk02yYjmgozha7oe/QLxq Lr9UDPbzivjvRkW2sPwYGniKGu1FCpIjwArjLICsSl7t8ewEUcLM8hyUoMhXt4+zDJZK6qnDwZN tTrdSSdUcG95HayHTjRJxgtYDTy4= X-Gm-Gg: ASbGncvjMgEtl0cCw43J0YlBpKSD5l/OMq62U3pjn3WyQjiCjPTdrpFaM5xkFZJEPg4 8hG8330AKmKzYWUInbwBJ8WvIXlAeexjoFZ7JadXn51Q2/R9h461+MdNgC9DAW3Au369PMzEU1z k4oTkSpqx5VufoPNGMrx4z7VjrsRjh5Kc= X-Google-Smtp-Source: AGHT+IGfe90Yh/twSIPIRdq1+JuPCbptsAc/7ThkR0KliBB0LsB6V8Aw7dKb0gRMR+bR9nV4YQWagNXUFS133oXah18= X-Received: by 2002:a05:6830:34a6:b0:72b:854f:9ebf with SMTP id 46e09a7af769-72e86308a7fmr865509a34.11.1744344818490; Thu, 10 Apr 2025 21:13:38 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:ac9:5f03:0:b0:589:13f9:e937 with HTTP; Thu, 10 Apr 2025 21:13:37 -0700 (PDT) In-Reply-To: <752812.1744343513@sss.pgh.pa.us> References: <662101e2-5ca5-4bf5-9307-2519efde8690@ya.ru> <8687df7d49bc4b28b41c7cf4e9ad1e15d383b2fa.camel@cybertec.at> <752812.1744343513@sss.pgh.pa.us> From: "David G. Johnston" Date: Thu, 10 Apr 2025 21:13:37 -0700 X-Gm-Features: ATxdqUEaHeS9ItklkqnDZGvO15RT0S9-aykLN9qB0Ml71cH1qJwnBc7SGMeESBs Message-ID: Subject: Re: Interesting case of IMMUTABLE significantly hurting performance To: Tom Lane Cc: Merlin Moncure , Nico Williams , Adrian Klaver , Laurenz Albe , Olleg Samoylov , "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000045ab33063278ed7c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000045ab33063278ed7c Content-Type: text/plain; charset="UTF-8" On Thursday, April 10, 2025, Tom Lane wrote: > Merlin Moncure writes: > > I guess the real problems here are lack of feedback on a number of > fronts: > > *) the server knows the function is not immutable but lets you create it > > anyway, even though it can have negative downstream consequences > > That's debatable I think. If you know what you're doing, you're going > to be annoyed by warnings telling you that you don't. > So long as you use atomic SQL functions I suspect it is possible to use the dependency data to get the volatility of the used functions and compare them to the volatility of the UDF. David J. --00000000000045ab33063278ed7c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thursday, April 10, 2025, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Merl= in Moncure <mmoncure@gmail.com= > writes:
> I guess the real problems here are lack of feedback on a number of fro= nts:
> *) the server knows the function is not immutable but lets you create = it
> anyway, even though it can have negative downstream consequences

That's debatable I think.=C2=A0 If you know what you're doing, you&= #39;re going
to be annoyed by warnings telling you that you don't.

So long as you use atomic SQL functions I = suspect it is possible to use the dependency data to get the volatility of = the used functions and compare them to the volatility of the UDF.

David J.

--00000000000045ab33063278ed7c--