Re: ON CONFLICT DO SELECT (take 3) - Mailing list pgsql-hackers

>
>
> https://www.postgresql.org/docs/current/ddl-priv.html#DDL-PRIV-UPDATE
> says:
> ```
> SELECT ... FOR UPDATE and SELECT ... FOR SHARE also require this privilege on at
> least one column, in addition to the SELECT privilege.
> ```
> I attached extensive permission tests for ON CONFLICT DO SELECT
>
>

+   For <literal>ON CONFLICT DO SELECT</literal>, <literal>SELECT</literal>
+   privilege is required on any column whose values are read in the
+   <replaceable>condition</replaceable>.
If you do <literal>ON CONFLICT DO SELECT FOR UPDATE</literal>
then <literal>UPDATE</literal> permission is also required  (at least
one column),
can we also mention this?


+ /* Parse analysis should already have disallowed this */
+ Assert(resultRelInfo->ri_projectReturning);
+
+ /* Process RETURNING like an UPDATE that didn't change anything */
+ *rslot = ExecProcessReturning(context, resultRelInfo, CMD_UPDATE,
+  existing, existing, context->planSlot);

The above two comments seem confusing. If you look at the code
ExecProcessReturning, I think you can set the cmdType as CMD_INSERT.

+ if (lockstrength != LCS_NONE)
+ {
+ LockTupleMode lockmode;
+
+ switch (lockstrength)
+ {
+ case LCS_FORKEYSHARE:
+ lockmode = LockTupleKeyShare;
+ break;
+ case LCS_FORSHARE:
+ lockmode = LockTupleShare;
+ break;
+ case LCS_FORNOKEYUPDATE:
+ lockmode = LockTupleNoKeyExclusive;
+ break;
+ case LCS_FORUPDATE:
+ lockmode = LockTupleExclusive;
+ break;
+ default:
+ elog(ERROR, "unexpected lock strength %d", lockstrength);
+ }
+
+ if (!ExecOnConflictLockRow(context, existing, conflictTid,
+   resultRelInfo->ri_RelationDesc, lockmode, false))
+ return false;

isolation tests do not test the case where ExecOnConflictLockRow returns false.
actually it's reachable.

-----------------------------setup---------------------
drop table if exists tsa1;
create table tsa1(a int, b int, constraint xxidx unique (a));
insert into tsa1 values (1,2);

session1, using GDB set a breakpoint at ExecOnConflictLockRow.
session1:
              insert into tsa1 values(1,3) on conflict(a) do select
for update returning *;
session2:
             update tsa1 set a = 111;

session1:     session 1 already reached the GDB breakpoint
(ExecOnConflictLockRow), issue
``continue`` in GDB let session1 complete.
----------------------------------------------------------------------------
I'm wondering how we can add test coverage for this.


+      <row>
+       <entry><command>ON CONFLICT DO SELECT FOR UPDATE/SHARE</command></entry>
+       <entry>Check existing rows</entry>
+       <entry>—</entry>
+       <entry>Existing row</entry>
+       <entry>—</entry>
+       <entry>—</entry>
       </row>

Here, it should be "
<entry>Check existing row</entry>
"?

If you search 'ON CONFLICT', it appears on many sgml files, currently we only
made change to:
doc/src/sgml/dml.sgml
doc/src/sgml/ref/create_policy.sgml
doc/src/sgml/ref/insert.sgml

seems other sgml files also need to be updated.


--
jian
https://www.enterprisedb.com/