Hi, Another thing I noticed while working on the "Fast COPY FROM based on batch insert" patch is: batch inserts vs. WITH CHECK OPTION constraints from parent views. Here is an example on a normal build producing incorrect results.
CREATE TABLE base_tbl (a int, b int);
CREATE FUNCTION row_before_insert_trigfunc() RETURNS trigger AS
$$BEGIN NEW.a := NEW.a + 10; RETURN NEW; END$$ LANGUAGE plpgsql;
CREATE TRIGGER row_before_insert_trigger BEFORE INSERT ON base_tbl FOR
EACH ROW EXECUTE PROCEDURE row_before_insert_trigfunc();
CREATE FOREIGN TABLE foreign_tbl (a int, b int) SERVER loopback
OPTIONS (table_name 'base_tbl');
CREATE VIEW rw_view AS SELECT * FROM foreign_tbl WHERE a < b WITH CHECK OPTION;
ALTER SERVER loopback OPTIONS (ADD batch_size '10');
EXPLAIN VERBOSE INSERT INTO rw_view VALUES (0, 15), (0, 5);
QUERY PLAN
--------------------------------------------------------------------------------
Insert on public.foreign_tbl (cost=0.00..0.03 rows=0 width=0)
Remote SQL: INSERT INTO public.base_tbl(a, b) VALUES ($1, $2) RETURNING a, b
Batch Size: 10
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=8)
Output: "*VALUES*".column1, "*VALUES*".column2
(5 rows)
INSERT INTO rw_view VALUES (0, 15), (0, 5);
INSERT 0 2
This isn't correct; the INSERT query should abort because the
second-inserted row violates the WCO constraint as it is changed to
(10, 5) by the BEFORE ROW trigger.
Also, the query caused an assertion failure on an assert-enabled build, like:
TRAP: FailedAssertion("*numSlots == 1", File: "postgres_fdw.c", Line:
4164, PID: 7775)
I think the root cause for these is that WCO constraints are enforced
locally, but in batch-insert mode postgres_fdw cannot currently
retrieve the data needed to enforce such constraints locally that was
actually inserted on the remote side (except for the first-inserted
row). And I think this leads to the incorrect results on the normal
build as the WCO constraint is enforced with the data passed from the
core for the second-inserted row, and leads to the assertion failure
on the assert-enabled build.
To fix, I modified postgresGetForeignModifyBatchSize() to disable
batch insert when there are any such constraints, like when there are
any AFTER ROW triggers on the foreign table. Attached is a patch for
that.
If there are no objections, I'll commit the patch.
Best regards,
Etsuro Fujita
postgres_fdw-disable-batching-further.patch
Description: Binary data
