Re: [(known) BUG] DELETE/UPDATE more than one row in partitioned foreign table

Hi Ajit,

Ajit Awekar <ajitpostgres(at)gmail(dot)com> wrote:
> @Manu: went with your option (1) rather than (2) for case_f
>
> Please find attached V3. Request a review.

Thanks, the new paragraph describes case_f as I saw it, including that
the remote tableoid is the middle foreign table's own OID.

I ran the same eight cases on V3, applied on master e8a3ee5b197, against
that master, both with --enable-cassert. V3 builds without warnings,
and make check (239) and postgres_fdw's check pass.

- Jakub's zero-column case with a BEFORE DELETE trigger no longer fails
the assertion; the output is the same as master's.
- Etsuro's DELETE ... USING case, the dropped and renamed columns with
triggers, the mixed local and foreign partitions and the self-join
give the right rows, as with v2.
- The zero-column RETURNING and the cross-partition cases are the same
as master.
- The chained case still updates the wrong rows, as documented.

Since the commit message mentions the extra column and WHERE clause for
every non-direct UPDATE/DELETE, I measured it, on a loopback server
with 100000 rows, UPDATE ... WHERE id <= N AND random() <= 1, median of 5
runs:

master V3
plain table, 1000 rows 26 ms 29 ms
plain table, 10000 rows 234 ms 224 ms
100 partitions, 1000 rows 42 ms 309 ms
100 partitions, 10000 rows 102 ms 3606 ms

On a plain table I can't see a difference. With partitions V3 looks
much slower, but it is not what it adds. With log_min_duration_statement
= 0, for 1000 rows master sends 100 per-row UPDATEs and V3 sends 1000,
at the same cost each (0.20 ms in one run, 0.25 ms in another, the same
for both). Master sends fewer because each
"WHERE ctid = $1" hits that ctid in every partition, so the rows already
changed are not fetched again. Master is faster only because it is
doing the wrong thing.

What remains is the cost your paragraph describes: each per-row UPDATE
goes through the root and scans every partition, so with 100 partitions
a row costs about ten times what it costs on a plain table (309 ms vs
29 ms for 1000 rows). Prepared on the remote side the way postgres_fdw
does it, "WHERE ctid = $1 AND tableoid = $2" costs about 10% more than
"WHERE ctid = $1" (190 vs 172 ms for 1000 executions), so the tableoid
condition itself is cheap.

The attached fdw_update_cost.sh does both measurements: the medians, and
the statements the remote side receives with their count and time. It
takes an install prefix and a port, like run_cases.sh. The numbers move
a bit between runs, but the counts don't.

I have nothing else to raise on V3.

Regards,
Manu