INSERT ... ON CONFLICT DO SELECT [FOR ...] take 2

Hi!

This, ON CONFLICT DO SELECT, is a feature I have wished for ever since
ON CONFLICT was added to PostgreSQL and I have worked with several code
bases where it would have been useful. So now I finally got round to
revive and rebase Marko's old patch.

Marko originally posted the patch back in 2017[1], but discussion sadly
died and the patch bitrotted heavily (especially due to pluggable table
AMs).

The patch is very similar to Marko's original but with some fixes like
support for EXPLAIN, clean up, a bug-fix and heavy changes to move it
from PG 11 to master.

This patch adds support both for SELECT with locking of the tuples (at a
lock level the user can specify) and SELECT without any locking. I
personally find both useful. There is no need to take any locks if you
are just fetching the tuples and sending them back to the client and
directly committing the transaction.

Without lock:

INSERT INTO testtab (key, fruit)
VALUES (1, 'Apple')
ON CONFLICT (key) DO SELECT FOR UPDATE
RETURNING *;

With lock:

INSERT INTO testtab (key, fruit)
VALUES (1, 'Apple')
ON CONFLICT (key) DO SELECT
RETURNING *;

What do you think? Is the current propose syntax good? Any other
thoughts? If there is interest I will keep working on this patch.

Remaining work:

- Make sure it works with row level security correctly
- Verify that partitions are supported correctly
- Clean up code
- Clean up tests
- Write more comments
- Improve documentation (e.g. we may need to update documentation for
CREATE POLICY)

References

1.
https://www.postgresql.org/message-id/CAL9smLCdV-v3KgOJX3mU19FYK82N7yzqJj2HAwWX70E%3DP98kgQ%40mail.gmail.com

Andreas