diff options
| author | Pavan Deolasee | 2015-06-10 08:09:06 +0000 |
|---|---|---|
| committer | Pavan Deolasee | 2015-06-10 08:09:06 +0000 |
| commit | e96a4914d80f85005934226c88982bea4f269b1a (patch) | |
| tree | 0ca01fb3d4a49f5fe333195b674cf5d98f653f16 | |
| parent | 8efad103cc34db497b7371c5b232ccfb66930a57 (diff) | |
Fix a few expected output diffs from test case inherit
Also removed alternate expected output file for the test case
| -rw-r--r-- | src/test/regress/expected/inherit.out | 836 | ||||
| -rw-r--r-- | src/test/regress/expected/inherit_1.out | 1488 |
2 files changed, 286 insertions, 2038 deletions
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 6d877de98c..101d28a3db 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -32,122 +32,44 @@ INSERT INTO d(aa) VALUES('dddddd'); INSERT INTO d(aa) VALUES('ddddddd'); INSERT INTO d(aa) VALUES('dddddddd'); SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+---------- - a | aaa - a | aaaa - a | aaaaa - a | aaaaaa - a | aaaaaaa - a | aaaaaaaa - b | bbb - b | bbbb - b | bbbbb - b | bbbbbb - b | bbbbbbb - b | bbbbbbbb - c | ccc - c | cccc - c | ccccc - c | cccccc - c | ccccccc - c | cccccccc - d | ddd - d | dddd - d | ddddd - d | dddddd - d | ddddddd - d | dddddddd -(24 rows) + relname | aa +---------+---- +(0 rows) SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+----------+---- - b | bbb | - b | bbbb | - b | bbbbb | - b | bbbbbb | - b | bbbbbbb | - b | bbbbbbbb | - d | ddd | - d | dddd | - d | ddddd | - d | dddddd | - d | ddddddd | - d | dddddddd | -(12 rows) + relname | aa | bb +---------+----+---- +(0 rows) SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----------+---- - c | ccc | - c | cccc | - c | ccccc | - c | cccccc | - c | ccccccc | - c | cccccccc | - d | ddd | - d | dddd | - d | ddddd | - d | dddddd | - d | ddddddd | - d | dddddddd | -(12 rows) + relname | aa | cc +---------+----+---- +(0 rows) SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+----------+----+----+---- - d | ddd | | | - d | dddd | | | - d | ddddd | | | - d | dddddd | | | - d | ddddddd | | | - d | dddddddd | | | -(6 rows) + relname | aa | bb | cc | dd +---------+----+----+----+---- +(0 rows) SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+---------- - a | aaa - a | aaaa - a | aaaaa - a | aaaaaa - a | aaaaaaa - a | aaaaaaaa -(6 rows) + relname | aa +---------+---- +(0 rows) SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+----------+---- - b | bbb | - b | bbbb | - b | bbbbb | - b | bbbbbb | - b | bbbbbbb | - b | bbbbbbbb | -(6 rows) + relname | aa | bb +---------+----+---- +(0 rows) SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----------+---- - c | ccc | - c | cccc | - c | ccccc | - c | cccccc | - c | ccccccc | - c | cccccccc | -(6 rows) + relname | aa | cc +---------+----+---- +(0 rows) SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+----------+----+----+---- - d | ddd | | | - d | dddd | | | - d | ddddd | | | - d | dddddd | | | - d | ddddddd | | | - d | dddddddd | | | -(6 rows) + relname | aa | bb | cc | dd +---------+----+----+----+---- +(0 rows) -- In Postgres-XL OIDs are not consistent across the cluster. Hence above -- queries do not show any result. Hence in order to ensure data consistency, we @@ -277,122 +199,44 @@ UPDATE b SET aa='zzz' WHERE aa='aaa'; UPDATE ONLY b SET aa='zzz' WHERE aa='aaa'; UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%'; SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+---------- - a | zzzz - a | zzzzz - a | zzzzzz - a | zzzzzz - a | zzzzzz - a | zzzzzz - b | bbb - b | bbbb - b | bbbbb - b | bbbbbb - b | bbbbbbb - b | bbbbbbbb - c | ccc - c | cccc - c | ccccc - c | cccccc - c | ccccccc - c | cccccccc - d | ddd - d | dddd - d | ddddd - d | dddddd - d | ddddddd - d | dddddddd -(24 rows) + relname | aa +---------+---- +(0 rows) SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+----------+---- - b | bbb | - b | bbbb | - b | bbbbb | - b | bbbbbb | - b | bbbbbbb | - b | bbbbbbbb | - d | ddd | - d | dddd | - d | ddddd | - d | dddddd | - d | ddddddd | - d | dddddddd | -(12 rows) + relname | aa | bb +---------+----+---- +(0 rows) SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----------+---- - c | ccc | - c | cccc | - c | ccccc | - c | cccccc | - c | ccccccc | - c | cccccccc | - d | ddd | - d | dddd | - d | ddddd | - d | dddddd | - d | ddddddd | - d | dddddddd | -(12 rows) + relname | aa | cc +---------+----+---- +(0 rows) SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+----------+----+----+---- - d | ddd | | | - d | dddd | | | - d | ddddd | | | - d | dddddd | | | - d | ddddddd | | | - d | dddddddd | | | -(6 rows) + relname | aa | bb | cc | dd +---------+----+----+----+---- +(0 rows) SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+-------- - a | zzzz - a | zzzzz - a | zzzzzz - a | zzzzzz - a | zzzzzz - a | zzzzzz -(6 rows) + relname | aa +---------+---- +(0 rows) SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+----------+---- - b | bbb | - b | bbbb | - b | bbbbb | - b | bbbbbb | - b | bbbbbbb | - b | bbbbbbbb | -(6 rows) + relname | aa | bb +---------+----+---- +(0 rows) SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----------+---- - c | ccc | - c | cccc | - c | ccccc | - c | cccccc | - c | ccccccc | - c | cccccccc | -(6 rows) + relname | aa | cc +---------+----+---- +(0 rows) SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+----------+----+----+---- - d | ddd | | | - d | dddd | | | - d | ddddd | | | - d | dddddd | | | - d | ddddddd | | | - d | dddddddd | | | -(6 rows) + relname | aa | bb | cc | dd +---------+----+----+----+---- +(0 rows) -- In Postgres-XL OIDs are not consistent across the cluster. Hence above -- queries do not show any result. Hence in order to ensure data consistency, we @@ -518,122 +362,44 @@ SELECT * from ONLY d ORDER BY d.aa; UPDATE b SET aa='new'; SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+---------- - a | zzzz - a | zzzzz - a | zzzzzz - a | zzzzzz - a | zzzzzz - a | zzzzzz - b | new - b | new - b | new - b | new - b | new - b | new - c | ccc - c | cccc - c | ccccc - c | cccccc - c | ccccccc - c | cccccccc - d | new - d | new - d | new - d | new - d | new - d | new -(24 rows) + relname | aa +---------+---- +(0 rows) SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+-----+---- - b | new | - b | new | - b | new | - b | new | - b | new | - b | new | - d | new | - d | new | - d | new | - d | new | - d | new | - d | new | -(12 rows) + relname | aa | bb +---------+----+---- +(0 rows) SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----------+---- - c | ccc | - c | cccc | - c | ccccc | - c | cccccc | - c | ccccccc | - c | cccccccc | - d | new | - d | new | - d | new | - d | new | - d | new | - d | new | -(12 rows) + relname | aa | cc +---------+----+---- +(0 rows) SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+-----+----+----+---- - d | new | | | - d | new | | | - d | new | | | - d | new | | | - d | new | | | - d | new | | | -(6 rows) + relname | aa | bb | cc | dd +---------+----+----+----+---- +(0 rows) SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+-------- - a | zzzz - a | zzzzz - a | zzzzzz - a | zzzzzz - a | zzzzzz - a | zzzzzz -(6 rows) + relname | aa +---------+---- +(0 rows) SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+-----+---- - b | new | - b | new | - b | new | - b | new | - b | new | - b | new | -(6 rows) + relname | aa | bb +---------+----+---- +(0 rows) SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----------+---- - c | ccc | - c | cccc | - c | ccccc | - c | cccccc | - c | ccccccc | - c | cccccccc | -(6 rows) + relname | aa | cc +---------+----+---- +(0 rows) SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+-----+----+----+---- - d | new | | | - d | new | | | - d | new | | | - d | new | | | - d | new | | | - d | new | | | -(6 rows) + relname | aa | bb | cc | dd +---------+----+----+----+---- +(0 rows) -- In Postgres-XL OIDs are not consistent across the cluster. Hence above -- queries do not show any result. Hence in order to ensure data consistency, we @@ -760,88 +526,34 @@ SELECT * from ONLY d ORDER BY d.aa; UPDATE a SET aa='new'; DELETE FROM ONLY c WHERE aa='new'; SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+----- - a | new - a | new - a | new - a | new - a | new - a | new - b | new - b | new - b | new - b | new - b | new - b | new - d | new - d | new - d | new - d | new - d | new - d | new -(18 rows) + relname | aa +---------+---- +(0 rows) SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+-----+---- - b | new | - b | new | - b | new | - b | new | - b | new | - b | new | - d | new | - d | new | - d | new | - d | new | - d | new | - d | new | -(12 rows) + relname | aa | bb +---------+----+---- +(0 rows) SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+-----+---- - d | new | - d | new | - d | new | - d | new | - d | new | - d | new | -(6 rows) + relname | aa | cc +---------+----+---- +(0 rows) SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+-----+----+----+---- - d | new | | | - d | new | | | - d | new | | | - d | new | | | - d | new | | | - d | new | | | -(6 rows) + relname | aa | bb | cc | dd +---------+----+----+----+---- +(0 rows) SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+----- - a | new - a | new - a | new - a | new - a | new - a | new -(6 rows) + relname | aa +---------+---- +(0 rows) SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+-----+---- - b | new | - b | new | - b | new | - b | new | - b | new | - b | new | -(6 rows) + relname | aa | bb +---------+----+---- +(0 rows) SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; relname | aa | cc @@ -849,15 +561,9 @@ SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER (0 rows) SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+-----+----+----+---- - d | new | | | - d | new | | | - d | new | | | - d | new | | | - d | new | | | - d | new | | | -(6 rows) + relname | aa | bb | cc | dd +---------+----+----+----+---- +(0 rows) -- In Postgres-XL OIDs are not consistent across the cluster. Hence above -- queries do not show any result. Hence in order to ensure data consistency, we @@ -1071,18 +777,11 @@ insert into bar2 values(2,2,2); insert into bar2 values(3,3,3); insert into bar2 values(4,4,4); update bar set f2 = f2 + 100 where f1 in (select f1 from foo); -select tableoid::regclass::text as relname, bar.* from bar order by 1,2; - relname | f1 | f2 ----------+----+----- - bar | 1 | 101 - bar | 2 | 102 - bar | 3 | 103 - bar | 4 | 4 - bar2 | 1 | 101 - bar2 | 2 | 102 - bar2 | 3 | 103 - bar2 | 4 | 4 -(8 rows) +SELECT relname, bar.* FROM bar, pg_class where bar.tableoid = pg_class.oid +order by 1,2; + relname | f1 | f2 +---------+----+---- +(0 rows) -- In Postgres-XL OIDs are not consistent across the cluster. Hence above -- queries do not show any result. Hence in order to ensure data consistency, we @@ -1119,24 +818,6 @@ SELECT * FROM bar2 ORDER BY f1, f2; 4 | 4 | 4 (4 rows) --- Check UPDATE with inherited target and an appendrel subquery -update bar set f2 = f2 + 100 -from - ( select f1 from foo union all select f1+3 from foo ) ss -where bar.f1 = ss.f1; -select tableoid::regclass::text as relname, bar.* from bar order by 1,2; - relname | f1 | f2 ----------+----+----- - bar | 1 | 201 - bar | 2 | 202 - bar | 3 | 203 - bar | 4 | 104 - bar2 | 1 | 201 - bar2 | 2 | 202 - bar2 | 3 | 203 - bar2 | 4 | 104 -(8 rows) - /* Test multiple inheritance of column defaults */ CREATE TABLE firstparent (tomorrow date default now()::date + 1); CREATE TABLE secondparent (tomorrow date default now() :: date + 1); @@ -1516,22 +1197,7 @@ SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected WHERE inhparent IN (SELECT inhrelid FROM r) GROUP BY inhrelid) e JOIN pg_attribute a ON e.inhrelid = a.attrelid WHERE NOT attislocal ORDER BY a.attrelid::regclass::name, a.attnum; - attrelid | attname | attinhcount | expected -----------+---------+-------------+---------- - inht2 | aaaa | 1 | 1 - inht2 | b | 1 | 1 - inht3 | aaaa | 1 | 1 - inht3 | b | 1 | 1 - inht4 | aaaa | 2 | 2 - inht4 | b | 2 | 2 - inht4 | x | 1 | 2 - inht4 | y | 1 | 2 - inhts | aaaa | 1 | 1 - inhts | b | 2 | 1 - inhts | x | 1 | 1 - inhts | c | 1 | 1 -(12 rows) - +ERROR: WITH RECURSIVE currently not supported on distributed tables. DROP TABLE inht1, inhs1 CASCADE; NOTICE: drop cascades to 4 other objects DETAIL: drop cascades to table inht2 @@ -1551,6 +1217,8 @@ CREATE TABLE test_constraints_inh () INHERITS (test_constraints); Indexes: "test_constraints_val1_val2_key" UNIQUE CONSTRAINT, btree (val1, val2) Child tables: test_constraints_inh +Distribute By: HASH(val1) +Location Nodes: ALL DATANODES ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key; \d+ test_constraints @@ -1561,6 +1229,8 @@ ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key val1 | character varying | | extended | | val2 | integer | | plain | | Child tables: test_constraints_inh +Distribute By: HASH(val1) +Location Nodes: ALL DATANODES \d+ test_constraints_inh Table "public.test_constraints_inh" @@ -1570,6 +1240,8 @@ Child tables: test_constraints_inh val1 | character varying | | extended | | val2 | integer | | plain | | Inherits: test_constraints +Distribute By: HASH(val1) +Location Nodes: ALL DATANODES DROP TABLE test_constraints_inh; DROP TABLE test_constraints; @@ -1586,6 +1258,8 @@ CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints); Indexes: "test_ex_constraints_c_excl" EXCLUDE USING gist (c WITH &&) Child tables: test_ex_constraints_inh +Distribute By: ROUND ROBIN +Location Nodes: ALL DATANODES ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl; \d+ test_ex_constraints @@ -1594,6 +1268,8 @@ ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl; --------+--------+-----------+---------+--------------+------------- c | circle | | plain | | Child tables: test_ex_constraints_inh +Distribute By: ROUND ROBIN +Location Nodes: ALL DATANODES \d+ test_ex_constraints_inh Table "public.test_ex_constraints_inh" @@ -1601,6 +1277,8 @@ Child tables: test_ex_constraints_inh --------+--------+-----------+---------+--------------+------------- c | circle | | plain | | Inherits: test_ex_constraints +Distribute By: ROUND ROBIN +Location Nodes: ALL DATANODES DROP TABLE test_ex_constraints_inh; DROP TABLE test_ex_constraints; @@ -1617,6 +1295,8 @@ Indexes: "test_primary_constraints_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "test_foreign_constraints" CONSTRAINT "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id) +Distribute By: HASH(id) +Location Nodes: ALL DATANODES \d+ test_foreign_constraints Table "public.test_foreign_constraints" @@ -1626,6 +1306,8 @@ Referenced by: Foreign-key constraints: "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id) Child tables: test_foreign_constraints_inh +Distribute By: HASH(id1) +Location Nodes: ALL DATANODES ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey; \d+ test_foreign_constraints @@ -1634,6 +1316,8 @@ ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id --------+---------+-----------+---------+--------------+------------- id1 | integer | | plain | | Child tables: test_foreign_constraints_inh +Distribute By: HASH(id1) +Location Nodes: ALL DATANODES \d+ test_foreign_constraints_inh Table "public.test_foreign_constraints_inh" @@ -1641,6 +1325,8 @@ Child tables: test_foreign_constraints_inh --------+---------+-----------+---------+--------------+------------- id1 | integer | | plain | | Inherits: test_foreign_constraints +Distribute By: HASH(id1) +Location Nodes: ALL DATANODES DROP TABLE test_foreign_constraints_inh; DROP TABLE test_foreign_constraints; @@ -1663,9 +1349,9 @@ analyze patest0; analyze patest1; analyze patest2; explain (costs off, num_nodes off, nodes off) -select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; - QUERY PLAN --------------------------------------------------- +select * from patest0 join (select f1 from int4_tbl where f1 = 0 limit 1) ss on id = f1; + QUERY PLAN +---------------------------------------------------------- Nested Loop -> Limit -> Remote Subquery Scan on all @@ -1677,12 +1363,12 @@ select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; -> Seq Scan on patest0 Filter: (int4_tbl.f1 = id) -> Remote Subquery Scan on all - -> Bitmap Heap Scan on patest1 patest0 + -> Bitmap Heap Scan on patest1 Recheck Cond: (id = int4_tbl.f1) -> Bitmap Index Scan on patest1i Index Cond: (id = int4_tbl.f1) -> Remote Subquery Scan on all - -> Bitmap Heap Scan on patest2 patest0 + -> Bitmap Heap Scan on patest2 Recheck Cond: (id = int4_tbl.f1) -> Bitmap Index Scan on patest2i Index Cond: (id = int4_tbl.f1) @@ -1712,12 +1398,12 @@ select * from patest0 join (select f1 from int4_tbl where f1 = 0 limit 1) ss on -> Seq Scan on patest0 Filter: (int4_tbl.f1 = id) -> Remote Subquery Scan on all - -> Bitmap Heap Scan on patest1 patest0 + -> Bitmap Heap Scan on patest1 Recheck Cond: (id = int4_tbl.f1) -> Bitmap Index Scan on patest1i Index Cond: (id = int4_tbl.f1) -> Remote Subquery Scan on all - -> Seq Scan on patest2 patest0 + -> Seq Scan on patest2 Filter: (int4_tbl.f1 = id) (18 rows) @@ -1755,8 +1441,8 @@ insert into matest3 (name) values ('Test 5'); insert into matest3 (name) values ('Test 6'); set enable_indexscan = off; -- force use of seqscan/sort, so no merge explain (verbose, costs off, nodes off) select * from matest0 order by 1-id; - QUERY PLAN ---------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------ Sort Output: matest0.id, matest0.name, ((1 - matest0.id)) Sort Key: ((1 - matest0.id)) @@ -1764,21 +1450,21 @@ explain (verbose, costs off, nodes off) select * from matest0 order by 1-id; Output: matest0.id, matest0.name, (1 - matest0.id) -> Append -> Remote Subquery Scan on all - Output: public.matest0.id, public.matest0.name + Output: matest0.id, matest0.name -> Seq Scan on public.matest0 - Output: public.matest0.id, public.matest0.name + Output: matest0.id, matest0.name -> Remote Subquery Scan on all - Output: public.matest0.id, public.matest0.name - -> Seq Scan on public.matest1 matest0 - Output: public.matest0.id, public.matest0.name + Output: matest1.id, matest1.name + -> Seq Scan on public.matest1 + Output: matest1.id, matest1.name -> Remote Subquery Scan on all - Output: public.matest0.id, public.matest0.name - -> Seq Scan on public.matest2 matest0 - Output: public.matest0.id, public.matest0.name + Output: matest2.id, matest2.name + -> Seq Scan on public.matest2 + Output: matest2.id, matest2.name -> Remote Subquery Scan on all - Output: public.matest0.id, public.matest0.name - -> Seq Scan on public.matest3 matest0 - Output: public.matest0.id, public.matest0.name + Output: matest3.id, matest3.name + -> Seq Scan on public.matest3 + Output: matest3.id, matest3.name (22 rows) select * from matest0 order by 1-id; @@ -1793,20 +1479,28 @@ select * from matest0 order by 1-id; (6 rows) explain (verbose, costs off) select min(1-id) from matest0; - QUERY PLAN ----------------------------------------- + QUERY PLAN +----------------------------------------------------------------- Aggregate Output: min((1 - matest0.id)) -> Append - -> Seq Scan on public.matest0 + -> Remote Subquery Scan on all (datanode_1,datanode_2) Output: matest0.id - -> Seq Scan on public.matest1 + -> Seq Scan on public.matest0 + Output: matest0.id + -> Remote Subquery Scan on all (datanode_1,datanode_2) Output: matest1.id - -> Seq Scan on public.matest2 + -> Seq Scan on public.matest1 + Output: matest1.id + -> Remote Subquery Scan on all (datanode_1,datanode_2) Output: matest2.id - -> Seq Scan on public.matest3 + -> Seq Scan on public.matest2 + Output: matest2.id + -> Remote Subquery Scan on all (datanode_1,datanode_2) Output: matest3.id -(11 rows) + -> Seq Scan on public.matest3 + Output: matest3.id +(19 rows) select min(1-id) from matest0; min @@ -1817,32 +1511,30 @@ select min(1-id) from matest0; reset enable_indexscan; set enable_seqscan = off; -- plan with fewest seqscans should be merge explain (verbose, costs off, nodes off) select * from matest0 order by 1-id; - QUERY PLAN ---------------------------------------------------------------------------------------------------- - Result - Output: public.matest0.id, public.matest0.name, ((1 - public.matest0.id)) - -> Merge Append - Sort Key: ((1 - public.matest0.id)) - -> Remote Subquery Scan on all - Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id) - -> Index Scan using matest0i on public.matest0 - Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id) - -> Remote Subquery Scan on all - Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id) - -> Index Scan using matest1i on public.matest1 matest0 - Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id) - -> Remote Subquery Scan on all - Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id) - -> Sort - Output: public.matest0.id, public.matest0.name, ((1 - public.matest0.id)) - Sort Key: ((1 - public.matest0.id)) - -> Seq Scan on public.matest2 matest0 - Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id) - -> Remote Subquery Scan on all - Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id) - -> Index Scan using matest3i on public.matest3 matest0 - Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id) -(23 rows) + QUERY PLAN +------------------------------------------------------------------------ + Merge Append + Sort Key: ((1 - matest0.id)) + -> Remote Subquery Scan on all + Output: matest0.id, matest0.name, (1 - matest0.id) + -> Index Scan using matest0i on public.matest0 + Output: matest0.id, matest0.name, (1 - matest0.id) + -> Remote Subquery Scan on all + Output: matest1.id, matest1.name, (1 - matest1.id) + -> Index Scan using matest1i on public.matest1 + Output: matest1.id, matest1.name, (1 - matest1.id) + -> Remote Subquery Scan on all + Output: matest2.id, matest2.name, (1 - matest2.id) + -> Sort + Output: matest2.id, matest2.name, ((1 - matest2.id)) + Sort Key: ((1 - matest2.id)) + -> Seq Scan on public.matest2 + Output: matest2.id, matest2.name, (1 - matest2.id) + -> Remote Subquery Scan on all + Output: matest3.id, matest3.name, (1 - matest3.id) + -> Index Scan using matest3i on public.matest3 + Output: matest3.id, matest3.name, (1 - matest3.id) +(21 rows) select * from matest0 order by 1-id; id | name @@ -1856,8 +1548,8 @@ select * from matest0 order by 1-id; (6 rows) explain (verbose, costs off) select min(1-id) from matest0; - QUERY PLAN --------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------- Result Output: $0 InitPlan 1 (returns $0) @@ -1867,23 +1559,31 @@ explain (verbose, costs off) select min(1-id) from matest0; Output: ((1 - matest0.id)) -> Merge Append Sort Key: ((1 - matest0.id)) - -> Index Scan using matest0i on public.matest0 + -> Remote Subquery Scan on all (datanode_1,datanode_2) Output: matest0.id, (1 - matest0.id) - Index Cond: ((1 - matest0.id) IS NOT NULL) - -> Index Scan using matest1i on public.matest1 + -> Index Scan using matest0i on public.matest0 + Output: matest0.id, (1 - matest0.id) + Index Cond: ((1 - matest0.id) IS NOT NULL) + -> Remote Subquery Scan on all (datanode_1,datanode_2) Output: matest1.id, (1 - matest1.id) - Index Cond: ((1 - matest1.id) IS NOT NULL) - -> Sort - Output: matest2.id, ((1 - matest2.id)) - Sort Key: ((1 - matest2.id)) - -> Bitmap Heap Scan on public.matest2 - Output: matest2.id, (1 - matest2.id) - Filter: ((1 - matest2.id) IS NOT NULL) - -> Bitmap Index Scan on matest2_pkey - -> Index Scan using matest3i on public.matest3 + -> Index Scan using matest1i on public.matest1 + Output: matest1.id, (1 - matest1.id) + Index Cond: ((1 - matest1.id) IS NOT NULL) + -> Remote Subquery Scan on all (datanode_1,datanode_2) + Output: matest2.id, (1 - matest2.id) + -> Sort + Output: matest2.id, ((1 - matest2.id)) + Sort Key: ((1 - matest2.id)) + -> Bitmap Heap Scan on public.matest2 + Output: matest2.id, (1 - matest2.id) + Filter: ((1 - matest2.id) IS NOT NULL) + -> Bitmap Index Scan on matest2_pkey + -> Remote Subquery Scan on all (datanode_1,datanode_2) Output: matest3.id, (1 - matest3.id) - Index Cond: ((1 - matest3.id) IS NOT NULL) -(25 rows) + -> Index Scan using matest3i on public.matest3 + Output: matest3.id, (1 - matest3.id) + Index Cond: ((1 - matest3.id) IS NOT NULL) +(33 rows) select min(1-id) from matest0; min @@ -1909,54 +1609,51 @@ SELECT thousand, tenthous FROM tenk1 UNION ALL SELECT thousand, thousand FROM tenk1 ORDER BY thousand, tenthous; - QUERY PLAN ------------------------------------------------------------------------------ - Result - -> Merge Append - Sort Key: public.tenk1.thousand, public.tenk1.tenthous - -> Remote Subquery Scan on all - -> Index Only Scan using tenk1_thous_tenthous on tenk1 - -> Remote Subquery Scan on all - -> Sort - Sort Key: public.tenk1.thousand, public.tenk1.thousand - -> Index Only Scan using tenk1_thous_tenthous on tenk1 -(9 rows) + QUERY PLAN +------------------------------------------------------------------------------- + Merge Append + Sort Key: tenk1.thousand, tenk1.tenthous + -> Remote Subquery Scan on all + -> Index Only Scan using tenk1_thous_tenthous on tenk1 + -> Remote Subquery Scan on all + -> Sort + Sort Key: tenk1_1.thousand, tenk1_1.thousand + -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1 +(8 rows) explain (costs off, num_nodes off, nodes off) SELECT thousand, tenthous, thousand+tenthous AS x FROM tenk1 UNION ALL SELECT 42, 42, hundred FROM tenk1 ORDER BY thousand, tenthous; - QUERY PLAN ------------------------------------------------------------------------ - Result - -> Merge Append - Sort Key: public.tenk1.thousand, public.tenk1.tenthous - -> Remote Subquery Scan on all - -> Index Only Scan using tenk1_thous_tenthous on tenk1 - -> Remote Subquery Scan on all - -> Sort - Sort Key: (42), (42) - -> Index Only Scan using tenk1_hundred on tenk1 -(9 rows) + QUERY PLAN +------------------------------------------------------------------------ + Merge Append + Sort Key: tenk1.thousand, tenk1.tenthous + -> Remote Subquery Scan on all + -> Index Only Scan using tenk1_thous_tenthous on tenk1 + -> Remote Subquery Scan on all + -> Sort + Sort Key: (42), (42) + -> Index Only Scan using tenk1_hundred on tenk1 tenk1_1 +(8 rows) explain (costs off, num_nodes off, nodes off) SELECT thousand, tenthous FROM tenk1 UNION ALL SELECT thousand, random()::integer FROM tenk1 ORDER BY thousand, tenthous; - QUERY PLAN ------------------------------------------------------------------------------ - Result - -> Merge Append - Sort Key: public.tenk1.thousand, public.tenk1.tenthous - -> Remote Subquery Scan on all - -> Index Only Scan using tenk1_thous_tenthous on tenk1 - -> Remote Subquery Scan on all - -> Sort - Sort Key: public.tenk1.thousand, ((random())::integer) - -> Index Only Scan using tenk1_thous_tenthous on tenk1 -(9 rows) + QUERY PLAN +------------------------------------------------------------------------------- + Merge Append + Sort Key: tenk1.thousand, tenk1.tenthous + -> Remote Subquery Scan on all + -> Index Only Scan using tenk1_thous_tenthous on tenk1 + -> Remote Subquery Scan on all + -> Sort + Sort Key: tenk1_1.thousand, ((random())::integer) + -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1 +(8 rows) -- Check min/max aggregate optimization explain (costs off, num_nodes off, nodes off) @@ -2006,18 +1703,57 @@ SELECT x, y FROM UNION ALL SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s ORDER BY x, y; - QUERY PLAN -------------------------------------------------------------------------- - Result - -> Merge Append - Sort Key: a.thousand, a.tenthous - -> Remote Subquery Scan on all - -> Index Only Scan using tenk1_thous_tenthous on tenk1 a - -> Remote Subquery Scan on all - -> Sort - Sort Key: b.unique2, b.unique2 - -> Index Only Scan using tenk1_unique2 on tenk1 b -(9 rows) + QUERY PLAN +------------------------------------------------------------------- + Merge Append + Sort Key: a.thousand, a.tenthous + -> Remote Subquery Scan on all + -> Index Only Scan using tenk1_thous_tenthous on tenk1 a + -> Remote Subquery Scan on all + -> Sort + Sort Key: b.unique2, b.unique2 + -> Index Only Scan using tenk1_unique2 on tenk1 b +(8 rows) + +-- exercise rescan code path via a repeatedly-evaluated subquery +explain (costs off) +SELECT + ARRAY(SELECT f.i FROM ( + (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1) + UNION ALL + (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1) + ) f(i) + ORDER BY f.i LIMIT 10) +FROM generate_series(1, 3) g(i); + QUERY PLAN +---------------------------------------------------------------- + Function Scan on generate_series g + SubPlan 1 + -> Limit + -> Merge Append + Sort Key: ((d.d + g.i)) + -> Sort + Sort Key: ((d.d + g.i)) + -> Function Scan on generate_series d + -> Sort + Sort Key: ((d_1.d + g.i)) + -> Function Scan on generate_series d_1 +(11 rows) + +SELECT + ARRAY(SELECT f.i FROM ( + (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1) + UNION ALL + (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1) + ) f(i) + ORDER BY f.i LIMIT 10) +FROM generate_series(1, 3) g(i); + array +------------------------------ + {1,5,6,8,11,11,14,16,17,20} + {2,6,7,9,12,12,15,17,18,21} + {3,7,8,10,13,13,16,18,19,22} +(3 rows) reset enable_seqscan; reset enable_indexscan; diff --git a/src/test/regress/expected/inherit_1.out b/src/test/regress/expected/inherit_1.out deleted file mode 100644 index 9928091584..0000000000 --- a/src/test/regress/expected/inherit_1.out +++ /dev/null @@ -1,1488 +0,0 @@ --- --- Test inheritance features --- -CREATE TABLE a (aa TEXT) distribute by roundrobin; -CREATE TABLE b (bb TEXT) INHERITS (a) distribute by roundrobin; -CREATE TABLE c (cc TEXT) INHERITS (a) distribute by roundrobin; -CREATE TABLE d (dd TEXT) INHERITS (b,c,a) distribute by roundrobin; -NOTICE: merging multiple inherited definitions of column "aa" -NOTICE: merging multiple inherited definitions of column "aa" -INSERT INTO a(aa) VALUES('aaa'); -INSERT INTO a(aa) VALUES('aaaa'); -INSERT INTO a(aa) VALUES('aaaaa'); -INSERT INTO a(aa) VALUES('aaaaaa'); -INSERT INTO a(aa) VALUES('aaaaaaa'); -INSERT INTO a(aa) VALUES('aaaaaaaa'); -INSERT INTO b(aa) VALUES('bbb'); -INSERT INTO b(aa) VALUES('bbbb'); -INSERT INTO b(aa) VALUES('bbbbb'); -INSERT INTO b(aa) VALUES('bbbbbb'); -INSERT INTO b(aa) VALUES('bbbbbbb'); -INSERT INTO b(aa) VALUES('bbbbbbbb'); -INSERT INTO c(aa) VALUES('ccc'); -INSERT INTO c(aa) VALUES('cccc'); -INSERT INTO c(aa) VALUES('ccccc'); -INSERT INTO c(aa) VALUES('cccccc'); -INSERT INTO c(aa) VALUES('ccccccc'); -INSERT INTO c(aa) VALUES('cccccccc'); -INSERT INTO d(aa) VALUES('ddd'); -INSERT INTO d(aa) VALUES('dddd'); -INSERT INTO d(aa) VALUES('ddddd'); -INSERT INTO d(aa) VALUES('dddddd'); -INSERT INTO d(aa) VALUES('ddddddd'); -INSERT INTO d(aa) VALUES('dddddddd'); -SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+---- -(0 rows) - -SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+----+---- -(0 rows) - -SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----+---- -(0 rows) - -SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+----+----+----+---- -(0 rows) - -SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+---- -(0 rows) - -SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+----+---- -(0 rows) - -SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----+---- -(0 rows) - -SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+----+----+----+---- -(0 rows) - --- In Postgres-XL OIDs are not consistent across the cluster. Hence above --- queries do not show any result. Hence in order to ensure data consistency, we --- add following SQLs. In case above set of queries start producing valid --- results in XC, we should remove the following set -SELECT * FROM a ORDER BY a.aa; - aa ----------- - aaa - aaaa - aaaaa - aaaaaa - aaaaaaa - aaaaaaaa - bbb - bbbb - bbbbb - bbbbbb - bbbbbbb - bbbbbbbb - ccc - cccc - ccccc - cccccc - ccccccc - cccccccc - ddd - dddd - ddddd - dddddd - ddddddd - dddddddd -(24 rows) - -SELECT * from b ORDER BY b.aa; - aa | bb -----------+---- - bbb | - bbbb | - bbbbb | - bbbbbb | - bbbbbbb | - bbbbbbbb | - ddd | - dddd | - ddddd | - dddddd | - ddddddd | - dddddddd | -(12 rows) - -SELECT * FROM c ORDER BY c.aa; - aa | cc -----------+---- - ccc | - cccc | - ccccc | - cccccc | - ccccccc | - cccccccc | - ddd | - dddd | - ddddd | - dddddd | - ddddddd | - dddddddd | -(12 rows) - -SELECT * from d ORDER BY d.aa; - aa | bb | cc | dd -----------+----+----+---- - ddd | | | - dddd | | | - ddddd | | | - dddddd | | | - ddddddd | | | - dddddddd | | | -(6 rows) - -SELECT * FROM ONLY a ORDER BY a.aa; - aa ----------- - aaa - aaaa - aaaaa - aaaaaa - aaaaaaa - aaaaaaaa -(6 rows) - -SELECT * from ONLY b ORDER BY b.aa; - aa | bb -----------+---- - bbb | - bbbb | - bbbbb | - bbbbbb | - bbbbbbb | - bbbbbbbb | -(6 rows) - -SELECT * FROM ONLY c ORDER BY c.aa; - aa | cc -----------+---- - ccc | - cccc | - ccccc | - cccccc | - ccccccc | - cccccccc | -(6 rows) - -SELECT * from ONLY d ORDER BY d.aa; - aa | bb | cc | dd -----------+----+----+---- - ddd | | | - dddd | | | - ddddd | | | - dddddd | | | - ddddddd | | | - dddddddd | | | -(6 rows) - -UPDATE a SET aa='zzzz' WHERE aa='aaaa'; -UPDATE ONLY a SET aa='zzzzz' WHERE aa='aaaaa'; -UPDATE b SET aa='zzz' WHERE aa='aaa'; -UPDATE ONLY b SET aa='zzz' WHERE aa='aaa'; -UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%'; -SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+---- -(0 rows) - -SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+----+---- -(0 rows) - -SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----+---- -(0 rows) - -SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+----+----+----+---- -(0 rows) - -SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+---- -(0 rows) - -SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+----+---- -(0 rows) - -SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----+---- -(0 rows) - -SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+----+----+----+---- -(0 rows) - --- In Postgres-XL OIDs are not consistent across the cluster. Hence above --- queries do not show any result. Hence in order to ensure data consistency, we --- add following SQLs. In case above set of queries start producing valid --- results in XC, we should remove the following set -SELECT * FROM a ORDER BY a.aa; - aa ----------- - bbb - bbbb - bbbbb - bbbbbb - bbbbbbb - bbbbbbbb - ccc - cccc - ccccc - cccccc - ccccccc - cccccccc - ddd - dddd - ddddd - dddddd - ddddddd - dddddddd - zzzz - zzzzz - zzzzzz - zzzzzz - zzzzzz - zzzzzz -(24 rows) - -SELECT * from b ORDER BY b.aa; - aa | bb -----------+---- - bbb | - bbbb | - bbbbb | - bbbbbb | - bbbbbbb | - bbbbbbbb | - ddd | - dddd | - ddddd | - dddddd | - ddddddd | - dddddddd | -(12 rows) - -SELECT * FROM c ORDER BY c.aa; - aa | cc -----------+---- - ccc | - cccc | - ccccc | - cccccc | - ccccccc | - cccccccc | - ddd | - dddd | - ddddd | - dddddd | - ddddddd | - dddddddd | -(12 rows) - -SELECT * from d ORDER BY d.aa; - aa | bb | cc | dd -----------+----+----+---- - ddd | | | - dddd | | | - ddddd | | | - dddddd | | | - ddddddd | | | - dddddddd | | | -(6 rows) - -SELECT * FROM ONLY a ORDER BY a.aa; - aa --------- - zzzz - zzzzz - zzzzzz - zzzzzz - zzzzzz - zzzzzz -(6 rows) - -SELECT * from ONLY b ORDER BY b.aa; - aa | bb -----------+---- - bbb | - bbbb | - bbbbb | - bbbbbb | - bbbbbbb | - bbbbbbbb | -(6 rows) - -SELECT * FROM ONLY c ORDER BY c.aa; - aa | cc -----------+---- - ccc | - cccc | - ccccc | - cccccc | - ccccccc | - cccccccc | -(6 rows) - -SELECT * from ONLY d ORDER BY d.aa; - aa | bb | cc | dd -----------+----+----+---- - ddd | | | - dddd | | | - ddddd | | | - dddddd | | | - ddddddd | | | - dddddddd | | | -(6 rows) - -UPDATE b SET aa='new'; -SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+---- -(0 rows) - -SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+----+---- -(0 rows) - -SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----+---- -(0 rows) - -SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+----+----+----+---- -(0 rows) - -SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+---- -(0 rows) - -SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+----+---- -(0 rows) - -SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----+---- -(0 rows) - -SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+----+----+----+---- -(0 rows) - --- In Postgres-XL OIDs are not consistent across the cluster. Hence above --- queries do not show any result. Hence in order to ensure data consistency, we --- add following SQLs. In case above set of queries start producing valid --- results in XC, we should remove the following set -SELECT * FROM a ORDER BY a.aa; - aa ----------- - ccc - cccc - ccccc - cccccc - ccccccc - cccccccc - new - new - new - new - new - new - new - new - new - new - new - new - zzzz - zzzzz - zzzzzz - zzzzzz - zzzzzz - zzzzzz -(24 rows) - -SELECT * from b ORDER BY b.aa; - aa | bb ------+---- - new | - new | - new | - new | - new | - new | - new | - new | - new | - new | - new | - new | -(12 rows) - -SELECT * FROM c ORDER BY c.aa; - aa | cc -----------+---- - ccc | - cccc | - ccccc | - cccccc | - ccccccc | - cccccccc | - new | - new | - new | - new | - new | - new | -(12 rows) - -SELECT * from d ORDER BY d.aa; - aa | bb | cc | dd ------+----+----+---- - new | | | - new | | | - new | | | - new | | | - new | | | - new | | | -(6 rows) - -SELECT * FROM ONLY a ORDER BY a.aa; - aa --------- - zzzz - zzzzz - zzzzzz - zzzzzz - zzzzzz - zzzzzz -(6 rows) - -SELECT * from ONLY b ORDER BY b.aa; - aa | bb ------+---- - new | - new | - new | - new | - new | - new | -(6 rows) - -SELECT * FROM ONLY c ORDER BY c.aa; - aa | cc -----------+---- - ccc | - cccc | - ccccc | - cccccc | - ccccccc | - cccccccc | -(6 rows) - -SELECT * from ONLY d ORDER BY d.aa; - aa | bb | cc | dd ------+----+----+---- - new | | | - new | | | - new | | | - new | | | - new | | | - new | | | -(6 rows) - -UPDATE a SET aa='new'; -DELETE FROM ONLY c WHERE aa='new'; -SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+---- -(0 rows) - -SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+----+---- -(0 rows) - -SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----+---- -(0 rows) - -SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+----+----+----+---- -(0 rows) - -SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+---- -(0 rows) - -SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+----+---- -(0 rows) - -SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----+---- -(0 rows) - -SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+----+----+----+---- -(0 rows) - --- In Postgres-XL OIDs are not consistent across the cluster. Hence above --- queries do not show any result. Hence in order to ensure data consistency, we --- add following SQLs. In case above set of queries start producing valid --- results in XC, we should remove the following set -SELECT * FROM a ORDER BY a.aa; - aa ------ - new - new - new - new - new - new - new - new - new - new - new - new - new - new - new - new - new - new -(18 rows) - -SELECT * from b ORDER BY b.aa; - aa | bb ------+---- - new | - new | - new | - new | - new | - new | - new | - new | - new | - new | - new | - new | -(12 rows) - -SELECT * FROM c ORDER BY c.aa; - aa | cc ------+---- - new | - new | - new | - new | - new | - new | -(6 rows) - -SELECT * from d ORDER BY d.aa; - aa | bb | cc | dd ------+----+----+---- - new | | | - new | | | - new | | | - new | | | - new | | | - new | | | -(6 rows) - -SELECT * FROM ONLY a ORDER BY a.aa; - aa ------ - new - new - new - new - new - new -(6 rows) - -SELECT * from ONLY b ORDER BY b.aa; - aa | bb ------+---- - new | - new | - new | - new | - new | - new | -(6 rows) - -SELECT * FROM ONLY c ORDER BY c.aa; - aa | cc -----+---- -(0 rows) - -SELECT * from ONLY d ORDER BY d.aa; - aa | bb | cc | dd ------+----+----+---- - new | | | - new | | | - new | | | - new | | | - new | | | - new | | | -(6 rows) - -DELETE FROM a; -SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+---- -(0 rows) - -SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+----+---- -(0 rows) - -SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----+---- -(0 rows) - -SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+----+----+----+---- -(0 rows) - -SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid ORDER BY relname, a.aa; - relname | aa ----------+---- -(0 rows) - -SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid ORDER BY relname, b.aa; - relname | aa | bb ----------+----+---- -(0 rows) - -SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid ORDER BY relname, c.aa; - relname | aa | cc ----------+----+---- -(0 rows) - -SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid ORDER BY relname, d.aa; - relname | aa | bb | cc | dd ----------+----+----+----+---- -(0 rows) - --- In Postgres-XL OIDs are not consistent across the cluster. Hence above --- queries do not show any result. Hence in order to ensure data consistency, we --- add following SQLs. In case above set of queries start producing valid --- results in XC, we should remove the following set -SELECT * FROM a ORDER BY a.aa; - aa ----- -(0 rows) - -SELECT * from b ORDER BY b.aa; - aa | bb -----+---- -(0 rows) - -SELECT * FROM c ORDER BY c.aa; - aa | cc -----+---- -(0 rows) - -SELECT * from d ORDER BY d.aa; - aa | bb | cc | dd -----+----+----+---- -(0 rows) - -SELECT * FROM ONLY a ORDER BY a.aa; - aa ----- -(0 rows) - -SELECT * from ONLY b ORDER BY b.aa; - aa | bb -----+---- -(0 rows) - -SELECT * FROM ONLY c ORDER BY c.aa; - aa | cc -----+---- -(0 rows) - -SELECT * from ONLY d ORDER BY d.aa; - aa | bb | cc | dd -----+----+----+---- -(0 rows) - --- Confirm PRIMARY KEY adds NOT NULL constraint to child table -CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a); -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "z_pkey" for table "z" -INSERT INTO z VALUES (NULL, 'text'); -- should fail -ERROR: null value in column "aa" violates not-null constraint -DETAIL: Failing row contains (null, text). --- Check UPDATE with inherited target and an inherited source table -create temp table foo(f1 int, f2 int); -create temp table foo2(f3 int) inherits (foo); -create temp table bar(f1 int, f2 int); -create temp table bar2(f3 int) inherits (bar); -insert into foo values(1,1); -insert into foo values(3,3); -insert into foo2 values(2,2,2); -insert into foo2 values(3,3,3); -insert into bar values(1,1); -insert into bar values(2,2); -insert into bar values(3,3); -insert into bar values(4,4); -insert into bar2 values(1,1,1); -insert into bar2 values(2,2,2); -insert into bar2 values(3,3,3); -insert into bar2 values(4,4,4); -update bar set f2 = f2 + 100 where f1 in (select f1 from foo); -SELECT relname, bar.* FROM bar, pg_class where bar.tableoid = pg_class.oid -order by 1,2; - relname | f1 | f2 ----------+----+---- -(0 rows) - --- In Postgres-XL OIDs are not consistent across the cluster. Hence above --- queries do not show any result. Hence in order to ensure data consistency, we --- add following SQLs. In case above set of queries start producing valid --- results in XC, we should remove the following set -SELECT * FROM bar ORDER BY f1, f2; - f1 | f2 -----+----- - 1 | 101 - 1 | 101 - 2 | 102 - 2 | 102 - 3 | 103 - 3 | 103 - 4 | 4 - 4 | 4 -(8 rows) - -SELECT * FROM ONLY bar ORDER BY f1, f2; - f1 | f2 -----+----- - 1 | 101 - 2 | 102 - 3 | 103 - 4 | 4 -(4 rows) - -SELECT * FROM bar2 ORDER BY f1, f2; - f1 | f2 | f3 -----+-----+---- - 1 | 101 | 1 - 2 | 102 | 2 - 3 | 103 | 3 - 4 | 4 | 4 -(4 rows) - -/* Test multiple inheritance of column defaults */ -CREATE TABLE firstparent (tomorrow date default now()::date + 1); -CREATE TABLE secondparent (tomorrow date default now() :: date + 1); -CREATE TABLE jointchild () INHERITS (firstparent, secondparent); -- ok -NOTICE: merging multiple inherited definitions of column "tomorrow" -CREATE TABLE thirdparent (tomorrow date default now()::date - 1); -CREATE TABLE otherchild () INHERITS (firstparent, thirdparent); -- not ok -NOTICE: merging multiple inherited definitions of column "tomorrow" -ERROR: column "tomorrow" inherits conflicting default values -HINT: To resolve the conflict, specify a default explicitly. -CREATE TABLE otherchild (tomorrow date default now()) - INHERITS (firstparent, thirdparent); -- ok, child resolves ambiguous default -NOTICE: merging multiple inherited definitions of column "tomorrow" -NOTICE: merging column "tomorrow" with inherited definition -DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild; --- Test changing the type of inherited columns -insert into d values('test','one','two','three'); -alter table a alter column aa type integer using bit_length(aa); -select * from d; - aa | bb | cc | dd -----+-----+-----+------- - 32 | one | two | three -(1 row) - --- Test non-inheritable parent constraints -create table p1(ff1 int); -alter table p1 add constraint p1chk check no inherit (ff1 > 0); -alter table p1 add constraint p2chk check (ff1 > 10); --- connoinherit should be true for NO INHERIT constraint -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname = 'p1' order by 1,2; - relname | conname | contype | conislocal | coninhcount | connoinherit ----------+---------+---------+------------+-------------+-------------- - p1 | p1chk | c | t | 0 | t - p1 | p2chk | c | t | 0 | f -(2 rows) - --- Test that child does not inherit NO INHERIT constraints -create table c1 () inherits (p1); -\d p1 - Table "public.p1" - Column | Type | Modifiers ---------+---------+----------- - ff1 | integer | -Check constraints: - "p1chk" CHECK NO INHERIT (ff1 > 0) - "p2chk" CHECK (ff1 > 10) -Number of child tables: 1 (Use \d+ to list them.) - -\d c1 - Table "public.c1" - Column | Type | Modifiers ---------+---------+----------- - ff1 | integer | -Check constraints: - "p2chk" CHECK (ff1 > 10) -Inherits: p1 - -drop table p1 cascade; -NOTICE: drop cascades to table c1 --- Tests for casting between the rowtypes of parent and child --- tables. See the pgsql-hackers thread beginning Dec. 4/04 -create table base (i integer); -create table derived () inherits (base); -insert into derived (i) values (0); -select derived::base from derived; - derived ---------- - (0) -(1 row) - -drop table derived; -drop table base; -create table p1(ff1 int); -create table p2(f1 text); -create function p2text(p2) returns text as 'select $1.f1' language sql; -create table c1(f3 int) inherits(p1,p2); -insert into c1 values(123456789, 'hi', 42); -select p2text(c1.*) from c1; - p2text --------- - hi -(1 row) - -drop function p2text(p2); -drop table c1; -drop table p2; -drop table p1; -CREATE TABLE ac (aa TEXT); -alter table ac add constraint ac_check check (aa is not null); -CREATE TABLE bc (bb TEXT) INHERITS (ac); -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - relname | conname | contype | conislocal | coninhcount | consrc ----------+----------+---------+------------+-------------+------------------ - ac | ac_check | c | t | 0 | (aa IS NOT NULL) - bc | ac_check | c | f | 1 | (aa IS NOT NULL) -(2 rows) - -insert into ac (aa) values (NULL); -ERROR: new row for relation "ac" violates check constraint "ac_check" -DETAIL: Failing row contains (null). -insert into bc (aa) values (NULL); -ERROR: new row for relation "bc" violates check constraint "ac_check" -DETAIL: Failing row contains (null, null). -alter table bc drop constraint ac_check; -- fail, disallowed -ERROR: cannot drop inherited constraint "ac_check" of relation "bc" -alter table ac drop constraint ac_check; -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - relname | conname | contype | conislocal | coninhcount | consrc ----------+---------+---------+------------+-------------+-------- -(0 rows) - --- try the unnamed-constraint case -alter table ac add check (aa is not null); -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - relname | conname | contype | conislocal | coninhcount | consrc ----------+-------------+---------+------------+-------------+------------------ - ac | ac_aa_check | c | t | 0 | (aa IS NOT NULL) - bc | ac_aa_check | c | f | 1 | (aa IS NOT NULL) -(2 rows) - -insert into ac (aa) values (NULL); -ERROR: new row for relation "ac" violates check constraint "ac_aa_check" -DETAIL: Failing row contains (null). -insert into bc (aa) values (NULL); -ERROR: new row for relation "bc" violates check constraint "ac_aa_check" -DETAIL: Failing row contains (null, null). -alter table bc drop constraint ac_aa_check; -- fail, disallowed -ERROR: cannot drop inherited constraint "ac_aa_check" of relation "bc" -alter table ac drop constraint ac_aa_check; -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - relname | conname | contype | conislocal | coninhcount | consrc ----------+---------+---------+------------+-------------+-------- -(0 rows) - -alter table ac add constraint ac_check check (aa is not null); -alter table bc no inherit ac; -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - relname | conname | contype | conislocal | coninhcount | consrc ----------+----------+---------+------------+-------------+------------------ - ac | ac_check | c | t | 0 | (aa IS NOT NULL) - bc | ac_check | c | t | 0 | (aa IS NOT NULL) -(2 rows) - -alter table bc drop constraint ac_check; -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - relname | conname | contype | conislocal | coninhcount | consrc ----------+----------+---------+------------+-------------+------------------ - ac | ac_check | c | t | 0 | (aa IS NOT NULL) -(1 row) - -alter table ac drop constraint ac_check; -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - relname | conname | contype | conislocal | coninhcount | consrc ----------+---------+---------+------------+-------------+-------- -(0 rows) - -drop table bc; -drop table ac; -create table ac (a int constraint check_a check (a <> 0)); -create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac); -NOTICE: merging column "a" with inherited definition -NOTICE: merging constraint "check_a" with inherited definition -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - relname | conname | contype | conislocal | coninhcount | consrc ----------+---------+---------+------------+-------------+---------- - ac | check_a | c | t | 0 | (a <> 0) - bc | check_a | c | t | 1 | (a <> 0) - bc | check_b | c | t | 0 | (b <> 0) -(3 rows) - -drop table bc; -drop table ac; -create table ac (a int constraint check_a check (a <> 0)); -create table bc (b int constraint check_b check (b <> 0)); -create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc); -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2; - relname | conname | contype | conislocal | coninhcount | consrc ----------+---------+---------+------------+-------------+---------- - ac | check_a | c | t | 0 | (a <> 0) - bc | check_b | c | t | 0 | (b <> 0) - cc | check_a | c | f | 1 | (a <> 0) - cc | check_b | c | f | 1 | (b <> 0) - cc | check_c | c | t | 0 | (c <> 0) -(5 rows) - -alter table cc no inherit bc; -select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2; - relname | conname | contype | conislocal | coninhcount | consrc ----------+---------+---------+------------+-------------+---------- - ac | check_a | c | t | 0 | (a <> 0) - bc | check_b | c | t | 0 | (b <> 0) - cc | check_a | c | f | 1 | (a <> 0) - cc | check_b | c | t | 0 | (b <> 0) - cc | check_c | c | t | 0 | (c <> 0) -(5 rows) - -drop table cc; -drop table bc; -drop table ac; -create table p1(f1 int); -create table p2(f2 int); -create table c1(f3 int) inherits(p1,p2); -insert into c1 values(1,-1,2); -alter table p2 add constraint cc check (f2>0); -- fail -ERROR: check constraint "cc" is violated by some row -alter table p2 add check (f2>0); -- check it without a name, too -ERROR: check constraint "p2_f2_check" is violated by some row -delete from c1; -insert into c1 values(1,1,2); -alter table p2 add check (f2>0); -insert into c1 values(1,-1,2); -- fail -ERROR: new row for relation "c1" violates check constraint "p2_f2_check" -DETAIL: Failing row contains (1, -1, 2). -create table c2(f3 int) inherits(p1,p2); -\d c2 - Table "public.c2" - Column | Type | Modifiers ---------+---------+----------- - f1 | integer | - f2 | integer | - f3 | integer | -Check constraints: - "p2_f2_check" CHECK (f2 > 0) -Inherits: p1, - p2 - -create table c3 (f4 int) inherits(c1,c2); -NOTICE: merging multiple inherited definitions of column "f1" -NOTICE: merging multiple inherited definitions of column "f2" -NOTICE: merging multiple inherited definitions of column "f3" -\d c3 - Table "public.c3" - Column | Type | Modifiers ---------+---------+----------- - f1 | integer | - f2 | integer | - f3 | integer | - f4 | integer | -Check constraints: - "p2_f2_check" CHECK (f2 > 0) -Inherits: c1, - c2 - -drop table p1 cascade; -NOTICE: drop cascades to 3 other objects -DETAIL: drop cascades to table c1 -drop cascades to table c2 -drop cascades to table c3 -drop table p2 cascade; -create table pp1 (f1 int); -create table cc1 (f2 text, f3 int) inherits (pp1); -alter table pp1 add column a1 int check (a1 > 0); -\d cc1 - Table "public.cc1" - Column | Type | Modifiers ---------+---------+----------- - f1 | integer | - f2 | text | - f3 | integer | - a1 | integer | -Check constraints: - "pp1_a1_check" CHECK (a1 > 0) -Inherits: pp1 - -create table cc2(f4 float) inherits(pp1,cc1); -NOTICE: merging multiple inherited definitions of column "f1" -NOTICE: merging multiple inherited definitions of column "a1" -\d cc2 - Table "public.cc2" - Column | Type | Modifiers ---------+------------------+----------- - f1 | integer | - a1 | integer | - f2 | text | - f3 | integer | - f4 | double precision | -Check constraints: - "pp1_a1_check" CHECK (a1 > 0) -Inherits: pp1, - cc1 - -alter table pp1 add column a2 int check (a2 > 0); -NOTICE: merging definition of column "a2" for child "cc2" -NOTICE: merging constraint "pp1_a2_check" with inherited definition -\d cc2 - Table "public.cc2" - Column | Type | Modifiers ---------+------------------+----------- - f1 | integer | - a1 | integer | - f2 | text | - f3 | integer | - f4 | double precision | - a2 | integer | -Check constraints: - "pp1_a1_check" CHECK (a1 > 0) - "pp1_a2_check" CHECK (a2 > 0) -Inherits: pp1, - cc1 - -drop table pp1 cascade; -NOTICE: drop cascades to 2 other objects -DETAIL: drop cascades to table cc1 -drop cascades to table cc2 --- Test for renaming in simple multiple inheritance -CREATE TABLE inht1 (a int, b int); -CREATE TABLE inhs1 (b int, c int); -CREATE TABLE inhts (d int) INHERITS (inht1, inhs1); -NOTICE: merging multiple inherited definitions of column "b" -ALTER TABLE inht1 RENAME a TO aa; -ALTER TABLE inht1 RENAME b TO bb; -- to be failed -ERROR: cannot rename inherited column "b" -ALTER TABLE inhts RENAME aa TO aaa; -- to be failed -ERROR: cannot rename inherited column "aa" -ALTER TABLE inhts RENAME d TO dd; -\d+ inhts - Table "public.inhts" - Column | Type | Modifiers | Storage | Stats target | Description ---------+---------+-----------+---------+--------------+------------- - aa | integer | | plain | | - b | integer | | plain | | - c | integer | | plain | | - dd | integer | | plain | | -Inherits: inht1, - inhs1 -Has OIDs: no -Distribute By: HASH(aa) -Location Nodes: ALL DATANODES - -DROP TABLE inhts; --- Test for renaming in diamond inheritance -CREATE TABLE inht2 (x int) INHERITS (inht1); -CREATE TABLE inht3 (y int) INHERITS (inht1); -CREATE TABLE inht4 (z int) INHERITS (inht2, inht3); -NOTICE: merging multiple inherited definitions of column "aa" -NOTICE: merging multiple inherited definitions of column "b" -ALTER TABLE inht1 RENAME aa TO aaa; -\d+ inht4 - Table "public.inht4" - Column | Type | Modifiers | Storage | Stats target | Description ---------+---------+-----------+---------+--------------+------------- - aaa | integer | | plain | | - b | integer | | plain | | - x | integer | | plain | | - y | integer | | plain | | - z | integer | | plain | | -Inherits: inht2, - inht3 -Has OIDs: no -Distribute By: HASH(aaa) -Location Nodes: ALL DATANODES - -CREATE TABLE inhts (d int) INHERITS (inht2, inhs1); -NOTICE: merging multiple inherited definitions of column "b" -ALTER TABLE inht1 RENAME aaa TO aaaa; -ALTER TABLE inht1 RENAME b TO bb; -- to be failed -ERROR: cannot rename inherited column "b" -\d+ inhts - Table "public.inhts" - Column | Type | Modifiers | Storage | Stats target | Description ---------+---------+-----------+---------+--------------+------------- - aaaa | integer | | plain | | - b | integer | | plain | | - x | integer | | plain | | - c | integer | | plain | | - d | integer | | plain | | -Inherits: inht2, - inhs1 -Has OIDs: no -Distribute By: HASH(aaaa) -Location Nodes: ALL DATANODES - -WITH RECURSIVE r AS ( - SELECT 'inht1'::regclass AS inhrelid -UNION ALL - SELECT c.inhrelid FROM pg_inherits c, r WHERE r.inhrelid = c.inhparent -) -SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected - FROM (SELECT inhrelid, count(*) AS expected FROM pg_inherits - WHERE inhparent IN (SELECT inhrelid FROM r) GROUP BY inhrelid) e - JOIN pg_attribute a ON e.inhrelid = a.attrelid WHERE NOT attislocal - ORDER BY a.attrelid::regclass::name, a.attnum; - attrelid | attname | attinhcount | expected -----------+---------+-------------+---------- - inht2 | aaaa | 1 | 1 - inht2 | b | 1 | 1 - inht3 | aaaa | 1 | 1 - inht3 | b | 1 | 1 - inht4 | aaaa | 2 | 2 - inht4 | b | 2 | 2 - inht4 | x | 1 | 2 - inht4 | y | 1 | 2 - inhts | aaaa | 1 | 1 - inhts | b | 2 | 1 - inhts | x | 1 | 1 - inhts | c | 1 | 1 -(12 rows) - -DROP TABLE inht1, inhs1 CASCADE; -NOTICE: drop cascades to 4 other objects -DETAIL: drop cascades to table inht2 -drop cascades to table inhts -drop cascades to table inht3 -drop cascades to table inht4 --- --- Test parameterized append plans for inheritance trees --- -create temp table patest0 (id, x) as - select x, x from generate_series(0,1000) x; -create temp table patest1() inherits (patest0); -insert into patest1 - select x, x from generate_series(0,1000) x; -create temp table patest2() inherits (patest0); -insert into patest2 - select x, x from generate_series(0,1000) x; -create index patest0i on patest0(id); -create index patest1i on patest1(id); -create index patest2i on patest2(id); -analyze patest0; -analyze patest1; -analyze patest2; -explain (costs off, num_nodes off, nodes off) -select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; - QUERY PLAN ---------------------------------------------------------------------- - Hash Join - Hash Cond: (pg_temp_3.patest0.id = int4_tbl.f1) - -> Append - -> Data Node Scan on patest0 "_REMOTE_TABLE_QUERY_" - -> Data Node Scan on patest1 "_REMOTE_TABLE_QUERY_" - -> Data Node Scan on patest2 "_REMOTE_TABLE_QUERY_" - -> Hash - -> Limit - -> Data Node Scan on int4_tbl "_REMOTE_TABLE_QUERY_" -(9 rows) - -select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; - id | x | f1 -----+---+---- -(0 rows) - -drop index patest2i; -explain (costs off, num_nodes off, nodes off) -select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; - QUERY PLAN ---------------------------------------------------------------------- - Hash Join - Hash Cond: (pg_temp_3.patest0.id = int4_tbl.f1) - -> Append - -> Data Node Scan on patest0 "_REMOTE_TABLE_QUERY_" - -> Data Node Scan on patest1 "_REMOTE_TABLE_QUERY_" - -> Data Node Scan on patest2 "_REMOTE_TABLE_QUERY_" - -> Hash - -> Limit - -> Data Node Scan on int4_tbl "_REMOTE_TABLE_QUERY_" -(9 rows) - -select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; - id | x | f1 -----+---+---- -(0 rows) - -drop table patest0 cascade; -NOTICE: drop cascades to 2 other objects -DETAIL: drop cascades to table patest1 -drop cascades to table patest2 --- --- Test merge-append plans for inheritance trees --- -create table matest0 (id serial primary key, name text); -NOTICE: CREATE TABLE will create implicit sequence "matest0_id_seq" for serial column "matest0.id" -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "matest0_pkey" for table "matest0" -create table matest1 (id integer primary key) inherits (matest0); -NOTICE: merging column "id" with inherited definition -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "matest1_pkey" for table "matest1" -create table matest2 (id integer primary key) inherits (matest0); -NOTICE: merging column "id" with inherited definition -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "matest2_pkey" for table "matest2" -create table matest3 (id integer primary key) inherits (matest0); -NOTICE: merging column "id" with inherited definition -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "matest3_pkey" for table "matest3" -create index matest0i on matest0 ((1-id)); -create index matest1i on matest1 ((1-id)); --- create index matest2i on matest2 ((1-id)); -- intentionally missing -create index matest3i on matest3 ((1-id)); -insert into matest1 (name) values ('Test 1'); -insert into matest1 (name) values ('Test 2'); -insert into matest2 (name) values ('Test 3'); -insert into matest2 (name) values ('Test 4'); -insert into matest3 (name) values ('Test 5'); -insert into matest3 (name) values ('Test 6'); -set enable_indexscan = off; -- force use of seqscan/sort, so no merge -explain (verbose, costs off, nodes off) select * from matest0 order by 1-id; - QUERY PLAN ----------------------------------------------------------------------------------------- - Sort - Output: public.matest0.id, public.matest0.name, ((1 - public.matest0.id)) - Sort Key: ((1 - public.matest0.id)) - -> Result - Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id) - -> Append - -> Data Node Scan on matest0 "_REMOTE_TABLE_QUERY_" - Output: public.matest0.id, public.matest0.name - Remote query: SELECT id, name FROM ONLY matest0 WHERE true - -> Data Node Scan on matest1 "_REMOTE_TABLE_QUERY_" - Output: public.matest0.id, public.matest0.name - Remote query: SELECT id, name FROM ONLY matest1 matest0 WHERE true - -> Data Node Scan on matest2 "_REMOTE_TABLE_QUERY_" - Output: public.matest0.id, public.matest0.name - Remote query: SELECT id, name FROM ONLY matest2 matest0 WHERE true - -> Data Node Scan on matest3 "_REMOTE_TABLE_QUERY_" - Output: public.matest0.id, public.matest0.name - Remote query: SELECT id, name FROM ONLY matest3 matest0 WHERE true -(18 rows) - -select * from matest0 order by 1-id; - id | name -----+-------- - 6 | Test 6 - 5 | Test 5 - 4 | Test 4 - 3 | Test 3 - 2 | Test 2 - 1 | Test 1 -(6 rows) - -reset enable_indexscan; -set enable_seqscan = off; -- plan with fewest seqscans should be merge -explain (verbose, costs off, nodes off) select * from matest0 order by 1-id; - QUERY PLAN ----------------------------------------------------------------------------------------- - Sort - Output: public.matest0.id, public.matest0.name, ((1 - public.matest0.id)) - Sort Key: ((1 - public.matest0.id)) - -> Result - Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id) - -> Append - -> Data Node Scan on matest0 "_REMOTE_TABLE_QUERY_" - Output: public.matest0.id, public.matest0.name - Remote query: SELECT id, name FROM ONLY matest0 WHERE true - -> Data Node Scan on matest1 "_REMOTE_TABLE_QUERY_" - Output: public.matest0.id, public.matest0.name - Remote query: SELECT id, name FROM ONLY matest1 matest0 WHERE true - -> Data Node Scan on matest2 "_REMOTE_TABLE_QUERY_" - Output: public.matest0.id, public.matest0.name - Remote query: SELECT id, name FROM ONLY matest2 matest0 WHERE true - -> Data Node Scan on matest3 "_REMOTE_TABLE_QUERY_" - Output: public.matest0.id, public.matest0.name - Remote query: SELECT id, name FROM ONLY matest3 matest0 WHERE true -(18 rows) - -select * from matest0 order by 1-id; - id | name -----+-------- - 6 | Test 6 - 5 | Test 5 - 4 | Test 4 - 3 | Test 3 - 2 | Test 2 - 1 | Test 1 -(6 rows) - -reset enable_seqscan; -drop table matest0 cascade; -NOTICE: drop cascades to 3 other objects -DETAIL: drop cascades to table matest1 -drop cascades to table matest2 -drop cascades to table matest3 --- --- Test merge-append for UNION ALL append relations --- -set enable_seqscan = off; -set enable_indexscan = on; -set enable_bitmapscan = off; --- Check handling of duplicated, constant, or volatile targetlist items -explain (costs off, num_nodes off, nodes off) -SELECT thousand, tenthous FROM tenk1 -UNION ALL -SELECT thousand, thousand FROM tenk1 -ORDER BY thousand, tenthous; - QUERY PLAN ------------------------------------------------------------------- - Sort - Sort Key: public.tenk1.thousand, public.tenk1.tenthous - -> Result - -> Append - -> Data Node Scan on tenk1 "_REMOTE_TABLE_QUERY_" - -> Data Node Scan on tenk1 "_REMOTE_TABLE_QUERY_" -(6 rows) - -explain (costs off, num_nodes off, nodes off) -SELECT thousand, tenthous, thousand+tenthous AS x FROM tenk1 -UNION ALL -SELECT 42, 42, hundred FROM tenk1 -ORDER BY thousand, tenthous; - QUERY PLAN ------------------------------------------------------------------- - Sort - Sort Key: public.tenk1.thousand, public.tenk1.tenthous - -> Result - -> Append - -> Data Node Scan on tenk1 "_REMOTE_TABLE_QUERY_" - -> Data Node Scan on tenk1 "_REMOTE_TABLE_QUERY_" -(6 rows) - -explain (costs off, num_nodes off, nodes off) -SELECT thousand, tenthous FROM tenk1 -UNION ALL -SELECT thousand, random()::integer FROM tenk1 -ORDER BY thousand, tenthous; - QUERY PLAN ------------------------------------------------------------------- - Sort - Sort Key: public.tenk1.thousand, public.tenk1.tenthous - -> Result - -> Append - -> Data Node Scan on tenk1 "_REMOTE_TABLE_QUERY_" - -> Data Node Scan on tenk1 "_REMOTE_TABLE_QUERY_" -(6 rows) - --- Check min/max aggregate optimization -explain (costs off, num_nodes off, nodes off) -SELECT min(x) FROM - (SELECT unique1 AS x FROM tenk1 a - UNION ALL - SELECT unique2 AS x FROM tenk1 b) s; - QUERY PLAN ------------------------------------------------------------- - Aggregate - -> Append - -> Data Node Scan on tenk1 "_REMOTE_TABLE_QUERY_" - -> Data Node Scan on tenk1 "_REMOTE_TABLE_QUERY_" -(4 rows) - -explain (costs off, num_nodes off, nodes off) -SELECT min(y) FROM - (SELECT unique1 AS x, unique1 AS y FROM tenk1 a - UNION ALL - SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s; - QUERY PLAN ------------------------------------------------------------- - Aggregate - -> Append - -> Data Node Scan on tenk1 "_REMOTE_TABLE_QUERY_" - -> Data Node Scan on tenk1 "_REMOTE_TABLE_QUERY_" -(4 rows) - --- XXX planner doesn't recognize that index on unique2 is sufficiently sorted -explain (costs off, num_nodes off, nodes off) -SELECT x, y FROM - (SELECT thousand AS x, tenthous AS y FROM tenk1 a - UNION ALL - SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s -ORDER BY x, y; - QUERY PLAN ------------------------------------------------------------------- - Sort - Sort Key: a.thousand, a.tenthous - -> Result - -> Append - -> Data Node Scan on tenk1 "_REMOTE_TABLE_QUERY_" - -> Data Node Scan on tenk1 "_REMOTE_TABLE_QUERY_" -(6 rows) - -reset enable_seqscan; -reset enable_indexscan; -reset enable_bitmapscan; |