diff options
Diffstat (limited to 'contrib/postgres_fdw/expected')
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 64 |
1 files changed, 53 insertions, 11 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 5f6e1f98332..bd706d18d1b 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -10347,8 +10347,12 @@ SELECT COUNT(*) FROM batch_table; 66 (1 row) --- Check that enabling batched inserts doesn't interfere with cross-partition --- updates +-- Clean up +DROP TABLE batch_table; +DROP TABLE batch_table_p0; +DROP TABLE batch_table_p1; +-- Check that batched mode also works for some inserts made during +-- cross-partition updates CREATE TABLE batch_cp_upd_test (a int) PARTITION BY LIST (a); CREATE TABLE batch_cp_upd_test1 (LIKE batch_cp_upd_test); CREATE FOREIGN TABLE batch_cp_upd_test1_f @@ -10356,21 +10360,59 @@ CREATE FOREIGN TABLE batch_cp_upd_test1_f FOR VALUES IN (1) SERVER loopback OPTIONS (table_name 'batch_cp_upd_test1', batch_size '10'); -CREATE TABLE batch_cp_up_test1 PARTITION OF batch_cp_upd_test +CREATE TABLE batch_cp_upd_test2 PARTITION OF batch_cp_upd_test FOR VALUES IN (2); -INSERT INTO batch_cp_upd_test VALUES (1), (2); --- The following moves a row from the local partition to the foreign one -UPDATE batch_cp_upd_test t SET a = 1 FROM (VALUES (1), (2)) s(a) WHERE t.a = s.a; -ERROR: cannot route tuples into foreign table to be updated "batch_cp_upd_test1_f" -SELECT tableoid::regclass, * FROM batch_cp_upd_test; +CREATE TABLE batch_cp_upd_test3 (LIKE batch_cp_upd_test); +CREATE FOREIGN TABLE batch_cp_upd_test3_f + PARTITION OF batch_cp_upd_test + FOR VALUES IN (3) + SERVER loopback + OPTIONS (table_name 'batch_cp_upd_test3', batch_size '1'); +-- Create statement triggers on remote tables that "log" any INSERTs +-- performed on them. +CREATE TABLE cmdlog (cmd text); +CREATE FUNCTION log_stmt() RETURNS TRIGGER LANGUAGE plpgsql AS $$ + BEGIN INSERT INTO public.cmdlog VALUES (TG_OP || ' on ' || TG_RELNAME); RETURN NULL; END; +$$; +CREATE TRIGGER stmt_trig AFTER INSERT ON batch_cp_upd_test1 + FOR EACH STATEMENT EXECUTE FUNCTION log_stmt(); +CREATE TRIGGER stmt_trig AFTER INSERT ON batch_cp_upd_test3 + FOR EACH STATEMENT EXECUTE FUNCTION log_stmt(); +-- This update moves rows from the local partition 'batch_cp_upd_test2' to the +-- foreign partition 'batch_cp_upd_test1', one that has insert batching +-- enabled, so a single INSERT for both rows. +INSERT INTO batch_cp_upd_test VALUES (2), (2); +UPDATE batch_cp_upd_test t SET a = 1 FROM (VALUES (1), (2)) s(a) WHERE t.a = s.a AND s.a = 2; +-- This one moves rows from the local partition 'batch_cp_upd_test2' to the +-- foreign partition 'batch_cp_upd_test2', one that has insert batching +-- disabled, so separate INSERTs for the two rows. +INSERT INTO batch_cp_upd_test VALUES (2), (2); +UPDATE batch_cp_upd_test t SET a = 3 FROM (VALUES (1), (2)) s(a) WHERE t.a = s.a AND s.a = 2; +SELECT tableoid::regclass, * FROM batch_cp_upd_test ORDER BY 1; tableoid | a ----------------------+--- batch_cp_upd_test1_f | 1 - batch_cp_up_test1 | 2 -(2 rows) + batch_cp_upd_test1_f | 1 + batch_cp_upd_test3_f | 3 + batch_cp_upd_test3_f | 3 +(4 rows) + +-- Should see 1 INSERT on batch_cp_upd_test1 and 2 on batch_cp_upd_test3 as +-- described above. +SELECT * FROM cmdlog ORDER BY 1; + cmd +------------------------------ + INSERT on batch_cp_upd_test1 + INSERT on batch_cp_upd_test3 + INSERT on batch_cp_upd_test3 +(3 rows) -- Clean up -DROP TABLE batch_table, batch_cp_upd_test, batch_table_p0, batch_table_p1 CASCADE; +DROP TABLE batch_cp_upd_test; +DROP TABLE batch_cp_upd_test1; +DROP TABLE batch_cp_upd_test3; +DROP TABLE cmdlog; +DROP FUNCTION log_stmt(); -- Use partitioning ALTER SERVER loopback OPTIONS (ADD batch_size '10'); CREATE TABLE batch_table ( x int, field1 text, field2 text) PARTITION BY HASH (x); |