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 1plYHv-00038x-CG for pgsql-general@arkaria.postgresql.org; Sun, 09 Apr 2023 16:53:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1plYHu-0003aT-A8 for pgsql-general@arkaria.postgresql.org; Sun, 09 Apr 2023 16:53:10 +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 1plL51-0007fE-6t for pgsql-general@lists.postgresql.org; Sun, 09 Apr 2023 02:46:59 +0000 Received: from mg-zaum-milo.mailguard.com.au ([52.147.60.132]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1plL4v-0007tn-KQ for pgsql-general@lists.postgresql.org; Sun, 09 Apr 2023 02:46:56 +0000 Received: from mg-zaum-milo.mailguard.com.au (localhost [127.0.0.1]) by mg-zaum-milo.mailguard.com.au (Postfix) with ESMTP id DEF556226E8; Sun, 9 Apr 2023 12:46:50 +1000 (AEST) Received: from AUS01-SY4-obe.outbound.protection.outlook.com (mail-sy4aus01lp2175.outbound.protection.outlook.com [104.47.71.175]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by mg-zaum-milo.mailguard.com.au (Postfix) with ESMTPS id CD87F621FBA; Sun, 9 Apr 2023 12:46:37 +1000 (AEST) ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=lQyiDSamuOZBK+iJnIFfc1a5U34NOWapr0ltGJ5/L4htoAt2DWl2YJ6CLAOcoFkI4p1EyYl3L+8ba+FkdVIoBoYOQ1wefQZ0NV7L6krHyABQPgDbxcTAX0SuoFwPdKU+11pvRqMO7gonmK3i+CDX6kumMTnBpJ2SnkuD67I1jdP3yIvngMpRRe9RPXDo9gUvFgfC9loiyM0o68JOsYGN4MtfBrlMzOwdo1j+XPDaKjiMnN+JTIXwLaJ7KsVx2BtV+2+xil5nwx9syk61akfdQj5l7uADB99TkIrcdATHaVS1D5zba9KmDGQK0kPDiCP5hVCCJWcnCL7n3D6WZzfj4A== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=8bJAhlaFw49FyoeJXMiRQpuyE4C7pq757KYbFIl7Ajk=; b=b6ixacw3mIYqLUDJc7rOJkuYEFHcBeeUna1LkjKsQrpkRrxqnP2z1FO3aambWq2hOoSWeGKTDQGa7B+0OpFEI86Q8hKvWd9YH6YwwR+Kb6g8RWi/UJXE1YxGTzK9xl2rU8DBCZOho/d5hO+K4Xl4gdNAdQeU19V66N7Xny8D/yaeKL8bEg99U6TgpfZ4+0s25auIp/RPUGwAl9a+DHmGQSs0cPazWZ1zRSbO4jVH30HSWohXtx82PS8KZPVCicsvRL3w+0Q3HU8fB+sJNan5bil0HP2wcVwilI8q91xdXKI+SMGtB3kybAkZkmfuXC2OUv7yTZl/05tLp20TokMBAA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=aquamonix.com.au; dmarc=pass action=none header.from=aquamonix.com.au; dkim=pass header.d=aquamonix.com.au; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aquamonix.com.au; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=8bJAhlaFw49FyoeJXMiRQpuyE4C7pq757KYbFIl7Ajk=; b=AaXkRRmT/bdgr4Vwl6/uuLipHuMSpoLSsSKcjZtRsAiyroUH9WBQnrUnznqwyL+SdaQLcRajLAuJm9yU0V3B7PWC1UUcVRjJxnVThxuxq22xGnYqEXyj1ihsdv12S5QIlJcewwxlZBkLd04KcFZ0VSinrLMdVGjp1C6/Ee9zmQ8= Received: from SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM (2603:10c6:10:a3::11) by ME3P282MB4161.AUSP282.PROD.OUTLOOK.COM (2603:10c6:220:188::14) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.6298.21; Sun, 9 Apr 2023 02:46:33 +0000 Received: from SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM ([fe80::39b6:baa7:9aca:b19e]) by SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM ([fe80::39b6:baa7:9aca:b19e%5]) with mapi id 15.20.6298.021; Sun, 9 Apr 2023 02:46:33 +0000 From: Louis Tian To: Adrian Klaver CC: "pgsql-general@lists.postgresql.org" Subject: Re: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres Thread-Topic: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres Thread-Index: AQHZaD++yYDuSAz8jEeE5AAohZ2RTq8exJcAgAAruW6AAwejAIAAQLA2 Date: Sun, 9 Apr 2023 02:46:33 +0000 Message-ID: References: <214b68bb-c0e3-ce9a-bf6e-2688cabad70b@aklaver.com> In-Reply-To: <214b68bb-c0e3-ce9a-bf6e-2688cabad70b@aklaver.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=aquamonix.com.au; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: SY4P282MB1052:EE_|ME3P282MB4161:EE_ x-ms-office365-filtering-correlation-id: c4d4dd62-5bfd-4ed4-e7d5-08db38a4a156 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: Q+ufkwF4GtG/lFFIEpR9ptiRwVSOMXqRezqiHufV+2MOampu8SgXeiTg4GIo2KnXAT57ipOCpbObkOgzZ0rvEMWydI/O1ESGFV5Km3N4sOi6tZ7T9lOsTE6M4gpXWwTD/q98BY27B3nXjArn7SXFbRBbdq50DQYsDubd+fM+sLSX32uyvnSZnkhKe3fSGmp0Z99BIp9dWBB/Oft5tqWHcVpztdJKecDzs8XB6oRAXeeROAJ58Lcxl4e8UweTNmp/Gzm+g3JJDbFVdBMQJW7ps9cLkLXaqiEc/mVsoGj/9CuXPHY8HnKiPM+2nZU/qVHox17sRzouAdOmHhTsh1Gw2ggzZ5o1iVo4JpXd8J5d9aEKo4BaYSUBIDYQPP3J8ER5CU3BQ4HflwIetUMxpO+I2Viy85Qx0AgzpiwHmLCkyOCEOTWz9jGcdTkv8DNnE+EdmkXIpFOt8RlLPPm6iCz4GzwIgXtDMX9XUiHSiCgWi4oKGJAKY87nU8NRN2DQdPYFyTgbk1wiYlFCbaqqNWxCj/J5Vq7welQUI3UsGXzI2RcMWOTK+/Lbz0opz9XA2KGEe5w1VlvdVzHWKWVgF6TbxjoyFefKr6kYb73wLM6fOlU= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230028)(136003)(366004)(346002)(376002)(39830400003)(396003)(451199021)(33656002)(86362001)(2906002)(38070700005)(66899021)(5660300002)(55016003)(71200400001)(7696005)(53546011)(83380400001)(186003)(66574015)(9686003)(6506007)(26005)(966005)(4326008)(478600001)(66946007)(66556008)(66476007)(66446008)(64756008)(6916009)(76116006)(122000001)(38100700002)(41300700001)(8676002)(8936002)(316002)(44832011)(52536014);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?tyv+gkj0tS1TlI89G6VJZBCNXY+Nx3feD/u/fKaZLcnKLdhyfi9300WRkZ?= =?iso-8859-1?Q?g5l3uor+Ne/nhDWVdmvjliXZQNZd6xjq066BqgJQ6J3z2F4FnkhIXOmoXP?= =?iso-8859-1?Q?UCelYjmGdoM0gCd+D+XapVxLvy1aqc2jE8rSDrGXj8UUxNGnD9FxYbFHh2?= =?iso-8859-1?Q?MSBYWQQixzlatv2gC1bXYc1KMBNEd7BI30xBaqsCuBMAIX2hKOd/FHa5tV?= =?iso-8859-1?Q?dbZQKWxObjkPJrCNNcX/tBPBf7wpqgTeh3qh79hFgaJmRwYaDse8vLJxx6?= =?iso-8859-1?Q?rqJFvAzGN7RjNz8SHm3bLK6jKvSpOnjopKVtNbxOd+b1QBAr4XehBdt3rs?= =?iso-8859-1?Q?hV6NmJxa7s3dj0BdO35OsPYSbf4gap7oPS2yFqmf/4l2hcG/VOP+ZlsNwL?= =?iso-8859-1?Q?HH2fddtGuNwLPM7dSgHXZzpxreB1Fuk9yX4Qw4CKzjfc+BbaCr2pIcV7kI?= =?iso-8859-1?Q?QgpB43oZB5NmE8QiCsu04Xe1dAoS6gxlwAMYIJn3QBBUe4r17yYY2DKzzM?= =?iso-8859-1?Q?m/C8i2E8P4mO6+FOlKuHOnlRJ6OojymWLutQdaY52fxWnzYSszREXZ6yxB?= =?iso-8859-1?Q?6/vcVdAlR+GdKT0T9GW1618x3ve0O69FLXj7+wfbDpvt0jkM7GT5q9nfIp?= =?iso-8859-1?Q?az2NHCl4FCLgXS0/USZqqVwFH8V9A8eZCJifd1Q/S1GrapQWarVqy9jaRm?= =?iso-8859-1?Q?QfYmPr3zS7m0vp9n9CpmPPHAfgekcWoCCnX8IjFJAuBAhOt5ZJd7g7XepI?= =?iso-8859-1?Q?rneSXfKHo1f4qQWQBsQvviVKdqs9uh0BzEYmZZ+t7wobSD6ZAmx5i1El8l?= =?iso-8859-1?Q?mTvxemiwP8MsTp7UYNnC6C9SA0YMXvvz3nUD40jPygHomc0bOyrgloBAC1?= =?iso-8859-1?Q?niSOXsbhUEl+xRNnefVU3ZTAIuDU4M873QmlgWcmbq8cKr9MFVSCU9qS3Z?= =?iso-8859-1?Q?R4uC5fERh+dZQRATaFNeYh6royyoKv7BJZZW0UwSttGkP/DahytQWYc2WY?= =?iso-8859-1?Q?uRhDr7xx8yCwUDRI0YZfLDTL3dzdUxebTZdb7cSvBUF+rTNtSRDIRSo+to?= =?iso-8859-1?Q?MjXyc+EGnAp2Cvh7tVfjzbDeGZ0No18vdaIZeufqLaTlcAdyTGizSka0Sq?= =?iso-8859-1?Q?sc9CLIZT24ymZG3H2ooqd4KDqb0h4J610dOSNcWYcviZlcjaNT1aSCTQBA?= =?iso-8859-1?Q?dDYJqY3FytoqBOFwCdyguSnLMPtt4OWWotKcjfSu+KnYQ2+N+BkSvW6GLy?= =?iso-8859-1?Q?MgA3yp/oLrSG7yxe0cpibsYpxtEbeyYRPxRNjYecZ+B0hNSx4csmpKyeyO?= =?iso-8859-1?Q?VOMpBQ6zz1EzGbN0TamvQXt33tI06fr5ZwNvNaAgPohaXfLfxixAla6Axj?= =?iso-8859-1?Q?F9+hyR+Db19jqANxZAMILiL7lmwln6BdgxMEOUw7rsXEXeKozq7ROBy83L?= =?iso-8859-1?Q?atitchzooVh1xVv7BmUpMzNjbIgPRJ34lvPzqkBK7FCkx4kEs2m+83Hejt?= =?iso-8859-1?Q?l/t/+qpWb0da2jzNn8qIPt5Ao933ed790xE9aoBcLqOeHCwIHkl7iaok1R?= =?iso-8859-1?Q?L5UDayT+2um+7TZMKo09vq98gZQJIP6QjkYOMWxrzcqbqkvh8aTy340XUQ?= =?iso-8859-1?Q?54pLPLvnyD80Kib0tQC1vJvLzLa83JXPhsr4tFH1zlQzni0xFT/ok12A?= =?iso-8859-1?Q?=3D=3D?= Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-OriginatorOrg: aquamonix.com.au X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-Network-Message-Id: c4d4dd62-5bfd-4ed4-e7d5-08db38a4a156 X-MS-Exchange-CrossTenant-originalarrivaltime: 09 Apr 2023 02:46:33.5636 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 0c1f77a4-88ee-41e8-865f-5d8ce56b5c6f X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: tUia7LxV/AuWTudn8g+qoyFvzHmJsGtfI2hDI0mZMOYdVmhMDNpk6Bdw5D/kE8+iA6IOGf4P3cMxRw44ciC7wG7RqOoBsUXeUksd5xYJUEQ= X-MS-Exchange-Transport-CrossTenantHeadersStamped: ME3P282MB4161 X-MailGuard-UID: 6432270d7f268f6f X-MailGuard-ID: 6432270fb2335e List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi Adrian,=A0=0A= =0A= >=A0No it is not as Israel Brewster pointed out.=0A= I think we need make a distinction between an operation and a statement her= e=0A= The examples Israel presented and yours are both non-idempotent statements = because the different value being upserted each time the statement is execu= ted.=A0Not because upsert as an operation is not idempotent. =0A= Likewise, In HTTP, PUT method is defined as idempotent. No one would expect= different PUT requests with different payload to be idempotent. =0A= =0A= Also, I don't think I am not the only one who think upsert is logically ide= mpotent.=A0Just a few seconds of searching on google.=0A= - "The upsert action is a combination of insert and update. It allows us to= write idempotent statements". (https://jonmeyers.io/blog/use-on-conflict-t= o-upsert-in-postgresql. )=0A= -"One common example of an idempotent operation is an upsert (update or ins= ert) statement in a database." (https://www.arecadata.com/core-data-enginee= ring-concepts-idempotency)=0A= - "A POST request means that the request is not idempotent, but an "upsert"= request is idempotent." (https://softwareengineering.stackexchange.com/que= stions/426225/rest-how-to-upsert-a-resource-without-an-specific-resource-ur= l#:~:text=3DA%20POST%20request%20means%20that,%22upsert%22%20request%20is%2= 0idempotent.)=0A= While no as direct as the article above, but the issue reported in those pl= aces are essentially stem from the same understanding. =0A= - https://stackoverflow.com/questions/48816629/on-conflict-do-nothing-in-po= stgres-with-a-not-null-constraint=0A= - https://www.postgresql.org/message-id/flat/1996438.1604952901%40sss.pgh.p= a.us#ace8adc1354265aca5672028faea0c0f=0A= So at least I am not only one imaging things?=0A= - "a ready made statement for idempotent data like Mongo's upsert" (https:/= /www.compose.com/articles/the-potency-of-idempotent-with-rabbitmq-and-mongo= db-upsert/)=0A= =0A= > And therein lies your problem, you are imagining something that does not= =0A= > exist and more to the point will most likely not exist as it would break = all code that depends on above behavior.=0A= I never argue the way postgresql does for insert ... on conflict is wrong a= nd need to change. =0A= I said "I think it is doing what it reads quite literally" just like in you= r words "It does what is advertised on the tin". So there is no disagreemen= t here. So I am not saying "insert on conflict" need to change. =0A= What I am point out here is, the "insert...on conflict do update" is not a = true "upsert". =0A= I am saying in my opinion it would be nice see UPSERT statement to be added= to PostgreSQL so upsert can work "out of box" even when there're not null = constraints on that table.=0A= =0A= It might never happen judging from the replies I am getting but that's fine= . The solution you have showed me is good enough as a workaround (to not de= preciate it's value, just not as easy/bullet proof as it could be in my opi= nion). =0A= It was just a question just to confirm my understanding so I got what I nee= d, so thank you all for that. =0A= =0A= =0A= Cheers,=0A= Louis Tian=0A= =0A= =0A= From: Adrian Klaver =0A= Sent: Sunday, April 9, 2023 7:51 AM=0A= To: Louis Tian ; Peter Geoghegan = =0A= Cc: pgsql-general@lists.postgresql.org = =0A= Subject: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres =0A= =A0=0A= CAUTION: This email originated from outside of Envirada. Do not click links= or open attachments unless you recognize the sender and know the content i= s safe.=0A= =0A= =0A= On 4/6/23 17:49, Louis Tian wrote:=0A= > Hi Peter,=0A= >=0A= > Thanks for your reply. Appreciate the help and discussion.=0A= >=0A= >>=A0=A0 In general UPSERT (or any definition of it that I can think of) do= es=0A= >> not imply idempotency.=0A= >=0A= > "Idempotence is the property of certain operations in mathematics and com= puter science whereby they can be applied multiple times without changing t= he result beyond the initial application." from Wikipedia.=0A= > the concept of Idempotence when applies to HTTP is consistent with the ab= ove. https://developer.mozilla.org/en-US/docs/Glossary/Idempotent. Or are y= ou going by a different defintion that I am not aware of?=0A= > If you execute the same upsert multiple times, the state of the database = remains the same as if only execute once.=0A= > If a row already exists, the first statement will update the row so does = any subsequent statements. executing the same update multiple time is the s= ame as executing it only once.=0A= > If the row doesn't exist, the first statement will insert that row and an= y subsequent will try to update, but the update has no real effect since it= the value is exactly the same as the insert.=0A= > So by defintion, upsert is idempotent.=0A= =0A= No it is not as Israel Brewster pointed out.=0A= =0A= To his example I would add:=0A= =0A= alter some_table add column ts_upsert_update timestamptz;=0A= =0A= insert into some_table values('foo', 'bar') on conflict(tbl_id) do=0A= update set foo_fld =3D excluded.foo_fld, bar_fld =3D some_table.bar_fld,=0A= ts_upsert_update =3D now();=0A= =0A= You are substituting whatever definition you have in your head for the=0A= definition as it actually exists.=0A= =0A= >=0A= >> It could just be a unique index or a unique constraint. So you can=0A= >> upsert on any individual unique constraint/index, or the primary key.=0A= >> Of course there might be several on a given table, but you can only=0A= >> use one as the "conflict arbiter" per statement.=0A= >=0A= > Understand that I can use any unique constraint with on conflict.=0A= > But semantically the only correct one is the primary key, since that's wh= at identifies a row logically.=0A= > In that sense, any unique column(s) is a potential candidate for primary = key.=0A= > It's more of a pedantic point rather than pragmatic one.=0A= > It's less of a problem for PostgreSQL where the semantic importance of pr= imary key is not manifested at implementation level, since all index points= to the tuple directly=0A= > Whereas it is more import for Databaes like MySQL where the secondary ind= ex points to the primary key index.=0A= =0A= Again you are dealing with the imagined instead of the reality. Along=0A= that line you left out that a 'exclusion constraint violation error' can=0A= also trigger the ON CONFLICT.=0A= =0A= =0A= > Use some pesudo code might be helpful here to explain the difference.=0A= >=0A= > How on conflict works at the moment.=0A= >=0A= > try {=0A= >=A0=A0 insert row=0A= > } catch (duplicated key error) {=0A= >=A0=A0=A0 update row=0A= > }=0A= >=0A= > How I think it upsert should work=0A= =0A= And therein lies your problem, you are imagining something that does not=0A= exist and more to the point will most likely not exist as it would break=0A= all code that depends on above behavior.=0A= =0A= >=0A= > if (new.id exists) {=0A= >=A0=A0 update row=0A= > } else {=0A= >=A0=A0 insert row=0A= > }=0A= >=0A= =0A= > I am not expecting an error here. The problem is with no conflict it alwa= ys go down the insert path first and results in a not null constraint error= .=0A= =0A= To be expected, as after all the command is:=0A= =0A= INSERT INTO ...=0A= =0A= > While I am expecting the insert is never executed in the first place when= that row already exist (as identified by it primary key). So the update ex= ecute without error.=0A= > I hope the pesudo code above is enough to clarify the difference?=0A= >=0A= =0A= > Cheers,=0A= > Louis Tian=0A= >=0A= >=0A= =0A= --=0A= Adrian Klaver=0A= adrian.klaver@aklaver.com=0A= =0A= --=0A= Message=A0 protected by MailGuard: e-mail anti-virus, anti-spam and content= filtering.https://www.mailguard.com.au/mg=0A= Click here to report this message as spam:=0A= https://console.mailguard.com.au/ras/25am4N38I1/7rIyiJl23oANcTXYht5xW0/0=0A=