pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Louis Tian <louis.tian@aquamonix.com.au>
To: Francisco Olarte <folarte@peoplecall.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: RE: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres
Date: Mon, 10 Apr 2023 01:05:30 +0000
Message-ID: <SY4P282MB1052CE9644C9B2E94E9B02FEA6959@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CA+bJJbwRHd+hiK2SHq3C1LSCJMDaCpbxnLRJc4SBEWHZw8_yrg@mail.gmail.com>
References: <SY4P282MB1052A434D91AC0656589B6AEA6919@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM>
	<CAH2-WzkSGmA-ZyMFu_EqQJ8tSgToj6h2=w8znTUa0Nw4oQvVHA@mail.gmail.com>
	<SY4P282MB10529BE09B553228FAA71163A6919@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM>
	<CA+bJJbwRHd+hiK2SHq3C1LSCJMDaCpbxnLRJc4SBEWHZw8_yrg@mail.gmail.com>

Hi Francisco, 

I think we need to make a distinction between an "operation" and a "statement".
The concept of idempotency applies to an "operation" not an entire statement. 
Like how HTTP "PUT" method is defined as "idempotent", you don't say actual HTTP PUT request is idempotent. 
With the "current_datetime" and "access_count+1", you are effectively changing the value passing to the UPSERT operator. 
Just like how you changed the payload of a PUT, then obviously there is no reason to expect the state of the database to remain the same. 

Cheers,
Louis Tian


-----Original Message-----
From: Francisco Olarte <folarte@peoplecall.com> 
Sent: Sunday, April 9, 2023 7:32 PM
To: Louis Tian <louis.tian@aquamonix.com.au>
Cc: Peter Geoghegan <pg@bowt.ie>; pgsql-general@lists.postgresql.org
Subject: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres

CAUTION: This email originated from outside of Envirada. Do not click links or open attachments unless you recognize the sender and know the content is safe.


(not the OP on idempotency)

On Sat, 8 Apr 2023 at 18:33, Louis Tian <louis.tian@aquamonix.com.au> wrote:
> > In general UPSERT (or any definition of it that I can think of) does 
> > not imply idempotency.
> "Idempotence is the property of certain operations in mathematics and computer science whereby they can be applied multiple times without changing the result beyond the initial application." from Wikipedia.
> the concept of Idempotence when applies to HTTP is consistent with the above. https://developer.mozilla.org/en-US/docs/Glossary/Idempotent. Or are you going by a different defintion that I am not aware of?
> If you execute the same upsert multiple times, the state of the database remains the same as if only execute once.
> 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 same as executing it only once.
> If the row doesn't exist, the first statement will insert that row and any subsequent will try to update, but the update has no real effect since it the value is exactly the same as the insert.
> So by defintion, upsert is idempotent.

Only on a narrow definition of upsert.

You are thinking on a narrow ( but very frequent ) use of "upsert"
statements, something like:

    insert on users(id,name) values (1,'x') on conflict(id) update set name='x'

But upsert can be used for things like:

   insert into last_access(id,cuando) values (1,current_timestamp) on
conflict(id) set cuando=current_timestamp

   insert into access_count(id, access_count) values (1,1) on
conflict(id) set access_count=access_count+1

Which are not idempotent ( and also frequent, I use both variants )

Francisco Olarte.
--
Message  protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.https://www.mailguard.com.au/mg
Click here to report this message as spam:
https://console.mailguard.com.au/ras/25ayvBFOMd/7rJjrYVDtTx03A1wSEIfeL/0.1



view thread (12+ messages)  latest in thread

Message-ID: <SY4P282MB1052CE9644C9B2E94E9B02FEA6959@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM>
Permalink:  ../SY4P282MB1052CE9644C9B2E94E9B02FEA6959@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM/
Also on:    postgresql.org/message-id/SY4P282MB1052CE9644C9B2E94E9B02FEA6959@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: louis.tian@aquamonix.com.au, folarte@peoplecall.com
  Subject: Re: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres
  In-Reply-To: <SY4P282MB1052CE9644C9B2E94E9B02FEA6959@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM>
  References: <SY4P282MB1052A434D91AC0656589B6AEA6919@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM> <CAH2-WzkSGmA-ZyMFu_EqQJ8tSgToj6h2=w8znTUa0Nw4oQvVHA@mail.gmail.com> <SY4P282MB10529BE09B553228FAA71163A6919@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM> <CA+bJJbwRHd+hiK2SHq3C1LSCJMDaCpbxnLRJc4SBEWHZw8_yrg@mail.gmail.com> <SY4P282MB1052CE9644C9B2E94E9B02FEA6959@SY4P282MB1052.AUSP282.PROD.OUTLOOK.COM>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox