pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: [EXTERNAL]: Re: UPSERT in Postgres
9+ messages / 6 participants
[nested] [flat]

* Re: [EXTERNAL]: Re: UPSERT in Postgres
@ 2023-04-07 00:49  Louis Tian <louis.tian@aquamonix.com.au>
  1 sibling, 4 replies; 9+ messages in thread

From: Louis Tian @ 2023-04-07 00:49 UTC (permalink / raw)
  To: Peter Geoghegan <pg@bowt.ie>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Hi Peter,

Thanks for your reply. Appreciate the help and discussion.

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

> It could just be a unique index or a unique constraint. So you can
> upsert on any individual unique constraint/index, or the primary key.
> Of course there might be several on a given table, but you can only
> use one as the "conflict arbiter" per statement.

Understand that I can use any unique constraint with on conflict. 
But semantically the only correct one is the primary key, since that's what identifies a row logically.
In that sense, any unique column(s) is a potential candidate for primary key.
It's more of a pedantic point rather than pragmatic one.
It's less of a problem for PostgreSQL where the semantic importance of primary key is not manifested at implementation level, since all index points to the tuple directly
Whereas it is more import for Databaes like MySQL where the secondary index points to the primary key index. 

> It sort of has to work that way, though. In general your example might
> *not* fail, due to a row-level before trigger in the insert path.

Do you mean having the trigger to check whether new."name" is set if not use the old."name" as fallback ?

> Why doesn't your proposed upsert syntax have the same problem? I mean,
> how could it not? I guess it doesn't if you assume that it'll never
> take the insert path with your not NULL constraint example?

Use some pesudo code might be helpful here to explain the difference. 

How on conflict works at the moment.

try {
 insert row
} catch (duplicated key error) {
  update row
}

How I think it upsert should work

if (new.id exists) {
 update row 
} else {
 insert row
}

I would argue that later is a correct form of upsert given it's definition. 
The two are not equivalent when there is not null constraint on any non primary key column.

> But if you know that for sure, why not just use a regular update statement? 
Yes, in general it is not know whether the insert or update path should be taken. 

> On the other hand, if you're not sure if the insert path can be taken,
> then why is it actually helpful to not just throw an error at the
> earliest opportunity?
I am not expecting an error here. The problem is with no conflict it always go down the insert path first and results in a not null constraint error.
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 execute without error.
I hope the pesudo code above is enough to clarify the difference? 

> The MERGE command has various race conditions that are particularly
> relevant to UPSERT type use cases. See the wiki page you referenced
> for a huge amount of information on this.
Thanks for the pointer. Reading into it.

Cheers,
Louis Tian


From: Peter Geoghegan <pg@bowt.ie>
Sent: Friday, April 7, 2023 6:58 AM
To: Louis Tian <louis.tian@aquamonix.com.au>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: [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.


On Thu, Apr 6, 2023 at 1:21 PM Louis Tian <louis.tian@aquamonix.com.au> wrote:
> An implicit assumption behind this definition is that table must have a primary key for the upsert operation to make sense since it's the primary key that uniquely identifies a row.

It could just be a unique index or a unique constraint. So you can
upsert on any individual unique constraint/index, or the primary key.
Of course there might be several on a given table, but you can only
use one as the "conflict arbiter" per statement.

> But can we achieve the same effect with "INSERT ... ON CONFLICT DO UPDATE" like a lot of references on the internet seems to suggest.
>
> insert into person (id, name) values (0, 'foo') on conflict ("id") do update set id=excluded.id, name=excluded.name
> insert into person (id, is_active) values (0, true) on conflict ("id") do update set id=excluded.id, is_active=excluded.is_active
>
> Unfortunately. the second statement will fail due to violation of the not null constraint on the "name" column.
> PostgreSQL will always try to insert the row into the table first. and only fallback to update when the uniqueness constraint is violated.
> Is this behavior wrong? maybe not, I think it is doing what it reads quite literally.

It sort of has to work that way, though. In general your example might
*not* fail, due to a row-level before trigger in the insert path.

Why doesn't your proposed upsert syntax have the same problem? I mean,
how could it not? I guess it doesn't if you assume that it'll never
take the insert path with your not NULL constraint example? But if you
know that for sure, why not just use a regular update statement? On
the other hand, if you're not sure if the insert path can be taken,
then why is it actually helpful to not just throw an error at the
earliest opportunity?

Surely upsert means "update or insert", so why wouldn't the user expect
to see an error like this, independent of the specifics of the row in question?
Isn't the user tacitly saying "I don't specifically know if the update or insert
path will be taken in respect of any given row" by using ON CONFLICT
DO UPDATE in the first place?

> That being said, I have never had a need for the ON CONFLICT DO UPDATE statement other than where I need upsert.
> But using it as "upsert" is only valid when the table is absent of any NOT NULL constraint on it's non primary key columns.

I don't know what you mean by that. "Valid"?

> The MERGE command introduced in PG15 in theory can be used to do UPSERT properly that is void of the aforementioned limitation.
> The downside is it is rather verbose.

The MERGE command has various race conditions that are particularly
relevant to UPSERT type use cases. See the wiki page you referenced
for a huge amount of information on this.

> *Feature Request*
> Given that UPSERT is an *idempotent* operator it is extremely useful.

In general UPSERT (or any definition of it that I can think of) does
not imply idempotency.


--
Peter Geoghegan
--
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/259w6P5THe/6Ld9hKnxHU77IFkjdp0Xsh/0.1


^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [EXTERNAL]: Re: UPSERT in Postgres
@ 2023-04-07 01:27  Louis Tian <louis.tian@aquamonix.com.au>
  1 sibling, 2 replies; 9+ messages in thread

From: Louis Tian @ 2023-04-07 01:27 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Hi Adrian,

Thank you. I think this is a better approach than trigger-based solution, at least for my taste.
That being said, it does require some logic to push to the client side (figuring out which required column value is missing and set it value to the existing one via reference of the table name).
Still wish there would be UPSERT statement that can handle this and make dev experience better.

Cheers,
Louis Tian
________________________________
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Friday, April 7, 2023 7:00 AM
To: Louis Tian <louis.tian@aquamonix.com.au>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: [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.


On 4/5/23 23:21, Louis Tian wrote:
> This is a question/feature request.
>

> Given the definition of upsert, I'd expect an upsert command to do the following.
> - `upsert into person (id, name) values (0, 'foo')` to insert a new row
> - `upsert into person (id, is_active) values (0, true)` updates the is_active column for the row inserted above
>
> Naturally, since there isn't a real upsert command in PostgreSQL this won't work today.
> But can we achieve the same effect with "INSERT ... ON CONFLICT DO UPDATE" like a lot of references on the internet seems to suggest.
>
> insert into person (id, name) values (0, 'foo') on conflict ("id") do update set id=excluded.id, name=excluded.name
> insert into person (id, is_active) values (0, true) on conflict ("id") do update set id=excluded.id, is_active=excluded.is_active

insert into person (id, name, is_active) values (0, '', true) on
conflict ("id") do update set id=excluded.id, name=person.name,
is_active=excluded.is_active ;
INSERT 0 1

select * from person;
  id | name | is_active
----+------+-----------
   0 | foo  | t

>
> Unfortunately. the second statement will fail due to violation of the not null constraint on the "name" column.
> PostgreSQL will always try to insert the row into the table first. and only fallback to update when the uniqueness constraint is violated.
> Is this behavior wrong? maybe not, I think it is doing what it reads quite literally.
> That being said, I have never had a need for the ON CONFLICT DO UPDATE statement other than where I need upsert.
> But using it as "upsert" is only valid when the table is absent of any NOT NULL constraint on it's non primary key columns.
> So, if my experience/use case is typical (meaning the main purpose / use case for ON CONFLICT DO UPDATE is to support upsert) then it can be argue the current behavior is incorrect?
>
> This has been a source confusion to say at least.
> https://stackoverflow.com/questions/48816629/on-conflict-do-nothing-in-postgres-with-a-not-null-cons...
> https://www.postgresql.org/message-id/flat/1996438.1604952901%40sss.pgh.pa.us#ace8adc1354265aca56720...
>
> The MERGE command introduced in PG15 in theory can be used to do UPSERT properly that is void of the aforementioned limitation.
> The downside is it is rather verbose.
>
> *Question*
> This there a way to do an upsert proper prior to PG15?
>
> *Feature Request*
> Given that UPSERT is an *idempotent* operator it is extremely useful.
> Would love to see an UPSERT command in PostgreSQL so one can 'upsert' properly and easily.
>
>
> Regards,
> Louis Tian
>
>
>
>
>
>
>
>
>

--
Adrian Klaver
adrian.klaver@aklaver.com

--
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/259w94L3yw/5Vb4QHWbRwfP3KeOMAvzU8/-2.1

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [EXTERNAL]: Re: UPSERT in Postgres
@ 2023-04-07 20:43  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Louis Tian <louis.tian@aquamonix.com.au>
  1 sibling, 0 replies; 9+ messages in thread

From: Adrian Klaver @ 2023-04-07 20:43 UTC (permalink / raw)
  To: Louis Tian <louis.tian@aquamonix.com.au>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On 4/6/23 18:27, Louis Tian wrote:
> Hi Adrian,
> 
> Thank you. I think this is a better approach than trigger-based 
> solution, at least for my taste.
> That being said, it does require some logic to push to the client side 
> (figuring out which required column value is missing and set it value to 
> the existing one via reference of the table name).
> Still wish there would be UPSERT statement that can handle this and make 
> dev experience better.

It does what is advertised on the tin:

https://www.postgresql.org/docs/current/sql-insert.html

The optional ON CONFLICT clause specifies an alternative action to 
raising a unique violation or exclusion constraint violation error

[...]

ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome; 
provided there is no independent error, one of those two outcomes is 
guaranteed, even under high concurrency. This is also known as UPSERT — 
“UPDATE or INSERT”.

You got caught by the '...independent error...' part. The same thing 
would have happened if you had just done:

insert into person (id, is_active) values(0, true);
ERROR:  null value in column "name" of relation "person" violates 
not-null constraint


The insert has to be valid on its own before you get to the 'alternative 
action to raising a unique violation or exclusion constraint violation 
error' part. Otherwise you are asking Postgres to override this 'insert 
into person (id, is_active)' and guess you really wanted something like:

insert into person (id, name, is_active) values(0, <existing value>, true)

I'm would not like the server making those guesses on my behalf.

> ,
> Cheers,
> Louis Tian
> ------------------------------------------------------------------------

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [EXTERNAL]: Re: UPSERT in Postgres
@ 2023-04-07 20:52  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Louis Tian <louis.tian@aquamonix.com.au>
  1 sibling, 0 replies; 9+ messages in thread

From: Adrian Klaver @ 2023-04-07 20:52 UTC (permalink / raw)
  To: Louis Tian <louis.tian@aquamonix.com.au>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On 4/6/23 18:27, Louis Tian wrote:
> Hi Adrian,
> 
> Thank you. I think this is a better approach than trigger-based 
> solution, at least for my taste.
> That being said, it does require some logic to push to the client side 
> (figuring out which required column value is missing and set it value to 
> the existing one via reference of the table name).
> Still wish there would be UPSERT statement that can handle this and make 
> dev experience better.

Another way to make the experience easier:

alter table person alter COLUMN name set default 'cat';

  \d person
                   Table "public.person"
   Column   |  Type   | Collation | Nullable |   Default
-----------+---------+-----------+----------+-------------
  id        | integer |           | not null |
  name      | text    |           | not null | 'cat'::text
  is_active | boolean |           |          |

select * from person;
  id | name | is_active
----+------+-----------
   0 | foo  | NULL


insert into person(id, is_active) values (0,  true) on conflict ("id") 
do update set (id, is_active) = (excluded.id,  excluded.is_active);
INSERT 0 1

select * from person;
  id | name | is_active
----+------+-----------
   0 | foo  | t


> 
> Cheers,
> Louis Tian

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [EXTERNAL]: Re: UPSERT in Postgres
@ 2023-04-08 17:09  Israel Brewster <ijbrewster@alaska.edu>
  parent: Louis Tian <louis.tian@aquamonix.com.au>
  3 siblings, 0 replies; 9+ messages in thread

From: Israel Brewster @ 2023-04-08 17:09 UTC (permalink / raw)
  To: Louis Tian <louis.tian@aquamonix.com.au>; +Cc: Peter Geoghegan <pg@bowt.ie>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

> On Apr 6, 2023, at 4:49 PM, Louis Tian <louis.tian@aquamonix.com.au> wrote:
> 
> Hi Peter,
> 
> Thanks for your reply. Appreciate the help and discussion.
> 
>>  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. 

Not necessarily. Consider the following UPSERT statement:

INSERT INTO customer_order_counts (customer_id,order_count) VALUES (123456, 1) ON CONFLICT (customer_id) DO UPDATE SET order_count=customer_order_counts.order_count+1;

That is completely valid (I tested it), and actually makes sense as something you might want to do - keep track of how many orders a customer has placed, for example if you only keep the order records for 6 months, but still want to know the total number of orders the customer has placed. If it is a new customer, you insert a record for the customer with an order count of 1. Otherwise, you update the record to increment the order count. Clearly this is NOT an idempotent operation - every time you run it, it changes the order count, so the state of the database does NOT remain the same as if you only execute it 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.
> 
>> It could just be a unique index or a unique constraint. So you can
>> upsert on any individual unique constraint/index, or the primary key.
>> Of course there might be several on a given table, but you can only
>> use one as the "conflict arbiter" per statement.
> 
> Understand that I can use any unique constraint with on conflict. 
> But semantically the only correct one is the primary key, since that's what identifies a row logically.

Not really. Generally when I am doing an UPSERT, I am NOT using the primary key, but rather some other UNIQUE-ly indexed column(s). My primary key is typically an ID column that is defined as a serial, and automatically generated by the database. The unique column I use for the upset, however, is generally something that would identify the row to a human - such as Station ID and timestamp  columns in a database I have of seismic readings. Each reading gets a unique ID (the primary key) that identifies it to the database and is used in joins. However, occasionally a reading gets updated, so I update the row, based not on the primary key (which I don’t know for incoming data), but on the station and timestamp. UPSERT, but not based on the primary key.

---
Israel Brewster
Software Engineer
Alaska Volcano Observatory 
Geophysical Institute - UAF 
2156 Koyukuk Drive 
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145

> In that sense, any unique column(s) is a potential candidate for primary key.
> It's more of a pedantic point rather than pragmatic one.
> It's less of a problem for PostgreSQL where the semantic importance of primary key is not manifested at implementation level, since all index points to the tuple directly
> Whereas it is more import for Databaes like MySQL where the secondary index points to the primary key index. 





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [EXTERNAL]: Re: UPSERT in Postgres
@ 2023-04-08 21:51  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Louis Tian <louis.tian@aquamonix.com.au>
  3 siblings, 1 reply; 9+ messages in thread

From: Adrian Klaver @ 2023-04-08 21:51 UTC (permalink / raw)
  To: Louis Tian <louis.tian@aquamonix.com.au>; Peter Geoghegan <pg@bowt.ie>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On 4/6/23 17:49, Louis Tian wrote:
> Hi Peter,
> 
> Thanks for your reply. Appreciate the help and discussion.
> 
>>   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.

No it is not as Israel Brewster pointed out.

To his example I would add:

alter some_table add column ts_upsert_update timestamptz;

insert into some_table values('foo', 'bar') on conflict(tbl_id) do 
update set foo_fld = excluded.foo_fld, bar_fld = some_table.bar_fld, 
ts_upsert_update = now();

You are substituting whatever definition you have in your head for the 
definition as it actually exists.

> 
>> It could just be a unique index or a unique constraint. So you can
>> upsert on any individual unique constraint/index, or the primary key.
>> Of course there might be several on a given table, but you can only
>> use one as the "conflict arbiter" per statement.
> 
> Understand that I can use any unique constraint with on conflict.
> But semantically the only correct one is the primary key, since that's what identifies a row logically.
> In that sense, any unique column(s) is a potential candidate for primary key.
> It's more of a pedantic point rather than pragmatic one.
> It's less of a problem for PostgreSQL where the semantic importance of primary key is not manifested at implementation level, since all index points to the tuple directly
> Whereas it is more import for Databaes like MySQL where the secondary index points to the primary key index.

Again you are dealing with the imagined instead of the reality. Along 
that line you left out that a 'exclusion constraint violation error' can 
also trigger the ON CONFLICT.


> Use some pesudo code might be helpful here to explain the difference.
> 
> How on conflict works at the moment.
> 
> try {
>   insert row
> } catch (duplicated key error) {
>    update row
> }
> 
> How I think it upsert should work

And therein lies your problem, you are imagining something that does not 
exist and more to the point will most likely not exist as it would break 
all code that depends on above behavior.

> 
> if (new.id exists) {
>   update row
> } else {
>   insert row
> }
> 

> I am not expecting an error here. The problem is with no conflict it always go down the insert path first and results in a not null constraint error.

To be expected, as after all the command is:

INSERT INTO <some_table> ...

> 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 execute without error.
> I hope the pesudo code above is enough to clarify the difference?
> 

> Cheers,
> Louis Tian
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [EXTERNAL]: Re: UPSERT in Postgres
@ 2023-04-08 22:05  Benedict Holland <benedict.m.holland@gmail.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Benedict Holland @ 2023-04-08 22:05 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Louis Tian <louis.tian@aquamonix.com.au>; Peter Geoghegan <pg@bowt.ie>; pgsql-general <pgsql-general@lists.postgresql.org>

This went on too long. On conflict is the solution. It has been since at
least 9. I have run that in a production stored proc without a single
problem.

This is an actual and literal solution.

Thanks,
Ben

On Sat, Apr 8, 2023, 5:51 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 4/6/23 17:49, Louis Tian wrote:
> > Hi Peter,
> >
> > Thanks for your reply. Appreciate the help and discussion.
> >
> >>   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.
>
> No it is not as Israel Brewster pointed out.
>
> To his example I would add:
>
> alter some_table add column ts_upsert_update timestamptz;
>
> insert into some_table values('foo', 'bar') on conflict(tbl_id) do
> update set foo_fld = excluded.foo_fld, bar_fld = some_table.bar_fld,
> ts_upsert_update = now();
>
> You are substituting whatever definition you have in your head for the
> definition as it actually exists.
>
> >
> >> It could just be a unique index or a unique constraint. So you can
> >> upsert on any individual unique constraint/index, or the primary key.
> >> Of course there might be several on a given table, but you can only
> >> use one as the "conflict arbiter" per statement.
> >
> > Understand that I can use any unique constraint with on conflict.
> > But semantically the only correct one is the primary key, since that's
> what identifies a row logically.
> > In that sense, any unique column(s) is a potential candidate for primary
> key.
> > It's more of a pedantic point rather than pragmatic one.
> > It's less of a problem for PostgreSQL where the semantic importance of
> primary key is not manifested at implementation level, since all index
> points to the tuple directly
> > Whereas it is more import for Databaes like MySQL where the secondary
> index points to the primary key index.
>
> Again you are dealing with the imagined instead of the reality. Along
> that line you left out that a 'exclusion constraint violation error' can
> also trigger the ON CONFLICT.
>
>
> > Use some pesudo code might be helpful here to explain the difference.
> >
> > How on conflict works at the moment.
> >
> > try {
> >   insert row
> > } catch (duplicated key error) {
> >    update row
> > }
> >
> > How I think it upsert should work
>
> And therein lies your problem, you are imagining something that does not
> exist and more to the point will most likely not exist as it would break
> all code that depends on above behavior.
>
> >
> > if (new.id exists) {
> >   update row
> > } else {
> >   insert row
> > }
> >
>
> > I am not expecting an error here. The problem is with no conflict it
> always go down the insert path first and results in a not null constraint
> error.
>
> To be expected, as after all the command is:
>
> INSERT INTO <some_table> ...
>
> > 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 execute without error.
> > I hope the pesudo code above is enough to clarify the difference?
> >
>
> > Cheers,
> > Louis Tian
> >
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
>
>

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [EXTERNAL]: Re: UPSERT in Postgres
@ 2023-04-09 09:26  Alban Hertroys <haramrae@gmail.com>
  parent: Louis Tian <louis.tian@aquamonix.com.au>
  3 siblings, 0 replies; 9+ messages in thread

From: Alban Hertroys @ 2023-04-09 09:26 UTC (permalink / raw)
  To: Louis Tian <louis.tian@aquamonix.com.au>; +Cc: Peter Geoghegan <pg@bowt.ie>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>



> On 7 Apr 2023, at 2:49, Louis Tian <louis.tian@aquamonix.com.au> wrote:

(…)

> I am not expecting an error here. The problem is with no conflict it always go down the insert path first and results in a not null constraint error.
> 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 execute without error.
> I hope the pesudo code above is enough to clarify the difference? 

Your assumption on what the problem is, is not correct. The problem is not with the conflict resolution, it is with your statement violating a not null constraint.

It doesn’t matter whether you insert first or update first, either operation is going to violate that constraint. You’re specifying a NULL value for a column that doesn’t accept that because it has a NOT NULL constraint. That is your problem.

Alban Hertroys
--
There is always an exception to always.









^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [EXTERNAL]: Re: UPSERT in Postgres
@ 2023-04-09 09:32  Francisco Olarte <folarte@peoplecall.com>
  parent: Louis Tian <louis.tian@aquamonix.com.au>
  3 siblings, 0 replies; 9+ messages in thread

From: Francisco Olarte @ 2023-04-09 09:32 UTC (permalink / raw)
  To: Louis Tian <louis.tian@aquamonix.com.au>; +Cc: Peter Geoghegan <pg@bowt.ie>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2023-04-09 09:32 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-07 00:49 ` Louis Tian <louis.tian@aquamonix.com.au>
2023-04-08 17:09   ` Israel Brewster <ijbrewster@alaska.edu>
2023-04-08 21:51   ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-04-08 22:05     ` Benedict Holland <benedict.m.holland@gmail.com>
2023-04-09 09:26   ` Alban Hertroys <haramrae@gmail.com>
2023-04-09 09:32   ` Francisco Olarte <folarte@peoplecall.com>
2023-04-07 01:27 ` Louis Tian <louis.tian@aquamonix.com.au>
2023-04-07 20:43   ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-04-07 20:52   ` Adrian Klaver <adrian.klaver@aklaver.com>

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