Re: [EXTERNAL]: Re: [EXTERNAL]: Re: UPSERT in Postgres

Hi Louis:

On Mon, 10 Apr 2023 at 03:05, Louis Tian <louis(dot)tian(at)aquamonix(dot)com(dot)au> wrote:
> I think we need to make a distinction between an "operation" and a "statement".
OK

> The concept of idempotency applies to an "operation" not an entire statement.
I think I'll need a definition of both to say anything on this.

> Like how HTTP "PUT" method is defined as "idempotent", you don't say actual HTTP PUT request is idempotent.
Well, in HTTP a request is half defined.

> With the "current_datetime" and "access_count+1", you are effectively changing the value passing to the UPSERT operator.
I can agree with current_datetime ( I doubt I used that... checks the
BOTTOM QUOTE ... effectively I did not ) which can be thought as a
fancy macro for passing a value, but access_count+1 is an expression,
I do not and could not pass it, the engine has to read the row(s)
affected by the insert to use it.

Anyway, what I was trying to point is that UPSERT has no standard
definition, and that the usual implementations of the concept can be
used to build idempotent "requests", but they are many times used for
non-idempotent ones, like timestamp logging or counter updates.

As postgres does not have, AFAIK, an "UPSERT" statement I think you
will need to define it before further discussion, something like
mapping it to an insert on conflict or similar.

> 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.

I did not change the payload of a put, I assume you refer to the
database request, but I'm too old to go hunting for the exact thing
you refer to on a response which quotes even my signatures and the
virus scanner lines.

You can repeat a PUT request or not repeat it. HTTP request cover body
too, changing body is like changing URI, or changing method, it is a
different request. Idempotent in HTTP is same request, body included,
same response. Not similar request, similar response.

Francisco Olarte.