Re: Re: [COMMITTERS] pgsql: Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.

> Andres Freund wrote:
> > Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.
>

Few comments/questions:

1.
insert.sgml
+ column. For example, <literal>INSERT ... ON CONFLICT DO UPDATE
+ tab SET table_name.col = 1</> is invalid (this follows the general
+ behavior for <command>UPDATE</>).

Here in above example shouldn't table_name be used instead of *tab*
after UPDATE?

2.
+ <para>
+ Insert new distributor if possible; otherwise
+ <literal>DO NOTHING</literal>. Example assumes a unique index has been
+ defined that constrains values appearing in the
+ <literal>did</literal> column on a subset of rows where the
+ <literal>is_active</literal> boolean column evaluates to
+ <literal>true</literal>:
+<programlisting>
+ -- This statement could infer a partial unique index on "did"
+ -- with a predicate of "WHERE is_active", but it could also
+ -- just use a regular unique constraint on "did"
+ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
+ ON CONFLICT (did) WHERE is_active DO NOTHING;
+</programlisting>
+ </para>

What does WHERE index_predicate mean for non-partial indexes
or non-expression indexes?

Actually that could cause error even though it is not used
for a unique-index because it would mean that user needs
to have Select privilige on column in used in WHERE clause.

Create table spec_insert(c1 int, c2 int);
Create unique index idx_si on spec_insert(c1);
insert into spec_insert values(1) ON Conflict (c1) where c2 > 2 DO Nothing;

If above insert is executed by user who doesn't have Select privilege
on C2, it will give error.

3.
heap_abort_speculative()
+ /*
+ * Set the tuple header xmin to InvalidTransactionId. This makes the
+ * tuple immediately invisible everyone. (In particular, to any
+ * transactions waiting on the speculative token, woken up later.)

/invisible everyone/invisible to everyone

4.
ExecInsert()
+ * speculatively. See the executor README for a full discussion
+ * of speculative insertion.

I could not find any updates about speculative insertion in executor/README,
am I missing the update?

5.
ExecInsert()
{
..
if (onconflict != ONCONFLICT_NONE && resultRelInfo->ri_NumIndices > 0)
{
..
if (!ExecCheckIndexConstraints(slot, estate, &conflictTid,
arbiterIndexes))
..
specToken = SpeculativeInsertionLockAcquire(GetCurrentTransactionId());
..
}

Here why do we need to perform speculative insertion for the
case when there is no constraint/index that can cause conflict?

For example, below case:
Create table spec_insert(c1 int);
Create index idx_si on spec_insert(c1);
insert into spec_insert values(1) ON Conflict DO Nothing;

6.
ExecInsert()
{
..
if (ExecOnConflictUpdate(mtstate, resultRelInfo,
&conflictTid, planSlot, slot,
estate, canSetTag, &returning))
{
InstrCountFiltered2(&mtstate->ps, 1);
..
}

ExecOnConflictUpdate()
{
..
if (!ExecQual(onConflictSetWhere, econtext, false))
{
ReleaseBuffer(buffer);
InstrCountFiltered1(&mtstate->ps, 1);
..
}

If ExecOnConflictUpdate() returns due to Qual (Qualification
is not satisfied), then it will result in counting both
Filtered1 and Filtered2. I think for such a case only one
of them should be updated, probably Filtered1.

7.
create table t1(c1 int, c2 int);
create unique index idx_t1 on t1(c1);
insert into t1 values(1,1);
postgres=# insert into t1 values(1, 1) On Conflict(c1) Do Update set c1=2
where c2 > 3;
ERROR: column reference "c2" is ambiguous
LINE 1: ...alues(1, 1) On Conflict(c1) Do Update set c1=2 where c2 > 3;

Why alias is required in Where condition whereas it works for Set?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com