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 1qdLOB-0031E5-D6 for pgsql-general@arkaria.postgresql.org; Tue, 05 Sep 2023 02:01:59 +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 1qdLO9-00Fox6-Pl for pgsql-general@arkaria.postgresql.org; Tue, 05 Sep 2023 02:01:57 +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 1qdLO9-00Fowe-5S for pgsql-general@lists.postgresql.org; Tue, 05 Sep 2023 02:01:56 +0000 Received: from mail-ed1-x535.google.com ([2a00:1450:4864:20::535]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qdLO5-002mdI-BM for pgsql-general@lists.postgresql.org; Tue, 05 Sep 2023 02:01:55 +0000 Received: by mail-ed1-x535.google.com with SMTP id 4fb4d7f45d1cf-52713d2c606so2649626a12.2 for ; Mon, 04 Sep 2023 19:01:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20230601.gappssmtp.com; s=20230601; t=1693879312; x=1694484112; darn=lists.postgresql.org; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=5rrN6IlJfiwsZBlurELSLXwBvl8PANlCU/FsaTsiQyc=; b=0ErjWMJI/oux//3C4r51prtHpU6LqnyhIwJJ5hNf2gtC0bf54belQpLd398c5FJUG8 tUmvh7Rtkr09FWppkZA6cG0UVbJHXfgxpW2+Igj+0/Rl3xu6claFT2lg/TR/EWILKblJ YEyeFDBKQIc6Q+clQw4TvPrjd5izYyc8FDDOHUYuDKilFb/9fbnH91REwOEM7Z7KF89X gXu3pqSA1ToAOPgckpFlknubjPjE8xlY2MW8ttFGdCVy8q/mic6a2yj2De4/20bDDuzW 9HzB3EaBctvPnfYLwqKBLH2JB4mcZJdqACj/3xONOY/Us/7tdqcUBALbg4uku8Dh7reB KJuQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1693879312; x=1694484112; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=5rrN6IlJfiwsZBlurELSLXwBvl8PANlCU/FsaTsiQyc=; b=f8cVtCF4iBGA/N82fXcNdQJX97P/082/oR2HTGbi9WbCqFu1ziWHto0dFvtCjTC4AB 7TkhOrzV9yR4d/QeEILBTH/PUm/5sBJ/5TaPhEYR1FV+k8C500Biz/K1exeAUuCEDuWK zqyvNCB3Xh99z8JB5a0GETqAXUWwKgujOaZlRbdUYLrwgeU/YgqIfjEgsbJQjzvXE1Nt 4HNjB+pB4KVNX5Ny1tP7VaEQWcBmrLFPm5I0n7vS1Awbol6JlnQhVFEilijwZujb5Agf jpVzIZA+Uyhj/NejbfUWDa8I/Hm+Go1POz6kFhEMtfswTZupNv3bm5eUhlQJckcJ33VW NFbw== X-Gm-Message-State: AOJu0YxWQMR62bVIbguzpwDXSRsor/IyehZ27GwyabOOuLwGxLT/UOkN VNknLoKPuPD/xLhDHapL+fJuHg== X-Google-Smtp-Source: AGHT+IHuvx0p5f9PXFQWc61ZrMs35CABO3+QwO0Bp/RyfUSK687UqfsEMawoCsCAFt12I9ryfFLd9w== X-Received: by 2002:a17:907:7808:b0:9a5:d095:a8e1 with SMTP id la8-20020a170907780800b009a5d095a8e1mr7772964ejc.11.1693879311840; Mon, 04 Sep 2023 19:01:51 -0700 (PDT) Received: from localhost.localdomain (178-190-171-95.adsl.highway.telekom.at. [178.190.171.95]) by smtp.gmail.com with ESMTPSA id a3-20020a17090680c300b009a19fa8d2e9sm6809056ejx.206.2023.09.04.19.01.51 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 04 Sep 2023 19:01:51 -0700 (PDT) Message-ID: Subject: Re: Array vs Temporary table vs Normal Table + truncate at end From: Laurenz Albe To: Lorusso Domenico , pgsql-general@lists.postgresql.org Date: Tue, 05 Sep 2023 04:01:50 +0200 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Mon, 2023-09-04 at 18:42 +0200, Lorusso Domenico wrote: > I prepared a function that build a set of records of type (myComplexType)= . >=20 > That works, but I've to use this record set in different situations. >=20 > One of them require to scan multiple times the function=C2=A0results: > =C2=A0=C2=A0=C2=A01. comparing with another table (myProducteAttributeTab= le) to determine if I need to delete old record > =C2=A0=C2=A0=C2=A02. check if the recordset contains attribute never regi= stered before > =C2=A0=C2=A0=C2=A03. in this case insert in appropriate table (myAttribut= e), retrieve attribute uid and type > =C2=A0=C2=A0=C2=A04. finally update first table (myProducteAttributeTable= ) with new information coming from the recordset (with a merge) >=20 > So, which is the best approach? >=20 > =C2=A0* Store result in an array and use in each subsequent query the unn= est (with a complex record type?) > =C2=A0* Create temporary table on commit delete, with the same structure?= But in this case I can't declare a > cursor for the table, and I can't use update where current of (for poi= nt 3) > =C2=A0* Create normal table and truncate as part of execution, but what h= appens in case of parallel execution? I'd say it depends. If the set is rather smallish, arrays may be the best way. If the set is large (or the rows themselves are large), and the function is= not called all the time, I'd probably go with a temporary table. If the set is large and the function called all the time, I'd try to use a = persistent table to avoid catalog table bloat. If several function invocations need to store t= heir record set in the same table, you could make the backend process ID part of the primary k= ey. Yours, Laurenz Albe