aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/test/regress/expected/updatable_views.out232
-rw-r--r--src/test/regress/sql/updatable_views.sql4
2 files changed, 102 insertions, 134 deletions
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 83a33772cd6..ea9197ab1d7 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -1998,14 +1998,17 @@ CREATE TABLE t1 (a int, b float, c text);
CREATE INDEX t1_a_idx ON t1(a);
INSERT INTO t1
SELECT i,i,'t1' FROM generate_series(1,10) g(i);
+ANALYZE t1;
CREATE TABLE t11 (d text) INHERITS (t1);
CREATE INDEX t11_a_idx ON t11(a);
INSERT INTO t11
SELECT i,i,'t11','t11d' FROM generate_series(1,10) g(i);
+ANALYZE t11;
CREATE TABLE t12 (e int[]) INHERITS (t1);
CREATE INDEX t12_a_idx ON t12(a);
INSERT INTO t12
SELECT i,i,'t12','{1,2}'::int[] FROM generate_series(1,10) g(i);
+ANALYZE t12;
CREATE TABLE t111 () INHERITS (t11, t12);
NOTICE: merging multiple inherited definitions of column "a"
NOTICE: merging multiple inherited definitions of column "b"
@@ -2013,6 +2016,7 @@ NOTICE: merging multiple inherited definitions of column "c"
CREATE INDEX t111_a_idx ON t111(a);
INSERT INTO t111
SELECT i,i,'t111','t111d','{1,1,1}'::int[] FROM generate_series(1,10) g(i);
+ANALYZE t111;
CREATE VIEW v1 WITH (security_barrier=true) AS
SELECT *, (SELECT d FROM t11 WHERE t11.a = t1.a LIMIT 1) AS d
FROM t1
@@ -2033,90 +2037,70 @@ SELECT * FROM v1 WHERE a=8;
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a = 3;
- QUERY PLAN
--------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
Update on public.t1 t1_4
-> Subquery Scan on t1
Output: 100, t1.b, t1.c, t1.ctid
Filter: snoop(t1.a)
- -> Hash Join
+ -> Nested Loop Semi Join
Output: t1_5.ctid, t1_5.a, t1_5.b, t1_5.c
- Hash Cond: (t12.a = t1_5.a)
- -> HashAggregate
- Output: t12.a
- Group Key: t12.a
- -> Append
- -> Seq Scan on public.t12
- Output: t12.a
- -> Seq Scan on public.t111
- Output: t111.a
- -> Hash
+ -> Seq Scan on public.t1 t1_5
Output: t1_5.ctid, t1_5.a, t1_5.b, t1_5.c
- -> Index Scan using t1_a_idx on public.t1 t1_5
- Output: t1_5.ctid, t1_5.a, t1_5.b, t1_5.c
- Index Cond: ((t1_5.a > 5) AND (t1_5.a = 3))
- Filter: leakproof(t1_5.a)
+ Filter: ((t1_5.a > 5) AND (t1_5.a = 3) AND leakproof(t1_5.a))
+ -> Append
+ -> Seq Scan on public.t12
+ Output: t12.a
+ Filter: (t1_5.a = t12.a)
+ -> Seq Scan on public.t111
+ Output: t111.a
+ Filter: (t1_5.a = t111.a)
-> Subquery Scan on t1_1
Output: 100, t1_1.b, t1_1.c, t1_1.d, t1_1.ctid
Filter: snoop(t1_1.a)
- -> Hash Join
+ -> Nested Loop Semi Join
Output: t11.ctid, t11.a, t11.b, t11.c, t11.d
- Hash Cond: (t12_1.a = t11.a)
- -> HashAggregate
- Output: t12_1.a
- Group Key: t12_1.a
- -> Append
- -> Seq Scan on public.t12 t12_1
- Output: t12_1.a
- -> Seq Scan on public.t111 t111_1
- Output: t111_1.a
- -> Hash
+ -> Seq Scan on public.t11
Output: t11.ctid, t11.a, t11.b, t11.c, t11.d
- -> Index Scan using t11_a_idx on public.t11
- Output: t11.ctid, t11.a, t11.b, t11.c, t11.d
- Index Cond: ((t11.a > 5) AND (t11.a = 3))
- Filter: leakproof(t11.a)
+ Filter: ((t11.a > 5) AND (t11.a = 3) AND leakproof(t11.a))
+ -> Append
+ -> Seq Scan on public.t12 t12_1
+ Output: t12_1.a
+ Filter: (t11.a = t12_1.a)
+ -> Seq Scan on public.t111 t111_1
+ Output: t111_1.a
+ Filter: (t11.a = t111_1.a)
-> Subquery Scan on t1_2
Output: 100, t1_2.b, t1_2.c, t1_2.e, t1_2.ctid
Filter: snoop(t1_2.a)
- -> Hash Join
+ -> Nested Loop Semi Join
Output: t12_2.ctid, t12_2.a, t12_2.b, t12_2.c, t12_2.e
- Hash Cond: (t12_3.a = t12_2.a)
- -> HashAggregate
- Output: t12_3.a
- Group Key: t12_3.a
- -> Append
- -> Seq Scan on public.t12 t12_3
- Output: t12_3.a
- -> Seq Scan on public.t111 t111_2
- Output: t111_2.a
- -> Hash
+ -> Seq Scan on public.t12 t12_2
Output: t12_2.ctid, t12_2.a, t12_2.b, t12_2.c, t12_2.e
- -> Index Scan using t12_a_idx on public.t12 t12_2
- Output: t12_2.ctid, t12_2.a, t12_2.b, t12_2.c, t12_2.e
- Index Cond: ((t12_2.a > 5) AND (t12_2.a = 3))
- Filter: leakproof(t12_2.a)
+ Filter: ((t12_2.a > 5) AND (t12_2.a = 3) AND leakproof(t12_2.a))
+ -> Append
+ -> Seq Scan on public.t12 t12_3
+ Output: t12_3.a
+ Filter: (t12_2.a = t12_3.a)
+ -> Seq Scan on public.t111 t111_2
+ Output: t111_2.a
+ Filter: (t12_2.a = t111_2.a)
-> Subquery Scan on t1_3
Output: 100, t1_3.b, t1_3.c, t1_3.d, t1_3.e, t1_3.ctid
Filter: snoop(t1_3.a)
- -> Hash Join
+ -> Nested Loop Semi Join
Output: t111_3.ctid, t111_3.a, t111_3.b, t111_3.c, t111_3.d, t111_3.e
- Hash Cond: (t12_4.a = t111_3.a)
- -> HashAggregate
- Output: t12_4.a
- Group Key: t12_4.a
- -> Append
- -> Seq Scan on public.t12 t12_4
- Output: t12_4.a
- -> Seq Scan on public.t111 t111_4
- Output: t111_4.a
- -> Hash
+ -> Seq Scan on public.t111 t111_3
Output: t111_3.ctid, t111_3.a, t111_3.b, t111_3.c, t111_3.d, t111_3.e
- -> Index Scan using t111_a_idx on public.t111 t111_3
- Output: t111_3.ctid, t111_3.a, t111_3.b, t111_3.c, t111_3.d, t111_3.e
- Index Cond: ((t111_3.a > 5) AND (t111_3.a = 3))
- Filter: leakproof(t111_3.a)
-(81 rows)
+ Filter: ((t111_3.a > 5) AND (t111_3.a = 3) AND leakproof(t111_3.a))
+ -> Append
+ -> Seq Scan on public.t12 t12_4
+ Output: t12_4.a
+ Filter: (t111_3.a = t12_4.a)
+ -> Seq Scan on public.t111 t111_4
+ Output: t111_4.a
+ Filter: (t111_3.a = t111_4.a)
+(61 rows)
UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a = 3;
SELECT * FROM v1 WHERE a=100; -- Nothing should have been changed to 100
@@ -2131,90 +2115,70 @@ SELECT * FROM t1 WHERE a=100; -- Nothing should have been changed to 100
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
- QUERY PLAN
--------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
Update on public.t1 t1_4
-> Subquery Scan on t1
Output: (t1.a + 1), t1.b, t1.c, t1.ctid
Filter: snoop(t1.a)
- -> Hash Join
+ -> Nested Loop Semi Join
Output: t1_5.a, t1_5.ctid, t1_5.b, t1_5.c
- Hash Cond: (t12.a = t1_5.a)
- -> HashAggregate
- Output: t12.a
- Group Key: t12.a
- -> Append
- -> Seq Scan on public.t12
- Output: t12.a
- -> Seq Scan on public.t111
- Output: t111.a
- -> Hash
+ -> Seq Scan on public.t1 t1_5
Output: t1_5.a, t1_5.ctid, t1_5.b, t1_5.c
- -> Index Scan using t1_a_idx on public.t1 t1_5
- Output: t1_5.a, t1_5.ctid, t1_5.b, t1_5.c
- Index Cond: ((t1_5.a > 5) AND (t1_5.a = 8))
- Filter: leakproof(t1_5.a)
+ Filter: ((t1_5.a > 5) AND (t1_5.a = 8) AND leakproof(t1_5.a))
+ -> Append
+ -> Seq Scan on public.t12
+ Output: t12.a
+ Filter: (t1_5.a = t12.a)
+ -> Seq Scan on public.t111
+ Output: t111.a
+ Filter: (t1_5.a = t111.a)
-> Subquery Scan on t1_1
Output: (t1_1.a + 1), t1_1.b, t1_1.c, t1_1.d, t1_1.ctid
Filter: snoop(t1_1.a)
- -> Hash Join
+ -> Nested Loop Semi Join
Output: t11.a, t11.ctid, t11.b, t11.c, t11.d
- Hash Cond: (t12_1.a = t11.a)
- -> HashAggregate
- Output: t12_1.a
- Group Key: t12_1.a
- -> Append
- -> Seq Scan on public.t12 t12_1
- Output: t12_1.a
- -> Seq Scan on public.t111 t111_1
- Output: t111_1.a
- -> Hash
+ -> Seq Scan on public.t11
Output: t11.a, t11.ctid, t11.b, t11.c, t11.d
- -> Index Scan using t11_a_idx on public.t11
- Output: t11.a, t11.ctid, t11.b, t11.c, t11.d
- Index Cond: ((t11.a > 5) AND (t11.a = 8))
- Filter: leakproof(t11.a)
+ Filter: ((t11.a > 5) AND (t11.a = 8) AND leakproof(t11.a))
+ -> Append
+ -> Seq Scan on public.t12 t12_1
+ Output: t12_1.a
+ Filter: (t11.a = t12_1.a)
+ -> Seq Scan on public.t111 t111_1
+ Output: t111_1.a
+ Filter: (t11.a = t111_1.a)
-> Subquery Scan on t1_2
Output: (t1_2.a + 1), t1_2.b, t1_2.c, t1_2.e, t1_2.ctid
Filter: snoop(t1_2.a)
- -> Hash Join
+ -> Nested Loop Semi Join
Output: t12_2.a, t12_2.ctid, t12_2.b, t12_2.c, t12_2.e
- Hash Cond: (t12_3.a = t12_2.a)
- -> HashAggregate
- Output: t12_3.a
- Group Key: t12_3.a
- -> Append
- -> Seq Scan on public.t12 t12_3
- Output: t12_3.a
- -> Seq Scan on public.t111 t111_2
- Output: t111_2.a
- -> Hash
+ -> Seq Scan on public.t12 t12_2
Output: t12_2.a, t12_2.ctid, t12_2.b, t12_2.c, t12_2.e
- -> Index Scan using t12_a_idx on public.t12 t12_2
- Output: t12_2.a, t12_2.ctid, t12_2.b, t12_2.c, t12_2.e
- Index Cond: ((t12_2.a > 5) AND (t12_2.a = 8))
- Filter: leakproof(t12_2.a)
+ Filter: ((t12_2.a > 5) AND (t12_2.a = 8) AND leakproof(t12_2.a))
+ -> Append
+ -> Seq Scan on public.t12 t12_3
+ Output: t12_3.a
+ Filter: (t12_2.a = t12_3.a)
+ -> Seq Scan on public.t111 t111_2
+ Output: t111_2.a
+ Filter: (t12_2.a = t111_2.a)
-> Subquery Scan on t1_3
Output: (t1_3.a + 1), t1_3.b, t1_3.c, t1_3.d, t1_3.e, t1_3.ctid
Filter: snoop(t1_3.a)
- -> Hash Join
+ -> Nested Loop Semi Join
Output: t111_3.a, t111_3.ctid, t111_3.b, t111_3.c, t111_3.d, t111_3.e
- Hash Cond: (t12_4.a = t111_3.a)
- -> HashAggregate
- Output: t12_4.a
- Group Key: t12_4.a
- -> Append
- -> Seq Scan on public.t12 t12_4
- Output: t12_4.a
- -> Seq Scan on public.t111 t111_4
- Output: t111_4.a
- -> Hash
+ -> Seq Scan on public.t111 t111_3
Output: t111_3.a, t111_3.ctid, t111_3.b, t111_3.c, t111_3.d, t111_3.e
- -> Index Scan using t111_a_idx on public.t111 t111_3
- Output: t111_3.a, t111_3.ctid, t111_3.b, t111_3.c, t111_3.d, t111_3.e
- Index Cond: ((t111_3.a > 5) AND (t111_3.a = 8))
- Filter: leakproof(t111_3.a)
-(81 rows)
+ Filter: ((t111_3.a > 5) AND (t111_3.a = 8) AND leakproof(t111_3.a))
+ -> Append
+ -> Seq Scan on public.t12 t12_4
+ Output: t12_4.a
+ Filter: (t111_3.a = t12_4.a)
+ -> Seq Scan on public.t111 t111_4
+ Output: t111_4.a
+ Filter: (t111_3.a = t111_4.a)
+(61 rows)
UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
NOTICE: snooped value: 8
@@ -2224,28 +2188,28 @@ NOTICE: snooped value: 8
SELECT * FROM v1 WHERE b=8;
a | b | c | d
---+---+------+------
- 9 | 8 | t111 | t11d
- 9 | 8 | t12 | t11d
- 9 | 8 | t11 | t11d
9 | 8 | t1 | t11d
+ 9 | 8 | t11 | t11d
+ 9 | 8 | t12 | t11d
+ 9 | 8 | t111 | t11d
(4 rows)
DELETE FROM v1 WHERE snoop(a) AND leakproof(a); -- should not delete everything, just where a>5
-NOTICE: snooped value: 10
-NOTICE: snooped value: 9
-NOTICE: snooped value: 9
NOTICE: snooped value: 6
NOTICE: snooped value: 7
-NOTICE: snooped value: 10
NOTICE: snooped value: 9
+NOTICE: snooped value: 10
NOTICE: snooped value: 9
NOTICE: snooped value: 6
NOTICE: snooped value: 7
-NOTICE: snooped value: 10
NOTICE: snooped value: 9
+NOTICE: snooped value: 10
NOTICE: snooped value: 9
NOTICE: snooped value: 6
NOTICE: snooped value: 7
+NOTICE: snooped value: 9
+NOTICE: snooped value: 10
+NOTICE: snooped value: 9
NOTICE: snooped value: 6
NOTICE: snooped value: 7
NOTICE: snooped value: 9
diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql
index eb7b17979ed..c072fca6be2 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -944,21 +944,25 @@ CREATE TABLE t1 (a int, b float, c text);
CREATE INDEX t1_a_idx ON t1(a);
INSERT INTO t1
SELECT i,i,'t1' FROM generate_series(1,10) g(i);
+ANALYZE t1;
CREATE TABLE t11 (d text) INHERITS (t1);
CREATE INDEX t11_a_idx ON t11(a);
INSERT INTO t11
SELECT i,i,'t11','t11d' FROM generate_series(1,10) g(i);
+ANALYZE t11;
CREATE TABLE t12 (e int[]) INHERITS (t1);
CREATE INDEX t12_a_idx ON t12(a);
INSERT INTO t12
SELECT i,i,'t12','{1,2}'::int[] FROM generate_series(1,10) g(i);
+ANALYZE t12;
CREATE TABLE t111 () INHERITS (t11, t12);
CREATE INDEX t111_a_idx ON t111(a);
INSERT INTO t111
SELECT i,i,'t111','t111d','{1,1,1}'::int[] FROM generate_series(1,10) g(i);
+ANALYZE t111;
CREATE VIEW v1 WITH (security_barrier=true) AS
SELECT *, (SELECT d FROM t11 WHERE t11.a = t1.a LIMIT 1) AS d
generated by cgit v1.2.3 (git 2.25.1) at 2025-08-06 18:35:36 +0000