Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nhHJt-0007Xs-8q for pgsql-docs@arkaria.postgresql.org; Wed, 20 Apr 2022 20:53:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nhHJs-00064s-2V for pgsql-docs@arkaria.postgresql.org; Wed, 20 Apr 2022 20:53:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nhHJr-00061g-N4 for pgsql-docs@lists.postgresql.org; Wed, 20 Apr 2022 20:52:59 +0000 Received: from mail-ed1-x52d.google.com ([2a00:1450:4864:20::52d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nhHJp-0003ks-5t for pgsql-docs@lists.postgresql.org; Wed, 20 Apr 2022 20:52:58 +0000 Received: by mail-ed1-x52d.google.com with SMTP id u18so3903365eda.3 for ; Wed, 20 Apr 2022 13:52:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to; bh=XiaCgDiRIIrvIGpT6jnJs8EJSYl2bFdl1L7CxJXt7ig=; b=YGXu4IbP7vEcGOgsYAuBI05N/bYrBg7yH+ha0pP2qH/ENEAGhdJbdJqyWZlwXgj3A6 e4hlTvcHPfDFUvqkZF1laatFQOxG64x41JtmmL00vRdgh3YGOwwTqRKGqogt2FpiNe5l RvRsDRozeJkYH2jd/fsxN47qTKwYaH3ieErtsKffyqZ9voNZ+ss06DGD6hYwoU+DA/7T J1YU28H27vt9VcPTiVcWI3B5g/TuPKQOjjW7wj/e/RdPGe4N0XB5n5M/p0+QvEJG5bXm zNbGnmNGNoApuZ9srLBFbAc3wpnaH3im8gvQsQ4KaOcNykW5IGnNulnIRW9d0LWxJr6a RBjg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to; bh=XiaCgDiRIIrvIGpT6jnJs8EJSYl2bFdl1L7CxJXt7ig=; b=w7aLdHlSPCY8qU4+MDE+24jdMbJaS2ZhpTu/O0Fl4nH+4C6p/sYUjKGTK/ddhRak/p xAKyNiKjesCbbSTojKODT2TY3klc3z+JHykvVIt0RcbYL15kkPlJ3lBArRgt+ZsIrKsb syRCah8B4JCwIgxpioQRg5BaCGZR4S7SDPYlbJgYZZU6yKjaucvPa9474UO1p6u4uiVU M56ftyZwzCqYGNcbzv2+AAyKavoEE1cTuxB0JGE28f0E6lm/8R/WAraiQuhVMecQ/jt0 Z7pWcU0sjYQ2m+A+n1tRrCsXQfY50FfT2tz5qr29ERbXiJZDFx3q/3Ms+gqNFhvhb7jg 7Rbg== X-Gm-Message-State: AOAM533pDKDMXOFmG/JF972ubY5RCppfmcSZcoUwKF/xqIq4TdzxchvA F4eAMnQVAXE0o5USgGtBXuL0KZHZMHpCcbTStLMwu3/uUiA= X-Google-Smtp-Source: ABdhPJy/F4kCq3kHGsCKuQJvElOrgsMl6mzNG8tDmV9pdTimPt33qM8/awpyji2X8ullCa7a6AodUEAaVPJYk2OuBkE= X-Received: by 2002:a05:6402:1d85:b0:41d:7c28:e118 with SMTP id dk5-20020a0564021d8500b0041d7c28e118mr25236652edb.134.1650487975472; Wed, 20 Apr 2022 13:52:55 -0700 (PDT) MIME-Version: 1.0 References: <165047776965.683.11067605937815363802@wrigleys.postgresql.org> In-Reply-To: <165047776965.683.11067605937815363802@wrigleys.postgresql.org> From: "David G. Johnston" Date: Wed, 20 Apr 2022 13:52:38 -0700 Message-ID: Subject: Re: 'CREATE OR REPLACE FUNCTION' behavior whenever a transaction is running To: joao@e3c.com.br, Pg Docs Content-Type: multipart/alternative; boundary="0000000000007bf10205dd1c2d9b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007bf10205dd1c2d9b Content-Type: text/plain; charset="UTF-8" On Wed, Apr 20, 2022 at 12:50 PM PG Doc comments form < noreply@postgresql.org> wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/14/sql-createfunction.html > Description: > > The page does not mention what is the default behavior whenever a function > is replaced while the same function is being used in another > query/transaction. > > Does the query fail? Does the 'CREATE OR REPLACE' operation fail? Does the > query finish before the function is replaced? > > As a side note, my interest in this came from our need to use 'C-Language > Functions', aka binary functions, so it would be neat to also add what is > the behavior on those as well, if there's any difference. > IIUC pg_proc is administered using MVCC behavior just like any other table. What you experience will be subject to your isolation mode but in no case will a single command's execution see different versions nor will such execution prevent the "replace"ment of the function with a newer version. Absent guidance to the contrary I wouldn't expect C language functions to behave any differently than any others. But that just pertains to the "REPLACE" aspect. You need to read the notes about C language functions to get the rest of the story (and you likely wouldn't need to "replace" a C-langauge function entry because of this different loading/compiling mechanism). Namely: https://www.postgresql.org/docs/current/xfunc-c.html#XFUNC-C-DYNLOAD In short, C-language functions, referenced simply by name and module, and pre-compiled, do not have a mechanism to invalidate the cache like pl/pgsql functions do. Other languages may involve yet other dynamics. David J. --0000000000007bf10205dd1c2d9b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Apr 20, 2022 at 12:50 PM PG Doc comments form <= noreply@postgresql.org> wr= ote:
The following documentation comment has been l= ogged on the website:

Page: https://www.postgresql.org/docs/14/s= ql-createfunction.html
Description:

The page does not mention what is the default behavior whenever a function<= br> is replaced while the same function is being used in another
query/transaction.

Does the query fail? Does the 'CREATE OR REPLACE' operation fail? D= oes the
query finish before the function is replaced?

As a side note, my interest in this came from our need to use 'C-Langua= ge
Functions', aka binary functions, so it would be neat to also add what = is
the behavior on those as well, if there's any difference.

IIUC pg_proc is administered using MVCC behavior just = like any other table.=C2=A0 What you experience will be subject to your iso= lation mode but in no case will a single command's execution see differ= ent versions nor will such execution prevent the "replace"ment=C2= =A0of the function with a newer version.

Absent guidan= ce to the contrary I wouldn't expect C language functions to behave any= differently than any others.=C2=A0 But that just pertains to the "REP= LACE" aspect.=C2=A0 You need to read the notes about C language functi= ons to get the rest of the story (and you likely wouldn't need to "= ;replace" a C-langauge function entry because of this different=C2=A0l= oading/compiling mechanism). Namely:

https://www= .postgresql.org/docs/current/xfunc-c.html#XFUNC-C-DYNLOAD
<= br>
In short, C-language functions, referenced simply by name and m= odule,=C2=A0and pre-compiled, do not have a mechanism to invalidate the cac= he like pl/pgsql functions do.=C2=A0 Other languages may involve yet other = dynamics.

David J.


<= br>
--0000000000007bf10205dd1c2d9b--