Re: ON CONFLICT DO SELECT (take 3)

On Tue, 10 Feb 2026 at 21:59, Viktor Holmberg <v(at)viktorh(dot)net> wrote:
>
> Looking at the first change to create_policy.sgml, it's not quite
> right, but on closer inspection neither was the original text. The
> issue is that in the case of an INSERT ... ON CONFLICT DO NOTHING,
> SELECT policies are only applied if an arbiter clause is present.
> Since that's a pre-existing bug, it should be fixed and back-patched
> separately -- see 0001, attached.
>
> Looking at the paragraph on insert.sgml describing column privileges
> required, I realised that there is another pre-existing bug there --
> it fails to mention privileges required by columns referred to by the
> arbiter index/constraint, and this applies to all forms of ON
> CONFLICT, not just DO UPDATE. Again, I think that should be fixed and
> back-patched separately, which I've done in 0002, in a way intended to
> need no update for ON CONFLICT DO SELECT.
>
> Nice that you found this.

OK, I have pushed and back-patched 0001 and 0002.

> Thanks for sorting all these changes Dean, all look good to me. The only
> thing that gave me pause is this change in analyse.c lines 1238-1247:
>
> So in v.24 we set p_is_insert = false for DO SELECT as well.
> If I’m understanding the code correctly, this is only used when using indirection
> on target columns for updates, like setting a value inside an array, like for example:
> update example set arr[2] = 10;
> So changing this should have any effect, as the DO SELECT doesn’t have any SET clause.
> Logically it makes sense that DO SELECT is p_is_insert = false.
> But maybe the comment should be updated to explain this?

Yes, that's right. I was just trying to reduce code churn. However, if
we need to change the comment to explain why it's OK for DO SELECT,
the code churn argument doesn't work, and it's probably better to only
set it for DO UPDATE. So I've reverted it back to how it was in v23,
with slightly updated comments.

Re-reading the INSERT docs, I still wasn't entirely happy with the
description of the conflict_action parameter, so I had a go at
rewriting it. I also pulled out the description of the locking
options, describing them separately and linking to the description of
the locking clause on the SELECT page.

Regards,
Dean