diff options
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 28 | ||||
| -rw-r--r-- | contrib/postgres_fdw/postgres_fdw.c | 17 | ||||
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 12 |
3 files changed, 53 insertions, 4 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 30e95f585fa..477de09a876 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -10008,6 +10008,34 @@ SELECT COUNT(*) FROM ftable; 2 (1 row) +-- Disable batch inserting into foreign tables with BEFORE ROW INSERT triggers +-- even if the batch_size option is enabled. +ALTER FOREIGN TABLE ftable OPTIONS ( SET batch_size '10' ); +CREATE TRIGGER trig_row_before BEFORE INSERT ON ftable +FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); +EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO ftable VALUES (3), (4); + QUERY PLAN +------------------------------------------------------------- + Insert on public.ftable + Remote SQL: INSERT INTO public.batch_table(x) VALUES ($1) + Batch Size: 1 + -> Values Scan on "*VALUES*" + Output: "*VALUES*".column1 +(5 rows) + +INSERT INTO ftable VALUES (3), (4); +NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON ftable +NOTICE: NEW: (3) +NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON ftable +NOTICE: NEW: (4) +SELECT COUNT(*) FROM ftable; + count +------- + 4 +(1 row) + +-- Clean up +DROP TRIGGER trig_row_before ON ftable; DROP FOREIGN TABLE ftable; DROP TABLE batch_table; -- Use partitioning diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index c51dd687229..0e5771c89d8 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -2012,8 +2012,8 @@ postgresExecForeignBatchInsert(EState *estate, * Determine the maximum number of tuples that can be inserted in bulk * * Returns the batch size specified for server or table. When batching is not - * allowed (e.g. for tables with AFTER ROW triggers or with RETURNING clause), - * returns 1. + * allowed (e.g. for tables with BEFORE/AFTER ROW triggers or with RETURNING + * clause), returns 1. */ static int postgresGetForeignModifyBatchSize(ResultRelInfo *resultRelInfo) @@ -2042,10 +2042,19 @@ postgresGetForeignModifyBatchSize(ResultRelInfo *resultRelInfo) else batch_size = get_batch_size_option(resultRelInfo->ri_RelationDesc); - /* Disable batching when we have to use RETURNING. */ + /* + * Disable batching when we have to use RETURNING or there are any + * BEFORE/AFTER ROW INSERT triggers on the foreign table. + * + * When there are any BEFORE ROW INSERT triggers on the table, we can't + * support it, because such triggers might query the table we're inserting + * into and act differently if the tuples that have already been processed + * and prepared for insertion are not there. + */ if (resultRelInfo->ri_projectReturning != NULL || (resultRelInfo->ri_TrigDesc && - resultRelInfo->ri_TrigDesc->trig_insert_after_row)) + (resultRelInfo->ri_TrigDesc->trig_insert_before_row || + resultRelInfo->ri_TrigDesc->trig_insert_after_row))) return 1; /* diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index ea35e61eb8a..ed181dedff5 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -3135,6 +3135,18 @@ CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batc EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO ftable VALUES (1), (2); INSERT INTO ftable VALUES (1), (2); SELECT COUNT(*) FROM ftable; + +-- Disable batch inserting into foreign tables with BEFORE ROW INSERT triggers +-- even if the batch_size option is enabled. +ALTER FOREIGN TABLE ftable OPTIONS ( SET batch_size '10' ); +CREATE TRIGGER trig_row_before BEFORE INSERT ON ftable +FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo'); +EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO ftable VALUES (3), (4); +INSERT INTO ftable VALUES (3), (4); +SELECT COUNT(*) FROM ftable; + +-- Clean up +DROP TRIGGER trig_row_before ON ftable; DROP FOREIGN TABLE ftable; DROP TABLE batch_table; |