Horton

Re: Should use MERGE use BulkInsertState ?

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Amit Langote <amitlangote09@gmail.com>, Japin Li <japinli@hotmail.com>, Zhihong Yu <zyu@yugabyte.com>, Simon Riggs <simon.riggs@enterprisedb.com>, pgsql-hackers@lists.postgresql.org, Tomas Vondra <tomas.vondra@enterprisedb.com>, Daniel Westermann <dwe@dbi-services.com>, Erik Rijkers <er@xs4all.nl>, Jaime Casanova <jcasanov@systemguards.com.ec>, Andres Freund <andres@anarazel.de>
Date: 2022-05-11T15:23:27Z
Lists: pgsql-hackers 
On 2022-May-11, Justin Pryzby wrote:

> Should it use it ?
> 
> It occured to me to ask when reading Bruce's release notes, which say:
> | [MERGE] is similar to INSERT ... ON CONFLICT but more batch-oriented.
> 
> Currently, INSERT *never* uses bistate - even INSERT SELECT.
> 
> INSERTing 10k tuples will dirty 10k buffers - not limited to the size of a
> strategy/ring buffer.  Currently, MERGE will do the same.

As I understand it, the point of using a ring is to throttle performance
for bulk operations such as vacuum.  I'm not sure why we would want to
throttle either MERGE or INSERT; it seems to me that we should want them
to go as fast as possible.

If MERGE were to use a ring buffer, why wouldn't UPDATE do the same?
There are some comments to that effect in src/backend/buffer/README --
they do mention UPDATE/DELETE and not INSERT.  It seems to me that these
three commands (MERGE/UPDATE/DELETE) should be handled in similar ways,
so I don't think we need to consider lack of MERGE using a ring buffer
an open item for pg15.

COPY uses a ring buffer too.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

Commits

  1. Doc fixes for MERGE statement

  2. MERGE docs adjustments

  3. Link to MVCC docs in MERGE docs

  4. Fix EXPLAIN MERGE output when no tuples are processed

  5. Make EXPLAIN MERGE output format more compact

  6. Add support for MERGE SQL command

  7. Split ExecUpdate and ExecDelete into reusable pieces

  8. Add API of sorts for transition table handling in trigger.c

  9. Represent Lists as expansible arrays, not chains of cons-cells.