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 1tp1Pn-00H9my-NP for pgsql-general@arkaria.postgresql.org; Mon, 03 Mar 2025 08:44:43 +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 1towlq-00DYrx-MF for pgsql-general@arkaria.postgresql.org; Mon, 03 Mar 2025 03:47:10 +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 1towlq-00DYrp-9r for pgsql-general@lists.postgresql.org; Mon, 03 Mar 2025 03:47:10 +0000 Received: from mail-pj1-x102e.google.com ([2607:f8b0:4864:20::102e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1towlm-000dBK-29 for pgsql-general@lists.postgresql.org; Mon, 03 Mar 2025 03:47:09 +0000 Received: by mail-pj1-x102e.google.com with SMTP id 98e67ed59e1d1-2f9d3d0f55dso6256913a91.1 for ; Sun, 02 Mar 2025 19:47:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1740973505; x=1741578305; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=fZljlaYk5f9VSxaSiyOyrvZa9BeH6UZbPqloCLS+lgo=; b=RQeerMG6q3UXovInMzhDwHcQD6SMA1NrI6c8ou2+HGCqebtav1QdLSxCxPj6t6BUEg GdLQL/xGeBL5MAA7hrsVMjp0GQDprM+GaShDSxrumK8+xMGpSvlQFWmjRudP3hWO+vZV gM1mR4kUxW2xaxNcef/h3QknWKTtLsbOdsYQymUzi1c3CJC9/w1Ss7pne3bPlZan2Srt QphR06ROnGWucg9FyFRHOQ3IyhDuU34nYDypA4MprgEn2kg9bA3sCfHAqBnn/5deRtY0 zyHzJFkig298cZFMiahAGkwpAl3uTwi8cs/9XY/i7KjQIhszy89BenvCPg1nPT3mVa17 RPAw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1740973505; x=1741578305; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=fZljlaYk5f9VSxaSiyOyrvZa9BeH6UZbPqloCLS+lgo=; b=ugwFU3/Maq1qcgEHJ6ul6CJRM50od33z9iThpNzIvMA2Z2lLnUrdFXQRIsqDSURges rk2qBDKxSs/t7p7tLJTpbJeIAlJ1kRzMSOKAHkqH3vCGm3dgLCCc7AFmEjQEDsPkPdFN Nx2NHDAEWowpNeP5AUXVF5cR893orFQW2iG1CwoS1qklfTyJ2FdwIdZu98safOQ0vHK6 9VdPl7urikh0oEnbO5Qwcmq/uoTkAyjDn0ae7+56JSIKqulhZ9A+/JI7amgyKpv42zhY h7K9RfuYevWlvfySHibDAbzhYQkNi/OQASIqAVe2P3QSTLtD0qEyIekxswm1CsjdxqFy 5uOA== X-Forwarded-Encrypted: i=1; AJvYcCVVK0lWzQPaGrNlrAqvitEuSg1aUP4yI1BQx5LniqkviaY5q4li4VV3yw/BL2HAuxgOXDb93EAVaVokW0wU@lists.postgresql.org X-Gm-Message-State: AOJu0YzTQe0dp6IFKYRp6/Dnc5uM1LjAWn5ObhPxal+qgO5+B3fRA6Ud jK4dDMqK6CA/aPMP+2/tF+ASIXH0onjC6+8PCHiiKSDaIRB8iIzWOTgI6unosj2ykLoYoJAFM2a xHzFVOwdDYNyYtJHW4YU0SMy4z+7dHmb5aps4ag== X-Gm-Gg: ASbGncvh8vcmJ9oZpaCAbRkxKjI4cTQz+99vX9H5wr/wbl7SB1WDSo7yVCVDdinX5bP /FRj9g/iHuP/qxeNgRhmCZqQw3lntfWA4cYOOhvrv+zCiKXbFBRsT7c+wESlFzMm03gLt1+PGP/ USahF9VqR7YLcW98Z9CL2Ew8L+4Q== X-Google-Smtp-Source: AGHT+IEjA0w/NfTWB7nX4dFD4iwQDFvMxRvBXhsRheoBILM6mTgaGYjvw14aqXWHKwzh87NpmQgi7oR0HfrGikAYL+Y= X-Received: by 2002:a17:90b:3909:b0:2fe:9783:afd3 with SMTP id 98e67ed59e1d1-2febab2ffa0mr18226755a91.2.1740973504586; Sun, 02 Mar 2025 19:45:04 -0800 (PST) MIME-Version: 1.0 References: <2961422.1740846012@sss.pgh.pa.us> In-Reply-To: From: me nefcanto Date: Mon, 3 Mar 2025 07:14:52 +0330 X-Gm-Features: AQ5f1JqYL91_UST5hwH4-EG6x_Qv7Gq5IGFLfA2eMJDStdQ-w29Qqk0R5AbLUlY Message-ID: Subject: Re: Please implement a catch-all error handler per row, for COPY To: Greg Sabino Mullane Cc: "David G. Johnston" , Tom Lane , pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000004deb26062f67fbba" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004deb26062f67fbba Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you all for responding. With respect, I don't understand why COPY gets related to ETL. All of the requirements I mentioned above have nothing to do with ETL. We have a table of categories. A hierarchical table. This is a multitenant app. Hundreds of thousands of records are in it. We want to calculate the hierarchical properties (nested set models, node depth, determining leaves, materialized paths, etc.) and then update the entire table. What does this have to do with ETL? Or as I said we create millions of records for products, attributes of products, and pricing and media of products to load test our system. Again, what does that have to do with ETL= ? The point is, that there is already an `on_error ignore` clause there. This means that somewhere there is a try/catch per row. If I'm wrong, please let me know. But when the `on_error ignore` catches problem x for each row, then it can catch all problems for each row without any performance problem= . Let me give you an example in C#: try { } catch (SomeException ex) { } becomes: try { } catch (Exception ex) { if (ex is SomeException) { } } The last catch clause catches everything. How does it affect performance? Running a simple if for hundreds of millions of iterations is literally nothing in time complexity. As I have specified in the bug thread, from 11 RDBMSs, 7 support this. Thus it's not an uncommon weird request. Regards Saeed On Sat, Mar 1, 2025 at 8:45=E2=80=AFPM Greg Sabino Mullane wrote: > FYI the -bugs thread in question: > > > https://www.postgresql.org/message-id/flat/CAEHBEOBCweDWGNHDaUk4%3D10HG0Q= XXJJAGXbEnFLMB30M%2BQw%2Bdg%40mail.gmail.com > > seems to imply the primary blocker was a unique constraint. > > Cheers, > Greg > > -- > Crunchy Data - https://www.crunchydata.com > Enterprise Postgres Software Products & Tech Support > > --0000000000004deb26062f67fbba Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you all for responding. With respect, I don't understa= nd why COPY gets related to ETL. All of the requirements I mentioned above = have nothing to do with ETL. We have a table of categories. A hierarchical = table. This is a multitenant app. Hundreds of thousands of records are in i= t. We want to calculate the hierarchical properties (nested set models, nod= e depth, determining leaves, materialized paths, etc.) and then update the = entire table. What does this have to do with ETL? Or as I said we create mi= llions of records for products, attributes of products, and pricing and med= ia of products to load test our system. Again, what does that have to do wi= th ETL?

The point is, that there is already an `on_error ignore` clause = there. This means that somewhere there is a try/catch per row. If I'm w= rong, please let me know. But when the `on_error ignore` catches problem x = for each row, then it can catch all problems for each row without any perfo= rmance problem.

Let me give you an example in C#:

try=C2=A0
{
}catch (S= omeException ex)
{
}

becomes:

try
{
}
catch (Exception ex)
{
=C2=A0 =C2=A0 = if (ex is SomeException)
=C2=A0 =C2=A0 {
=C2=A0 =C2=A0 }
}

The last catch c= lause catches everything. How does it affect performance? Running a simple = if for hundreds of millions of iterations is literally nothing in time comp= lexity.

As I have specified in the bug thread, from 11 RDBMSs, 7 support= this. Thus it's not an uncommon weird request.

Regards
Saeed

On Sat, Mar 1, 2025 at 8:45=E2=80=AFPM Greg Sabino Mull= ane <htamfids@gmail.com> wr= ote:

seem= s to imply the primary blocker was a unique constraint.

Cheers,
Greg

--
Crunchy Data - https://www.crunchy= data.com
Enterprise Postgres Software Products & Tech Sup= port

--0000000000004deb26062f67fbba--