diff options
Diffstat (limited to 'contrib/postgres_fdw/expected/postgres_fdw.out')
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 165 |
1 files changed, 142 insertions, 23 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index cf4863c5aa2..8c58720df6f 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -6173,10 +6173,12 @@ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative; -- =================================================================== -- test WITH CHECK OPTION constraints -- =================================================================== +CREATE FUNCTION row_before_insupd_trigfunc() RETURNS trigger AS $$BEGIN NEW.a := NEW.a + 10; RETURN NEW; END$$ LANGUAGE plpgsql; CREATE TABLE base_tbl (a int, b int); ALTER TABLE base_tbl SET (autovacuum_enabled = 'false'); +CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON base_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc(); CREATE FOREIGN TABLE foreign_tbl (a int, b int) - SERVER loopback OPTIONS(table_name 'base_tbl'); + SERVER loopback OPTIONS (table_name 'base_tbl'); CREATE VIEW rw_view AS SELECT * FROM foreign_tbl WHERE a < b WITH CHECK OPTION; \d+ rw_view @@ -6192,45 +6194,162 @@ View definition: WHERE foreign_tbl.a < foreign_tbl.b; Options: check_option=cascaded -INSERT INTO rw_view VALUES (0, 10); -- ok -INSERT INTO rw_view VALUES (10, 0); -- should fail +EXPLAIN (VERBOSE, COSTS OFF) +INSERT INTO rw_view VALUES (0, 5); + QUERY PLAN +-------------------------------------------------------------------------------- + Insert on public.foreign_tbl + Remote SQL: INSERT INTO public.base_tbl(a, b) VALUES ($1, $2) RETURNING a, b + -> Result + Output: 0, 5 +(4 rows) + +INSERT INTO rw_view VALUES (0, 5); -- should fail ERROR: new row violates check option for view "rw_view" -DETAIL: Failing row contains (10, 0). +DETAIL: Failing row contains (10, 5). EXPLAIN (VERBOSE, COSTS OFF) -UPDATE rw_view SET b = 20 WHERE a = 0; -- not pushed down - QUERY PLAN --------------------------------------------------------------------------------------------------- +INSERT INTO rw_view VALUES (0, 15); + QUERY PLAN +-------------------------------------------------------------------------------- + Insert on public.foreign_tbl + Remote SQL: INSERT INTO public.base_tbl(a, b) VALUES ($1, $2) RETURNING a, b + -> Result + Output: 0, 15 +(4 rows) + +INSERT INTO rw_view VALUES (0, 15); -- ok +SELECT * FROM foreign_tbl; + a | b +----+---- + 10 | 15 +(1 row) + +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = b + 5; + QUERY PLAN +--------------------------------------------------------------------------------------- Update on public.foreign_tbl - Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 + Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b -> Foreign Scan on public.foreign_tbl - Output: foreign_tbl.a, 20, foreign_tbl.ctid - Remote SQL: SELECT a, ctid FROM public.base_tbl WHERE ((a < b)) AND ((a = 0)) FOR UPDATE + Output: foreign_tbl.a, (foreign_tbl.b + 5), foreign_tbl.ctid + Remote SQL: SELECT a, b, ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE (5 rows) -UPDATE rw_view SET b = 20 WHERE a = 0; -- ok +UPDATE rw_view SET b = b + 5; -- should fail +ERROR: new row violates check option for view "rw_view" +DETAIL: Failing row contains (20, 20). EXPLAIN (VERBOSE, COSTS OFF) -UPDATE rw_view SET b = -20 WHERE a = 0; -- not pushed down - QUERY PLAN --------------------------------------------------------------------------------------------------- +UPDATE rw_view SET b = b + 15; + QUERY PLAN +--------------------------------------------------------------------------------------- Update on public.foreign_tbl - Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 + Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b -> Foreign Scan on public.foreign_tbl - Output: foreign_tbl.a, '-20'::integer, foreign_tbl.ctid - Remote SQL: SELECT a, ctid FROM public.base_tbl WHERE ((a < b)) AND ((a = 0)) FOR UPDATE + Output: foreign_tbl.a, (foreign_tbl.b + 15), foreign_tbl.ctid + Remote SQL: SELECT a, b, ctid FROM public.base_tbl WHERE ((a < b)) FOR UPDATE (5 rows) -UPDATE rw_view SET b = -20 WHERE a = 0; -- should fail -ERROR: new row violates check option for view "rw_view" -DETAIL: Failing row contains (0, -20). +UPDATE rw_view SET b = b + 15; -- ok SELECT * FROM foreign_tbl; - a | b ----+---- - 0 | 20 + a | b +----+---- + 20 | 30 (1 row) DROP FOREIGN TABLE foreign_tbl CASCADE; NOTICE: drop cascades to view rw_view +DROP TRIGGER row_before_insupd_trigger ON base_tbl; DROP TABLE base_tbl; +-- test WCO for partitions +CREATE TABLE child_tbl (a int, b int); +ALTER TABLE child_tbl SET (autovacuum_enabled = 'false'); +CREATE TRIGGER row_before_insupd_trigger BEFORE INSERT OR UPDATE ON child_tbl FOR EACH ROW EXECUTE PROCEDURE row_before_insupd_trigfunc(); +CREATE FOREIGN TABLE foreign_tbl (a int, b int) + SERVER loopback OPTIONS (table_name 'child_tbl'); +CREATE TABLE parent_tbl (a int, b int) PARTITION BY RANGE(a); +ALTER TABLE parent_tbl ATTACH PARTITION foreign_tbl FOR VALUES FROM (0) TO (100); +CREATE VIEW rw_view AS SELECT * FROM parent_tbl + WHERE a < b WITH CHECK OPTION; +\d+ rw_view + View "public.rw_view" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+---------+------------- + a | integer | | | | plain | + b | integer | | | | plain | +View definition: + SELECT parent_tbl.a, + parent_tbl.b + FROM parent_tbl + WHERE parent_tbl.a < parent_tbl.b; +Options: check_option=cascaded + +EXPLAIN (VERBOSE, COSTS OFF) +INSERT INTO rw_view VALUES (0, 5); + QUERY PLAN +----------------------------- + Insert on public.parent_tbl + -> Result + Output: 0, 5 +(3 rows) + +INSERT INTO rw_view VALUES (0, 5); -- should fail +ERROR: new row violates check option for view "rw_view" +DETAIL: Failing row contains (10, 5). +EXPLAIN (VERBOSE, COSTS OFF) +INSERT INTO rw_view VALUES (0, 15); + QUERY PLAN +----------------------------- + Insert on public.parent_tbl + -> Result + Output: 0, 15 +(3 rows) + +INSERT INTO rw_view VALUES (0, 15); -- ok +SELECT * FROM foreign_tbl; + a | b +----+---- + 10 | 15 +(1 row) + +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = b + 5; + QUERY PLAN +---------------------------------------------------------------------------------------- + Update on public.parent_tbl + Foreign Update on public.foreign_tbl + Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b + -> Foreign Scan on public.foreign_tbl + Output: foreign_tbl.a, (foreign_tbl.b + 5), foreign_tbl.ctid + Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE +(6 rows) + +UPDATE rw_view SET b = b + 5; -- should fail +ERROR: new row violates check option for view "rw_view" +DETAIL: Failing row contains (20, 20). +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE rw_view SET b = b + 15; + QUERY PLAN +---------------------------------------------------------------------------------------- + Update on public.parent_tbl + Foreign Update on public.foreign_tbl + Remote SQL: UPDATE public.child_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b + -> Foreign Scan on public.foreign_tbl + Output: foreign_tbl.a, (foreign_tbl.b + 15), foreign_tbl.ctid + Remote SQL: SELECT a, b, ctid FROM public.child_tbl WHERE ((a < b)) FOR UPDATE +(6 rows) + +UPDATE rw_view SET b = b + 15; -- ok +SELECT * FROM foreign_tbl; + a | b +----+---- + 20 | 30 +(1 row) + +DROP FOREIGN TABLE foreign_tbl CASCADE; +DROP TRIGGER row_before_insupd_trigger ON child_tbl; +DROP TABLE parent_tbl CASCADE; +NOTICE: drop cascades to view rw_view +DROP FUNCTION row_before_insupd_trigfunc; -- =================================================================== -- test serial columns (ie, sequence-based defaults) -- =================================================================== |