Re: ON CONFLICT DO SELECT (take 3)

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
Cc: "v(at)viktorh(dot)net" <v(at)viktorh(dot)net>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: ON CONFLICT DO SELECT (take 3)
Date: 2025-11-20 15:26:51
Message-ID: CACJufxGWUMvhgWrfsu-amm8e-C98Chg4mPip+8xOUrerMmNVvg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

>
>
> https://stream.boxwatch.ru/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>&mdash;</entry>
+ <entry>Existing row</entry>
+ <entry>&mdash;</entry>
+ <entry>&mdash;</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/

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Melanie Plageman 2025-11-20 15:27:59 Re: Trying out read streams in pgvector (an extension)
Previous Message Ashutosh Bapat 2025-11-20 15:13:16 Re: SQL Property Graph Queries (SQL/PGQ)