Marko Tiikkaja wrote:
> I have no idea why it worked in the past, but the patch was never
> designed to work for UPSERT. This has been discussed in the past and
> some people thought that that's not a huge deal.
It takes an excessively large lock when doing UPSERT, which means its
performance under a heavy concurrent load can't be good. The idea is
that if the syntax and general implementation issues can get sorted out,
fixing the locking can be a separate performance improvement to be
implemented later. Using MERGE for UPSERT is the #1 use case for this
feature by a gigantic margin. If that doesn't do what's expected, the
whole implementation doesn't provide the community anything really worth
talking about. That's why I keep hammering on this particular area in
all my testing.
One of the reflexive "I can't switch to PostgreSQL easily" stopping
points for MySQL users is "I can't convert my ON DUPLICATE KEY UPDATE
code". Every other use for MERGE is a helpful side-effect of adding the
implementation in my mind, but not the primary driver of why this is
important. My hints in this direction before didn't get adopted, so I'm
saying it outright now: this patch must have an UPSERT implementation
in its regression tests. And the first thing I'm going to do every time
a new rev comes in is try and break it with the pgbench test I
attached. If Boxuan can start doing that as part of his own testing, I
think development here might start moving forward faster. I don't care
so much about the rate at which concurrent UPSERT-style MERGE happens,
so long as it doesn't crash. But that's where this has been stuck at
for a while now.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books