public inbox for [email protected]
help / color / mirror / Atom feedFrom: Simon Riggs <[email protected]>
To: Alvaro Herrera <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Pg Hackers <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Zhihong Yu <[email protected]>
Cc: Daniel Westermann <[email protected]>
Cc: Amit Langote <[email protected]>
Cc: Justin Pryzby <[email protected]>
Cc: Japin Li <[email protected]>
Cc: Erik Rijkers <[email protected]>
Cc: Jaime Casanova <[email protected]>
Subject: Re: support for MERGE
Date: Thu, 10 Mar 2022 15:48:54 +0000
Message-ID: <CANbhV-HgNB805QSikNTVSeFOV_jpfGokQpbboQjHntzo9auXRw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
On Sun, 27 Feb 2022 at 17:35, Alvaro Herrera <[email protected]> wrote:
> > > + /*
> > > + * Project the tuple. In case of a partitioned table, the
> > > + * projection was already built to use the root's descriptor,
> > > + * so we don't need to map the tuple here.
> > > + */
> > > + actionInfo.actionState = action;
> > > + insert_slot = ExecProject(action->mas_proj);
> > > +
> > > + (void) ExecInsert(mtstate, rootRelInfo,
> > > + insert_slot, slot,
> > > + estate, &actionInfo,
> > > + mtstate->canSetTag);
> > > + InstrCountFiltered1(&mtstate->ps, 1);
> >
> > What happens if somebody concurrently inserts a conflicting row?
>
> An open question to which I don't have any good answer RN.
Duplicate rows should produce a uniqueness violation error in one of
the transactions, assuming there is a constraint to define the
conflict. Without such a constraint there is no conflict.
Concurrent inserts are checked by merge-insert-update.spec, which
correctly raises an ERROR in this case, as documented.
Various cases were not tested in the patch - additional patch
attached, but nothing surprising there.
ExecInsert() does not return from such an ERROR, so the code fragment
appears correct to me.
--
Simon Riggs http://www.EnterpriseDB.com/
Attachments:
[application/octet-stream] new_insert_tests_for_merge.patch (1.0K, ../CANbhV-HgNB805QSikNTVSeFOV_jpfGokQpbboQjHntzo9auXRw@mail.gmail.com/2-new_insert_tests_for_merge.patch)
download | inline diff:
298c298
< -- correct source data
---
> -- remove duplicate MATCHED data from source data
303a304,320
> -- duplicate source row on INSERT should fail because of target_pkey
> INSERT INTO source VALUES (4, 40);
> BEGIN;
> MERGE INTO target t
> USING source AS s
> ON t.tid = s.sid
> WHEN NOT MATCHED THEN
> INSERT VALUES (4, NULL);
> SELECT * FROM target ORDER BY tid;
> ROLLBACK;
>
> -- remove duplicate NOT MATCHED data from source data
> DELETE FROM source WHERE sid = 4;
> INSERT INTO source VALUES (4, 40);
> SELECT * FROM source ORDER BY sid;
> SELECT * FROM target ORDER BY tid;
>
352a370,386
> -- and again with duplicate source rows
> INSERT INTO source VALUES (5, 50);
> INSERT INTO source VALUES (5, 50);
>
> -- do a simple equivalent of an INSERT SELECT
> BEGIN;
> MERGE INTO target t
> USING source AS s
> ON t.tid = s.sid
> WHEN NOT MATCHED THEN
> INSERT VALUES (s.sid, s.delta);
> SELECT * FROM target ORDER BY tid;
> ROLLBACK;
>
> -- removing duplicate source rows
> DELETE FROM source WHERE sid = 5;
>
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: support for MERGE
In-Reply-To: <CANbhV-HgNB805QSikNTVSeFOV_jpfGokQpbboQjHntzo9auXRw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox