Re: UPSERT and HOT-update

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: "CHENG Yuk-Pong, Daniel " <j16sdiz(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: UPSERT and HOT-update
Date: 2016-03-18 13:41:44
Message-ID: 20160318134144.GZ3127@tamriel.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Daniel,

* CHENG Yuk-Pong, Daniel (j16sdiz(at)gmail(dot)com) wrote:
> I am doing a INSERT...ON CONFLICT...UPDATE.. on a table. The query is
> mostly-UPDATE and does not change any columns most of the time, like
> so:
>
> CREATE INDEX ON book(title);
> INSERT INTO book (isbn, title, author, lastupdate) VALUES ($1,$2,$3, now())
> ON CONFLICT (isbn) DO UPDATE set title=excluded.title,
> author=excluded.author, lastupdate=excluded.lastupdate;
>
> PostgreSQL seems to consider the title as changed and refused to do a
> HOT-update. It works if I remove the `title=...` part.
>
> Are there any tricks to make it smarter? The title don't change most
> of the time after all.

If it's really that infrequent for the title to change, you could do
something like:

insert into book (isbn, title, author, lastupdate) values ('$1','$2',
'$3', now()) on conflict (isbn) do update set author=excluded.author,
lastupdate = excluded.lastupdate where book.title = excluded.title;

and if that doesn't change any rows then the title did change and you
need to run the command you have above.

What might be kind of neat would be to have multiple UPDATE clauses
allowed for the INSERT .. ON CONFLICT DO UPDATE and then you could have
different WHERE clauses and do it all in one command.

Another interesting idea would be a different kind of 'UPDATE SET'
operation (maybe '*=' or something?) which says "only change this if the
value actually changed." There's clearly a lot of cases where that
use-case is desired.

Thanks!

Stephen

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Melvin Davidson 2016-03-18 14:33:53 Re: Partition
Previous Message Aleksander Alekseev 2016-03-18 13:31:10 Re: Drop only temporary table