2 -- Test inheritance features
4 CREATE TABLE a (aa TEXT);
5 CREATE TABLE b (bb TEXT) INHERITS (a);
6 CREATE TABLE c (cc TEXT) INHERITS (a);
7 CREATE TABLE d (dd TEXT) INHERITS (b,c,a);
8 NOTICE: merging multiple inherited definitions of column "aa"
9 NOTICE: merging multiple inherited definitions of column "aa"
10 INSERT INTO a(aa) VALUES('aaa');
11 INSERT INTO a(aa) VALUES('aaaa');
12 INSERT INTO a(aa) VALUES('aaaaa');
13 INSERT INTO a(aa) VALUES('aaaaaa');
14 INSERT INTO a(aa) VALUES('aaaaaaa');
15 INSERT INTO a(aa) VALUES('aaaaaaaa');
16 INSERT INTO b(aa) VALUES('bbb');
17 INSERT INTO b(aa) VALUES('bbbb');
18 INSERT INTO b(aa) VALUES('bbbbb');
19 INSERT INTO b(aa) VALUES('bbbbbb');
20 INSERT INTO b(aa) VALUES('bbbbbbb');
21 INSERT INTO b(aa) VALUES('bbbbbbbb');
22 INSERT INTO c(aa) VALUES('ccc');
23 INSERT INTO c(aa) VALUES('cccc');
24 INSERT INTO c(aa) VALUES('ccccc');
25 INSERT INTO c(aa) VALUES('cccccc');
26 INSERT INTO c(aa) VALUES('ccccccc');
27 INSERT INTO c(aa) VALUES('cccccccc');
28 INSERT INTO d(aa) VALUES('ddd');
29 INSERT INTO d(aa) VALUES('dddd');
30 INSERT INTO d(aa) VALUES('ddddd');
31 INSERT INTO d(aa) VALUES('dddddd');
32 INSERT INTO d(aa) VALUES('ddddddd');
33 INSERT INTO d(aa) VALUES('dddddddd');
34 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
63 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
65 ---------+----------+----
80 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
82 ---------+----------+----
97 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
98 relname | aa | bb | cc | dd
99 ---------+----------+----+----+----
108 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
119 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
121 ---------+----------+----
130 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
132 ---------+----------+----
141 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
142 relname | aa | bb | cc | dd
143 ---------+----------+----+----+----
152 UPDATE a SET aa='zzzz' WHERE aa='aaaa';
153 UPDATE ONLY a SET aa='zzzzz' WHERE aa='aaaaa';
154 UPDATE b SET aa='zzz' WHERE aa='aaa';
155 UPDATE ONLY b SET aa='zzz' WHERE aa='aaa';
156 UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';
157 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
186 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
188 ---------+----------+----
203 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
205 ---------+----------+----
220 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
221 relname | aa | bb | cc | dd
222 ---------+----------+----+----+----
231 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
242 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
244 ---------+----------+----
253 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
255 ---------+----------+----
264 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
265 relname | aa | bb | cc | dd
266 ---------+----------+----+----+----
275 UPDATE b SET aa='new';
276 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
305 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
322 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
324 ---------+----------+----
339 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
340 relname | aa | bb | cc | dd
341 ---------+-----+----+----+----
350 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
361 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
372 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
374 ---------+----------+----
383 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
384 relname | aa | bb | cc | dd
385 ---------+-----+----+----+----
394 UPDATE a SET aa='new';
395 DELETE FROM ONLY c WHERE aa='new';
396 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
419 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
436 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
447 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
448 relname | aa | bb | cc | dd
449 ---------+-----+----+----+----
458 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
469 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
480 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
485 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
486 relname | aa | bb | cc | dd
487 ---------+-----+----+----+----
497 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
502 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
507 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
512 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
513 relname | aa | bb | cc | dd
514 ---------+----+----+----+----
517 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
522 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
527 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
532 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
533 relname | aa | bb | cc | dd
534 ---------+----+----+----+----
537 -- Confirm PRIMARY KEY adds NOT NULL constraint to child table
538 CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a);
539 INSERT INTO z VALUES (NULL, 'text'); -- should fail
540 ERROR: null value in column "aa" violates not-null constraint
541 DETAIL: Failing row contains (null, text).
542 -- Check UPDATE with inherited target and an inherited source table
543 create temp table foo(f1 int, f2 int);
544 create temp table foo2(f3 int) inherits (foo);
545 create temp table bar(f1 int, f2 int);
546 create temp table bar2(f3 int) inherits (bar);
547 insert into foo values(1,1);
548 insert into foo values(3,3);
549 insert into foo2 values(2,2,2);
550 insert into foo2 values(3,3,3);
551 insert into bar values(1,1);
552 insert into bar values(2,2);
553 insert into bar values(3,3);
554 insert into bar values(4,4);
555 insert into bar2 values(1,1,1);
556 insert into bar2 values(2,2,2);
557 insert into bar2 values(3,3,3);
558 insert into bar2 values(4,4,4);
559 update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
560 select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
573 -- Check UPDATE with inherited target and an appendrel subquery
574 update bar set f2 = f2 + 100
576 ( select f1 from foo union all select f1+3 from foo ) ss
577 where bar.f1 = ss.f1;
578 select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
591 /* Test multiple inheritance of column defaults */
592 CREATE TABLE firstparent (tomorrow date default now()::date + 1);
593 CREATE TABLE secondparent (tomorrow date default now() :: date + 1);
594 CREATE TABLE jointchild () INHERITS (firstparent, secondparent); -- ok
595 NOTICE: merging multiple inherited definitions of column "tomorrow"
596 CREATE TABLE thirdparent (tomorrow date default now()::date - 1);
597 CREATE TABLE otherchild () INHERITS (firstparent, thirdparent); -- not ok
598 NOTICE: merging multiple inherited definitions of column "tomorrow"
599 ERROR: column "tomorrow" inherits conflicting default values
600 HINT: To resolve the conflict, specify a default explicitly.
601 CREATE TABLE otherchild (tomorrow date default now())
602 INHERITS (firstparent, thirdparent); -- ok, child resolves ambiguous default
603 NOTICE: merging multiple inherited definitions of column "tomorrow"
604 NOTICE: merging column "tomorrow" with inherited definition
605 DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild;
606 -- Test changing the type of inherited columns
607 insert into d values('test','one','two','three');
608 alter table a alter column aa type integer using bit_length(aa);
611 ----+-----+-----+-------
612 32 | one | two | three
615 -- Test non-inheritable parent constraints
616 create table p1(ff1 int);
617 alter table p1 add constraint p1chk check (ff1 > 0) no inherit;
618 alter table p1 add constraint p2chk check (ff1 > 10);
619 -- connoinherit should be true for NO INHERIT constraint
620 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;
621 relname | conname | contype | conislocal | coninhcount | connoinherit
622 ---------+---------+---------+------------+-------------+--------------
623 p1 | p1chk | c | t | 0 | t
624 p1 | p2chk | c | t | 0 | f
627 -- Test that child does not inherit NO INHERIT constraints
628 create table c1 () inherits (p1);
631 Column | Type | Modifiers
632 --------+---------+-----------
635 "p1chk" CHECK (ff1 > 0) NO INHERIT
636 "p2chk" CHECK (ff1 > 10)
637 Number of child tables: 1 (Use \d+ to list them.)
641 Column | Type | Modifiers
642 --------+---------+-----------
645 "p2chk" CHECK (ff1 > 10)
648 drop table p1 cascade;
649 NOTICE: drop cascades to table c1
650 -- Tests for casting between the rowtypes of parent and child
651 -- tables. See the pgsql-hackers thread beginning Dec. 4/04
652 create table base (i integer);
653 create table derived () inherits (base);
654 insert into derived (i) values (0);
655 select derived::base from derived;
663 create table p1(ff1 int);
664 create table p2(f1 text);
665 create function p2text(p2) returns text as 'select $1.f1' language sql;
666 create table c1(f3 int) inherits(p1,p2);
667 insert into c1 values(123456789, 'hi', 42);
668 select p2text(c1.*) from c1;
674 drop function p2text(p2);
678 CREATE TABLE ac (aa TEXT);
679 alter table ac add constraint ac_check check (aa is not null);
680 CREATE TABLE bc (bb TEXT) INHERITS (ac);
681 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;
682 relname | conname | contype | conislocal | coninhcount | consrc
683 ---------+----------+---------+------------+-------------+------------------
684 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
685 bc | ac_check | c | f | 1 | (aa IS NOT NULL)
688 insert into ac (aa) values (NULL);
689 ERROR: new row for relation "ac" violates check constraint "ac_check"
690 DETAIL: Failing row contains (null).
691 insert into bc (aa) values (NULL);
692 ERROR: new row for relation "bc" violates check constraint "ac_check"
693 DETAIL: Failing row contains (null, null).
694 alter table bc drop constraint ac_check; -- fail, disallowed
695 ERROR: cannot drop inherited constraint "ac_check" of relation "bc"
696 alter table ac drop constraint ac_check;
697 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;
698 relname | conname | contype | conislocal | coninhcount | consrc
699 ---------+---------+---------+------------+-------------+--------
702 -- try the unnamed-constraint case
703 alter table ac add check (aa is not null);
704 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;
705 relname | conname | contype | conislocal | coninhcount | consrc
706 ---------+-------------+---------+------------+-------------+------------------
707 ac | ac_aa_check | c | t | 0 | (aa IS NOT NULL)
708 bc | ac_aa_check | c | f | 1 | (aa IS NOT NULL)
711 insert into ac (aa) values (NULL);
712 ERROR: new row for relation "ac" violates check constraint "ac_aa_check"
713 DETAIL: Failing row contains (null).
714 insert into bc (aa) values (NULL);
715 ERROR: new row for relation "bc" violates check constraint "ac_aa_check"
716 DETAIL: Failing row contains (null, null).
717 alter table bc drop constraint ac_aa_check; -- fail, disallowed
718 ERROR: cannot drop inherited constraint "ac_aa_check" of relation "bc"
719 alter table ac drop constraint ac_aa_check;
720 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;
721 relname | conname | contype | conislocal | coninhcount | consrc
722 ---------+---------+---------+------------+-------------+--------
725 alter table ac add constraint ac_check check (aa is not null);
726 alter table bc no inherit ac;
727 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;
728 relname | conname | contype | conislocal | coninhcount | consrc
729 ---------+----------+---------+------------+-------------+------------------
730 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
731 bc | ac_check | c | t | 0 | (aa IS NOT NULL)
734 alter table bc drop constraint ac_check;
735 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;
736 relname | conname | contype | conislocal | coninhcount | consrc
737 ---------+----------+---------+------------+-------------+------------------
738 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
741 alter table ac drop constraint ac_check;
742 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;
743 relname | conname | contype | conislocal | coninhcount | consrc
744 ---------+---------+---------+------------+-------------+--------
749 create table ac (a int constraint check_a check (a <> 0));
750 create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac);
751 NOTICE: merging column "a" with inherited definition
752 NOTICE: merging constraint "check_a" with inherited definition
753 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;
754 relname | conname | contype | conislocal | coninhcount | consrc
755 ---------+---------+---------+------------+-------------+----------
756 ac | check_a | c | t | 0 | (a <> 0)
757 bc | check_a | c | t | 1 | (a <> 0)
758 bc | check_b | c | t | 0 | (b <> 0)
763 create table ac (a int constraint check_a check (a <> 0));
764 create table bc (b int constraint check_b check (b <> 0));
765 create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc);
766 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;
767 relname | conname | contype | conislocal | coninhcount | consrc
768 ---------+---------+---------+------------+-------------+----------
769 ac | check_a | c | t | 0 | (a <> 0)
770 bc | check_b | c | t | 0 | (b <> 0)
771 cc | check_a | c | f | 1 | (a <> 0)
772 cc | check_b | c | f | 1 | (b <> 0)
773 cc | check_c | c | t | 0 | (c <> 0)
776 alter table cc no inherit bc;
777 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;
778 relname | conname | contype | conislocal | coninhcount | consrc
779 ---------+---------+---------+------------+-------------+----------
780 ac | check_a | c | t | 0 | (a <> 0)
781 bc | check_b | c | t | 0 | (b <> 0)
782 cc | check_a | c | f | 1 | (a <> 0)
783 cc | check_b | c | t | 0 | (b <> 0)
784 cc | check_c | c | t | 0 | (c <> 0)
790 create table p1(f1 int);
791 create table p2(f2 int);
792 create table c1(f3 int) inherits(p1,p2);
793 insert into c1 values(1,-1,2);
794 alter table p2 add constraint cc check (f2>0); -- fail
795 ERROR: check constraint "cc" is violated by some row
796 alter table p2 add check (f2>0); -- check it without a name, too
797 ERROR: check constraint "p2_f2_check" is violated by some row
799 insert into c1 values(1,1,2);
800 alter table p2 add check (f2>0);
801 insert into c1 values(1,-1,2); -- fail
802 ERROR: new row for relation "c1" violates check constraint "p2_f2_check"
803 DETAIL: Failing row contains (1, -1, 2).
804 create table c2(f3 int) inherits(p1,p2);
807 Column | Type | Modifiers
808 --------+---------+-----------
813 "p2_f2_check" CHECK (f2 > 0)
817 create table c3 (f4 int) inherits(c1,c2);
818 NOTICE: merging multiple inherited definitions of column "f1"
819 NOTICE: merging multiple inherited definitions of column "f2"
820 NOTICE: merging multiple inherited definitions of column "f3"
823 Column | Type | Modifiers
824 --------+---------+-----------
830 "p2_f2_check" CHECK (f2 > 0)
834 drop table p1 cascade;
835 NOTICE: drop cascades to 3 other objects
836 DETAIL: drop cascades to table c1
837 drop cascades to table c2
838 drop cascades to table c3
839 drop table p2 cascade;
840 create table pp1 (f1 int);
841 create table cc1 (f2 text, f3 int) inherits (pp1);
842 alter table pp1 add column a1 int check (a1 > 0);
845 Column | Type | Modifiers
846 --------+---------+-----------
852 "pp1_a1_check" CHECK (a1 > 0)
855 create table cc2(f4 float) inherits(pp1,cc1);
856 NOTICE: merging multiple inherited definitions of column "f1"
857 NOTICE: merging multiple inherited definitions of column "a1"
860 Column | Type | Modifiers
861 --------+------------------+-----------
866 f4 | double precision |
868 "pp1_a1_check" CHECK (a1 > 0)
872 alter table pp1 add column a2 int check (a2 > 0);
873 NOTICE: merging definition of column "a2" for child "cc2"
874 NOTICE: merging constraint "pp1_a2_check" with inherited definition
877 Column | Type | Modifiers
878 --------+------------------+-----------
883 f4 | double precision |
886 "pp1_a1_check" CHECK (a1 > 0)
887 "pp1_a2_check" CHECK (a2 > 0)
891 drop table pp1 cascade;
892 NOTICE: drop cascades to 2 other objects
893 DETAIL: drop cascades to table cc1
894 drop cascades to table cc2
895 -- Test for renaming in simple multiple inheritance
896 CREATE TABLE inht1 (a int, b int);
897 CREATE TABLE inhs1 (b int, c int);
898 CREATE TABLE inhts (d int) INHERITS (inht1, inhs1);
899 NOTICE: merging multiple inherited definitions of column "b"
900 ALTER TABLE inht1 RENAME a TO aa;
901 ALTER TABLE inht1 RENAME b TO bb; -- to be failed
902 ERROR: cannot rename inherited column "b"
903 ALTER TABLE inhts RENAME aa TO aaa; -- to be failed
904 ERROR: cannot rename inherited column "aa"
905 ALTER TABLE inhts RENAME d TO dd;
908 Column | Type | Modifiers | Storage | Stats target | Description
909 --------+---------+-----------+---------+--------------+-------------
910 aa | integer | | plain | |
911 b | integer | | plain | |
912 c | integer | | plain | |
913 dd | integer | | plain | |
916 Replica Identity: DEFAULT
920 -- Test for renaming in diamond inheritance
921 CREATE TABLE inht2 (x int) INHERITS (inht1);
922 CREATE TABLE inht3 (y int) INHERITS (inht1);
923 CREATE TABLE inht4 (z int) INHERITS (inht2, inht3);
924 NOTICE: merging multiple inherited definitions of column "aa"
925 NOTICE: merging multiple inherited definitions of column "b"
926 ALTER TABLE inht1 RENAME aa TO aaa;
929 Column | Type | Modifiers | Storage | Stats target | Description
930 --------+---------+-----------+---------+--------------+-------------
931 aaa | integer | | plain | |
932 b | integer | | plain | |
933 x | integer | | plain | |
934 y | integer | | plain | |
935 z | integer | | plain | |
938 Replica Identity: DEFAULT
941 CREATE TABLE inhts (d int) INHERITS (inht2, inhs1);
942 NOTICE: merging multiple inherited definitions of column "b"
943 ALTER TABLE inht1 RENAME aaa TO aaaa;
944 ALTER TABLE inht1 RENAME b TO bb; -- to be failed
945 ERROR: cannot rename inherited column "b"
948 Column | Type | Modifiers | Storage | Stats target | Description
949 --------+---------+-----------+---------+--------------+-------------
950 aaaa | integer | | plain | |
951 b | integer | | plain | |
952 x | integer | | plain | |
953 c | integer | | plain | |
954 d | integer | | plain | |
957 Replica Identity: DEFAULT
960 WITH RECURSIVE r AS (
961 SELECT 'inht1'::regclass AS inhrelid
963 SELECT c.inhrelid FROM pg_inherits c, r WHERE r.inhrelid = c.inhparent
965 SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected
966 FROM (SELECT inhrelid, count(*) AS expected FROM pg_inherits
967 WHERE inhparent IN (SELECT inhrelid FROM r) GROUP BY inhrelid) e
968 JOIN pg_attribute a ON e.inhrelid = a.attrelid WHERE NOT attislocal
969 ORDER BY a.attrelid::regclass::name, a.attnum;
970 attrelid | attname | attinhcount | expected
971 ----------+---------+-------------+----------
986 DROP TABLE inht1, inhs1 CASCADE;
987 NOTICE: drop cascades to 4 other objects
988 DETAIL: drop cascades to table inht2
989 drop cascades to table inhts
990 drop cascades to table inht3
991 drop cascades to table inht4
992 -- Test non-inheritable indices [UNIQUE, EXCLUDE] contraints
993 CREATE TABLE test_constraints (id int, val1 varchar, val2 int, UNIQUE(val1, val2));
994 CREATE TABLE test_constraints_inh () INHERITS (test_constraints);
996 Table "public.test_constraints"
997 Column | Type | Modifiers | Storage | Stats target | Description
998 --------+-------------------+-----------+----------+--------------+-------------
999 id | integer | | plain | |
1000 val1 | character varying | | extended | |
1001 val2 | integer | | plain | |
1003 "test_constraints_val1_val2_key" UNIQUE CONSTRAINT, btree (val1, val2)
1004 Child tables: test_constraints_inh
1005 Replica Identity: DEFAULT
1008 ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key;
1009 \d+ test_constraints
1010 Table "public.test_constraints"
1011 Column | Type | Modifiers | Storage | Stats target | Description
1012 --------+-------------------+-----------+----------+--------------+-------------
1013 id | integer | | plain | |
1014 val1 | character varying | | extended | |
1015 val2 | integer | | plain | |
1016 Child tables: test_constraints_inh
1017 Replica Identity: DEFAULT
1020 \d+ test_constraints_inh
1021 Table "public.test_constraints_inh"
1022 Column | Type | Modifiers | Storage | Stats target | Description
1023 --------+-------------------+-----------+----------+--------------+-------------
1024 id | integer | | plain | |
1025 val1 | character varying | | extended | |
1026 val2 | integer | | plain | |
1027 Inherits: test_constraints
1028 Replica Identity: DEFAULT
1031 DROP TABLE test_constraints_inh;
1032 DROP TABLE test_constraints;
1033 CREATE TABLE test_ex_constraints (
1035 EXCLUDE USING gist (c WITH &&)
1037 CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints);
1038 \d+ test_ex_constraints
1039 Table "public.test_ex_constraints"
1040 Column | Type | Modifiers | Storage | Stats target | Description
1041 --------+--------+-----------+---------+--------------+-------------
1042 c | circle | | plain | |
1044 "test_ex_constraints_c_excl" EXCLUDE USING gist (c WITH &&)
1045 Child tables: test_ex_constraints_inh
1046 Replica Identity: DEFAULT
1049 ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl;
1050 \d+ test_ex_constraints
1051 Table "public.test_ex_constraints"
1052 Column | Type | Modifiers | Storage | Stats target | Description
1053 --------+--------+-----------+---------+--------------+-------------
1054 c | circle | | plain | |
1055 Child tables: test_ex_constraints_inh
1056 Replica Identity: DEFAULT
1059 \d+ test_ex_constraints_inh
1060 Table "public.test_ex_constraints_inh"
1061 Column | Type | Modifiers | Storage | Stats target | Description
1062 --------+--------+-----------+---------+--------------+-------------
1063 c | circle | | plain | |
1064 Inherits: test_ex_constraints
1065 Replica Identity: DEFAULT
1068 DROP TABLE test_ex_constraints_inh;
1069 DROP TABLE test_ex_constraints;
1070 -- Test non-inheritable foreign key contraints
1071 CREATE TABLE test_primary_constraints(id int PRIMARY KEY);
1072 CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id));
1073 CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints);
1074 \d+ test_primary_constraints
1075 Table "public.test_primary_constraints"
1076 Column | Type | Modifiers | Storage | Stats target | Description
1077 --------+---------+-----------+---------+--------------+-------------
1078 id | integer | not null | plain | |
1080 "test_primary_constraints_pkey" PRIMARY KEY, btree (id)
1082 TABLE "test_foreign_constraints" CONSTRAINT "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
1083 Replica Identity: DEFAULT
1086 \d+ test_foreign_constraints
1087 Table "public.test_foreign_constraints"
1088 Column | Type | Modifiers | Storage | Stats target | Description
1089 --------+---------+-----------+---------+--------------+-------------
1090 id1 | integer | | plain | |
1091 Foreign-key constraints:
1092 "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
1093 Child tables: test_foreign_constraints_inh
1094 Replica Identity: DEFAULT
1097 ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey;
1098 \d+ test_foreign_constraints
1099 Table "public.test_foreign_constraints"
1100 Column | Type | Modifiers | Storage | Stats target | Description
1101 --------+---------+-----------+---------+--------------+-------------
1102 id1 | integer | | plain | |
1103 Child tables: test_foreign_constraints_inh
1104 Replica Identity: DEFAULT
1107 \d+ test_foreign_constraints_inh
1108 Table "public.test_foreign_constraints_inh"
1109 Column | Type | Modifiers | Storage | Stats target | Description
1110 --------+---------+-----------+---------+--------------+-------------
1111 id1 | integer | | plain | |
1112 Inherits: test_foreign_constraints
1113 Replica Identity: DEFAULT
1116 DROP TABLE test_foreign_constraints_inh;
1117 DROP TABLE test_foreign_constraints;
1118 DROP TABLE test_primary_constraints;
1120 -- Test parameterized append plans for inheritance trees
1122 create temp table patest0 (id, x) as
1123 select x, x from generate_series(0,1000) x;
1124 create temp table patest1() inherits (patest0);
1126 select x, x from generate_series(0,1000) x;
1127 create temp table patest2() inherits (patest0);
1129 select x, x from generate_series(0,1000) x;
1130 create index patest0i on patest0(id);
1131 create index patest1i on patest1(id);
1132 create index patest2i on patest2(id);
1137 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1139 --------------------------------------------------
1142 -> Seq Scan on int4_tbl
1144 -> Index Scan using patest0i on patest0
1145 Index Cond: (id = int4_tbl.f1)
1146 -> Index Scan using patest1i on patest1
1147 Index Cond: (id = int4_tbl.f1)
1148 -> Index Scan using patest2i on patest2
1149 Index Cond: (id = int4_tbl.f1)
1152 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1160 drop index patest2i;
1162 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1164 --------------------------------------------------
1167 -> Seq Scan on int4_tbl
1169 -> Index Scan using patest0i on patest0
1170 Index Cond: (id = int4_tbl.f1)
1171 -> Index Scan using patest1i on patest1
1172 Index Cond: (id = int4_tbl.f1)
1173 -> Seq Scan on patest2
1174 Filter: (int4_tbl.f1 = id)
1177 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1185 drop table patest0 cascade;
1186 NOTICE: drop cascades to 2 other objects
1187 DETAIL: drop cascades to table patest1
1188 drop cascades to table patest2
1190 -- Test merge-append plans for inheritance trees
1192 create table matest0 (id serial primary key, name text);
1193 create table matest1 (id integer primary key) inherits (matest0);
1194 NOTICE: merging column "id" with inherited definition
1195 create table matest2 (id integer primary key) inherits (matest0);
1196 NOTICE: merging column "id" with inherited definition
1197 create table matest3 (id integer primary key) inherits (matest0);
1198 NOTICE: merging column "id" with inherited definition
1199 create index matest0i on matest0 ((1-id));
1200 create index matest1i on matest1 ((1-id));
1201 -- create index matest2i on matest2 ((1-id)); -- intentionally missing
1202 create index matest3i on matest3 ((1-id));
1203 insert into matest1 (name) values ('Test 1');
1204 insert into matest1 (name) values ('Test 2');
1205 insert into matest2 (name) values ('Test 3');
1206 insert into matest2 (name) values ('Test 4');
1207 insert into matest3 (name) values ('Test 5');
1208 insert into matest3 (name) values ('Test 6');
1209 set enable_indexscan = off; -- force use of seqscan/sort, so no merge
1210 explain (verbose, costs off) select * from matest0 order by 1-id;
1212 ------------------------------------------------------------
1214 Output: matest0.id, matest0.name, ((1 - matest0.id))
1215 Sort Key: ((1 - matest0.id))
1217 Output: matest0.id, matest0.name, (1 - matest0.id)
1219 -> Seq Scan on public.matest0
1220 Output: matest0.id, matest0.name
1221 -> Seq Scan on public.matest1
1222 Output: matest1.id, matest1.name
1223 -> Seq Scan on public.matest2
1224 Output: matest2.id, matest2.name
1225 -> Seq Scan on public.matest3
1226 Output: matest3.id, matest3.name
1229 select * from matest0 order by 1-id;
1240 explain (verbose, costs off) select min(1-id) from matest0;
1242 ----------------------------------------
1244 Output: min((1 - matest0.id))
1246 -> Seq Scan on public.matest0
1248 -> Seq Scan on public.matest1
1250 -> Seq Scan on public.matest2
1252 -> Seq Scan on public.matest3
1256 select min(1-id) from matest0;
1262 reset enable_indexscan;
1263 set enable_seqscan = off; -- plan with fewest seqscans should be merge
1264 explain (verbose, costs off) select * from matest0 order by 1-id;
1266 ------------------------------------------------------------------
1268 Sort Key: ((1 - matest0.id))
1269 -> Index Scan using matest0i on public.matest0
1270 Output: matest0.id, matest0.name, (1 - matest0.id)
1271 -> Index Scan using matest1i on public.matest1
1272 Output: matest1.id, matest1.name, (1 - matest1.id)
1274 Output: matest2.id, matest2.name, ((1 - matest2.id))
1275 Sort Key: ((1 - matest2.id))
1276 -> Seq Scan on public.matest2
1277 Output: matest2.id, matest2.name, (1 - matest2.id)
1278 -> Index Scan using matest3i on public.matest3
1279 Output: matest3.id, matest3.name, (1 - matest3.id)
1282 select * from matest0 order by 1-id;
1293 explain (verbose, costs off) select min(1-id) from matest0;
1295 --------------------------------------------------------------------------
1298 InitPlan 1 (returns $0)
1300 Output: ((1 - matest0.id))
1302 Output: ((1 - matest0.id))
1304 Sort Key: ((1 - matest0.id))
1305 -> Index Scan using matest0i on public.matest0
1306 Output: matest0.id, (1 - matest0.id)
1307 Index Cond: ((1 - matest0.id) IS NOT NULL)
1308 -> Index Scan using matest1i on public.matest1
1309 Output: matest1.id, (1 - matest1.id)
1310 Index Cond: ((1 - matest1.id) IS NOT NULL)
1312 Output: matest2.id, ((1 - matest2.id))
1313 Sort Key: ((1 - matest2.id))
1314 -> Bitmap Heap Scan on public.matest2
1315 Output: matest2.id, (1 - matest2.id)
1316 Filter: ((1 - matest2.id) IS NOT NULL)
1317 -> Bitmap Index Scan on matest2_pkey
1318 -> Index Scan using matest3i on public.matest3
1319 Output: matest3.id, (1 - matest3.id)
1320 Index Cond: ((1 - matest3.id) IS NOT NULL)
1323 select min(1-id) from matest0;
1329 reset enable_seqscan;
1330 drop table matest0 cascade;
1331 NOTICE: drop cascades to 3 other objects
1332 DETAIL: drop cascades to table matest1
1333 drop cascades to table matest2
1334 drop cascades to table matest3
1336 -- Test merge-append for UNION ALL append relations
1338 set enable_seqscan = off;
1339 set enable_indexscan = on;
1340 set enable_bitmapscan = off;
1341 -- Check handling of duplicated, constant, or volatile targetlist items
1343 SELECT thousand, tenthous FROM tenk1
1345 SELECT thousand, thousand FROM tenk1
1346 ORDER BY thousand, tenthous;
1348 -------------------------------------------------------------------------
1350 Sort Key: tenk1.thousand, tenk1.tenthous
1351 -> Index Only Scan using tenk1_thous_tenthous on tenk1
1353 Sort Key: tenk1_1.thousand, tenk1_1.thousand
1354 -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
1358 SELECT thousand, tenthous, thousand+tenthous AS x FROM tenk1
1360 SELECT 42, 42, hundred FROM tenk1
1361 ORDER BY thousand, tenthous;
1363 ------------------------------------------------------------------
1365 Sort Key: tenk1.thousand, tenk1.tenthous
1366 -> Index Only Scan using tenk1_thous_tenthous on tenk1
1368 Sort Key: (42), (42)
1369 -> Index Only Scan using tenk1_hundred on tenk1 tenk1_1
1373 SELECT thousand, tenthous FROM tenk1
1375 SELECT thousand, random()::integer FROM tenk1
1376 ORDER BY thousand, tenthous;
1378 -------------------------------------------------------------------------
1380 Sort Key: tenk1.thousand, tenk1.tenthous
1381 -> Index Only Scan using tenk1_thous_tenthous on tenk1
1383 Sort Key: tenk1_1.thousand, ((random())::integer)
1384 -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
1387 -- Check min/max aggregate optimization
1390 (SELECT unique1 AS x FROM tenk1 a
1392 SELECT unique2 AS x FROM tenk1 b) s;
1394 --------------------------------------------------------------------
1396 InitPlan 1 (returns $0)
1400 -> Index Only Scan using tenk1_unique1 on tenk1 a
1401 Index Cond: (unique1 IS NOT NULL)
1402 -> Index Only Scan using tenk1_unique2 on tenk1 b
1403 Index Cond: (unique2 IS NOT NULL)
1408 (SELECT unique1 AS x, unique1 AS y FROM tenk1 a
1410 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s;
1412 --------------------------------------------------------------------
1414 InitPlan 1 (returns $0)
1418 -> Index Only Scan using tenk1_unique1 on tenk1 a
1419 Index Cond: (unique1 IS NOT NULL)
1420 -> Index Only Scan using tenk1_unique2 on tenk1 b
1421 Index Cond: (unique2 IS NOT NULL)
1424 -- XXX planner doesn't recognize that index on unique2 is sufficiently sorted
1427 (SELECT thousand AS x, tenthous AS y FROM tenk1 a
1429 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s
1432 -------------------------------------------------------------
1434 Sort Key: a.thousand, a.tenthous
1435 -> Index Only Scan using tenk1_thous_tenthous on tenk1 a
1437 Sort Key: b.unique2, b.unique2
1438 -> Index Only Scan using tenk1_unique2 on tenk1 b
1441 -- exercise rescan code path via a repeatedly-evaluated subquery
1444 ARRAY(SELECT f.i FROM (
1445 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1)
1447 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1)
1449 ORDER BY f.i LIMIT 10)
1450 FROM generate_series(1, 3) g(i);
1452 ----------------------------------------------------------------
1453 Function Scan on generate_series g
1457 Sort Key: ((d.d + g.i))
1459 Sort Key: ((d.d + g.i))
1460 -> Function Scan on generate_series d
1462 Sort Key: ((d_1.d + g.i))
1463 -> Function Scan on generate_series d_1
1467 ARRAY(SELECT f.i FROM (
1468 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1)
1470 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1)
1472 ORDER BY f.i LIMIT 10)
1473 FROM generate_series(1, 3) g(i);
1475 ------------------------------
1476 {1,5,6,8,11,11,14,16,17,20}
1477 {2,6,7,9,12,12,15,17,18,21}
1478 {3,7,8,10,13,13,16,18,19,22}
1481 reset enable_seqscan;
1482 reset enable_indexscan;
1483 reset enable_bitmapscan;
projects / postgresql / blob