Re: ON CONFLICT DO SELECT (take 3)

Hi.

<para>
Note that an <literal>INSERT</literal> with an <literal>ON CONFLICT
clause will check the <literal>INSERT</literal> policies'

We need to change it to <literal>ON CONFLICT</literal> otherwise doc
build will fail.

doc/src/sgml/ref/create_policy.sgml
<para>
When an <literal>INSERT</literal> command has an auxiliary
<literal>ON CONFLICT DO UPDATE</literal> clause, if the
<literal>UPDATE</literal> path is taken, the row to be updated is
first checked against the <literal>USING</literal> expressions of
For ON CONFLICT DO SELECT, perhaps we should add some explanatory text
to the preceding paragraph?

doc/src/sgml/ref/create_policy.sgml
<table id="sql-createpolicy-summary">
<title>Policies Applied by Command Type</title>
The change in this section looks correct to me.

doc/src/sgml/ref/insert.sgml
However, <literal>ON CONFLICT DO UPDATE</literal>
also requires <literal>SELECT</literal> privilege on any column whose
values are read in the <literal>ON CONFLICT DO UPDATE</literal>
expressions or <replaceable>condition</replaceable>. If using a
<literal>WHERE</literal> with <literal>ON CONFLICT DO UPDATE /
SELECT</literal>,
you must have <literal>SELECT</literal> privilege on the columns referenced
in the <literal>WHERE</literal> clause.

<replaceable>condition</replaceable> is the same thing as
<literal>WHERE</literal> clause.
so here, you mentioned twice that ON CONFLICT DO UPDATE requires
SELECT privilege
on columns referenced by the WHERE clause.
So, I think the last sentence can just be
"""
If using a WHERE with ON CONFLICT DO SELECT, you must have SELECT privilege on
the columns referenced in the WHERE clause.
"""

ExecProcessReturning comment:
* oldSlot: slot holding old tuple deleted or updated
* newSlot: slot holding new tuple inserted or updated, or existing tuple
* selected (for ON CONFLICT DO SELECT)

"oldSlot" comment also needs slight adjustment.

typedef struct RTEPermissionInfo
comment:
* For SELECT/INSERT/UPDATE permissions, if the user doesn't have table-wide
* permissions then it is sufficient to have the permissions on all columns
* identified in selectedCols (for SELECT) and/or insertedCols and/or
* updatedCols (INSERT with ON CONFLICT DO UPDATE may have all 3).

This applies to ON CONFLICT DO SELECT FOR UPDATE, i think.
Maybe we can update this too.

Overall, these are all the issues I’ve identified. Aside from those
issues, the patch looks very good.

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