(not the OP on idempotency)
On Sat, 8 Apr 2023 at 18:33, Louis Tian <louis.t...@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.
