| blob | bb81f6d2b4d13ea2e1c58b322c8b97c5e0bf896a |
1 --
2 -- Test inheritance features
3 --
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;
35 relname | aa
36 ---------+----------
37 a | aaa
38 a | aaaa
39 a | aaaaa
40 a | aaaaaa
41 a | aaaaaaa
42 a | aaaaaaaa
43 b | bbb
44 b | bbbb
45 b | bbbbb
46 b | bbbbbb
47 b | bbbbbbb
48 b | bbbbbbbb
49 c | ccc
50 c | cccc
51 c | ccccc
52 c | cccccc
53 c | ccccccc
54 c | cccccccc
55 d | ddd
56 d | dddd
57 d | ddddd
58 d | dddddd
59 d | ddddddd
60 d | dddddddd
61 (24 rows)
63 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
64 relname | aa | bb
65 ---------+----------+----
66 b | bbb |
67 b | bbbb |
68 b | bbbbb |
69 b | bbbbbb |
70 b | bbbbbbb |
71 b | bbbbbbbb |
72 d | ddd |
73 d | dddd |
74 d | ddddd |
75 d | dddddd |
76 d | ddddddd |
77 d | dddddddd |
78 (12 rows)
80 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
81 relname | aa | cc
82 ---------+----------+----
83 c | ccc |
84 c | cccc |
85 c | ccccc |
86 c | cccccc |
87 c | ccccccc |
88 c | cccccccc |
89 d | ddd |
90 d | dddd |
91 d | ddddd |
92 d | dddddd |
93 d | ddddddd |
94 d | dddddddd |
95 (12 rows)
97 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
98 relname | aa | bb | cc | dd
99 ---------+----------+----+----+----
100 d | ddd | | |
101 d | dddd | | |
102 d | ddddd | | |
103 d | dddddd | | |
104 d | ddddddd | | |
105 d | dddddddd | | |
106 (6 rows)
108 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
109 relname | aa
110 ---------+----------
111 a | aaa
112 a | aaaa
113 a | aaaaa
114 a | aaaaaa
115 a | aaaaaaa
116 a | aaaaaaaa
117 (6 rows)
119 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
120 relname | aa | bb
121 ---------+----------+----
122 b | bbb |
123 b | bbbb |
124 b | bbbbb |
125 b | bbbbbb |
126 b | bbbbbbb |
127 b | bbbbbbbb |
128 (6 rows)
130 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
131 relname | aa | cc
132 ---------+----------+----
133 c | ccc |
134 c | cccc |
135 c | ccccc |
136 c | cccccc |
137 c | ccccccc |
138 c | cccccccc |
139 (6 rows)
141 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
142 relname | aa | bb | cc | dd
143 ---------+----------+----+----+----
144 d | ddd | | |
145 d | dddd | | |
146 d | ddddd | | |
147 d | dddddd | | |
148 d | ddddddd | | |
149 d | dddddddd | | |
150 (6 rows)
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;
158 relname | aa
159 ---------+----------
160 a | zzzz
161 a | zzzzz
162 a | zzzzzz
163 a | zzzzzz
164 a | zzzzzz
165 a | zzzzzz
166 b | bbb
167 b | bbbb
168 b | bbbbb
169 b | bbbbbb
170 b | bbbbbbb
171 b | bbbbbbbb
172 c | ccc
173 c | cccc
174 c | ccccc
175 c | cccccc
176 c | ccccccc
177 c | cccccccc
178 d | ddd
179 d | dddd
180 d | ddddd
181 d | dddddd
182 d | ddddddd
183 d | dddddddd
184 (24 rows)
186 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
187 relname | aa | bb
188 ---------+----------+----
189 b | bbb |
190 b | bbbb |
191 b | bbbbb |
192 b | bbbbbb |
193 b | bbbbbbb |
194 b | bbbbbbbb |
195 d | ddd |
196 d | dddd |
197 d | ddddd |
198 d | dddddd |
199 d | ddddddd |
200 d | dddddddd |
201 (12 rows)
203 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
204 relname | aa | cc
205 ---------+----------+----
206 c | ccc |
207 c | cccc |
208 c | ccccc |
209 c | cccccc |
210 c | ccccccc |
211 c | cccccccc |
212 d | ddd |
213 d | dddd |
214 d | ddddd |
215 d | dddddd |
216 d | ddddddd |
217 d | dddddddd |
218 (12 rows)
220 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
221 relname | aa | bb | cc | dd
222 ---------+----------+----+----+----
223 d | ddd | | |
224 d | dddd | | |
225 d | ddddd | | |
226 d | dddddd | | |
227 d | ddddddd | | |
228 d | dddddddd | | |
229 (6 rows)
231 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
232 relname | aa
233 ---------+--------
234 a | zzzz
235 a | zzzzz
236 a | zzzzzz
237 a | zzzzzz
238 a | zzzzzz
239 a | zzzzzz
240 (6 rows)
242 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
243 relname | aa | bb
244 ---------+----------+----
245 b | bbb |
246 b | bbbb |
247 b | bbbbb |
248 b | bbbbbb |
249 b | bbbbbbb |
250 b | bbbbbbbb |
251 (6 rows)
253 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
254 relname | aa | cc
255 ---------+----------+----
256 c | ccc |
257 c | cccc |
258 c | ccccc |
259 c | cccccc |
260 c | ccccccc |
261 c | cccccccc |
262 (6 rows)
264 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
265 relname | aa | bb | cc | dd
266 ---------+----------+----+----+----
267 d | ddd | | |
268 d | dddd | | |
269 d | ddddd | | |
270 d | dddddd | | |
271 d | ddddddd | | |
272 d | dddddddd | | |
273 (6 rows)
275 UPDATE b SET aa='new';
276 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
277 relname | aa
278 ---------+----------
279 a | zzzz
280 a | zzzzz
281 a | zzzzzz
282 a | zzzzzz
283 a | zzzzzz
284 a | zzzzzz
285 b | new
286 b | new
287 b | new
288 b | new
289 b | new
290 b | new
291 c | ccc
292 c | cccc
293 c | ccccc
294 c | cccccc
295 c | ccccccc
296 c | cccccccc
297 d | new
298 d | new
299 d | new
300 d | new
301 d | new
302 d | new
303 (24 rows)
305 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
306 relname | aa | bb
307 ---------+-----+----
308 b | new |
309 b | new |
310 b | new |
311 b | new |
312 b | new |
313 b | new |
314 d | new |
315 d | new |
316 d | new |
317 d | new |
318 d | new |
319 d | new |
320 (12 rows)
322 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
323 relname | aa | cc
324 ---------+----------+----
325 c | ccc |
326 c | cccc |
327 c | ccccc |
328 c | cccccc |
329 c | ccccccc |
330 c | cccccccc |
331 d | new |
332 d | new |
333 d | new |
334 d | new |
335 d | new |
336 d | new |
337 (12 rows)
339 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
340 relname | aa | bb | cc | dd
341 ---------+-----+----+----+----
342 d | new | | |
343 d | new | | |
344 d | new | | |
345 d | new | | |
346 d | new | | |
347 d | new | | |
348 (6 rows)
350 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
351 relname | aa
352 ---------+--------
353 a | zzzz
354 a | zzzzz
355 a | zzzzzz
356 a | zzzzzz
357 a | zzzzzz
358 a | zzzzzz
359 (6 rows)
361 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
362 relname | aa | bb
363 ---------+-----+----
364 b | new |
365 b | new |
366 b | new |
367 b | new |
368 b | new |
369 b | new |
370 (6 rows)
372 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
373 relname | aa | cc
374 ---------+----------+----
375 c | ccc |
376 c | cccc |
377 c | ccccc |
378 c | cccccc |
379 c | ccccccc |
380 c | cccccccc |
381 (6 rows)
383 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
384 relname | aa | bb | cc | dd
385 ---------+-----+----+----+----
386 d | new | | |
387 d | new | | |
388 d | new | | |
389 d | new | | |
390 d | new | | |
391 d | new | | |
392 (6 rows)
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;
397 relname | aa
398 ---------+-----
399 a | new
400 a | new
401 a | new
402 a | new
403 a | new
404 a | new
405 b | new
406 b | new
407 b | new
408 b | new
409 b | new
410 b | new
411 d | new
412 d | new
413 d | new
414 d | new
415 d | new
416 d | new
417 (18 rows)
419 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
420 relname | aa | bb
421 ---------+-----+----
422 b | new |
423 b | new |
424 b | new |
425 b | new |
426 b | new |
427 b | new |
428 d | new |
429 d | new |
430 d | new |
431 d | new |
432 d | new |
433 d | new |
434 (12 rows)
436 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
437 relname | aa | cc
438 ---------+-----+----
439 d | new |
440 d | new |
441 d | new |
442 d | new |
443 d | new |
444 d | new |
445 (6 rows)
447 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
448 relname | aa | bb | cc | dd
449 ---------+-----+----+----+----
450 d | new | | |
451 d | new | | |
452 d | new | | |
453 d | new | | |
454 d | new | | |
455 d | new | | |
456 (6 rows)
458 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
459 relname | aa
460 ---------+-----
461 a | new
462 a | new
463 a | new
464 a | new
465 a | new
466 a | new
467 (6 rows)
469 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
470 relname | aa | bb
471 ---------+-----+----
472 b | new |
473 b | new |
474 b | new |
475 b | new |
476 b | new |
477 b | new |
478 (6 rows)
480 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
481 relname | aa | cc
482 ---------+----+----
483 (0 rows)
485 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
486 relname | aa | bb | cc | dd
487 ---------+-----+----+----+----
488 d | new | | |
489 d | new | | |
490 d | new | | |
491 d | new | | |
492 d | new | | |
493 d | new | | |
494 (6 rows)
496 DELETE FROM a;
497 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
498 relname | aa
499 ---------+----
500 (0 rows)
502 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
503 relname | aa | bb
504 ---------+----+----
505 (0 rows)
507 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
508 relname | aa | cc
509 ---------+----+----
510 (0 rows)
512 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
513 relname | aa | bb | cc | dd
514 ---------+----+----+----+----
515 (0 rows)
517 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
518 relname | aa
519 ---------+----
520 (0 rows)
522 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
523 relname | aa | bb
524 ---------+----+----
525 (0 rows)
527 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
528 relname | aa | cc
529 ---------+----+----
530 (0 rows)
532 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
533 relname | aa | bb | cc | dd
534 ---------+----+----+----+----
535 (0 rows)
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" of relation "z" violates not-null constraint
541 DETAIL: Failing row contains (null, text).
542 -- ... but not UNIQUE.
543 CREATE TEMP TABLE z2 (b TEXT, UNIQUE(aa, b)) inherits (a);
544 INSERT INTO z2 VALUES (NULL, 'text'); -- should work
545 -- Check inherited UPDATE with first child excluded
546 create table some_tab (f1 int, f2 int, f3 int, check (f1 < 10) no inherit);
547 create table some_tab_child () inherits(some_tab);
548 insert into some_tab_child select i, i+1, 0 from generate_series(1,1000) i;
549 create index on some_tab_child(f1, f2);
550 -- while at it, also check that statement-level triggers fire
551 create function some_tab_stmt_trig_func() returns trigger as
552 $$begin raise notice 'updating some_tab'; return NULL; end;$$
553 language plpgsql;
554 create trigger some_tab_stmt_trig
555 before update on some_tab execute function some_tab_stmt_trig_func();
556 explain (costs off)
557 update some_tab set f3 = 11 where f1 = 12 and f2 = 13;
558 QUERY PLAN
559 ------------------------------------------------------------------------------------
560 Update on some_tab
561 Update on some_tab_child some_tab_1
562 -> Result
563 -> Index Scan using some_tab_child_f1_f2_idx on some_tab_child some_tab_1
564 Index Cond: ((f1 = 12) AND (f2 = 13))
565 (5 rows)
567 update some_tab set f3 = 11 where f1 = 12 and f2 = 13;
568 NOTICE: updating some_tab
569 drop table some_tab cascade;
570 NOTICE: drop cascades to table some_tab_child
571 drop function some_tab_stmt_trig_func();
572 -- Check inherited UPDATE with all children excluded
573 create table some_tab (a int, b int);
574 create table some_tab_child () inherits (some_tab);
575 insert into some_tab_child values(1,2);
576 explain (verbose, costs off)
577 update some_tab set a = a + 1 where false;
578 QUERY PLAN
579 --------------------------------------------------------
580 Update on public.some_tab
581 -> Result
582 Output: (some_tab.a + 1), NULL::oid, NULL::tid
583 One-Time Filter: false
584 (4 rows)
586 update some_tab set a = a + 1 where false;
587 explain (verbose, costs off)
588 update some_tab set a = a + 1 where false returning b, a;
589 QUERY PLAN
590 --------------------------------------------------------
591 Update on public.some_tab
592 Output: some_tab.b, some_tab.a
593 -> Result
594 Output: (some_tab.a + 1), NULL::oid, NULL::tid
595 One-Time Filter: false
596 (5 rows)
598 update some_tab set a = a + 1 where false returning b, a;
599 b | a
600 ---+---
601 (0 rows)
603 table some_tab;
604 a | b
605 ---+---
606 1 | 2
607 (1 row)
609 drop table some_tab cascade;
610 NOTICE: drop cascades to table some_tab_child
611 -- Check UPDATE with inherited target and an inherited source table
612 create temp table foo(f1 int, f2 int);
613 create temp table foo2(f3 int) inherits (foo);
614 create temp table bar(f1 int, f2 int);
615 create temp table bar2(f3 int) inherits (bar);
616 insert into foo values(1,1);
617 insert into foo values(3,3);
618 insert into foo2 values(2,2,2);
619 insert into foo2 values(3,3,3);
620 insert into bar values(1,1);
621 insert into bar values(2,2);
622 insert into bar values(3,3);
623 insert into bar values(4,4);
624 insert into bar2 values(1,1,1);
625 insert into bar2 values(2,2,2);
626 insert into bar2 values(3,3,3);
627 insert into bar2 values(4,4,4);
628 update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
629 select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
630 relname | f1 | f2
631 ---------+----+-----
632 bar | 1 | 101
633 bar | 2 | 102
634 bar | 3 | 103
635 bar | 4 | 4
636 bar2 | 1 | 101
637 bar2 | 2 | 102
638 bar2 | 3 | 103
639 bar2 | 4 | 4
640 (8 rows)
642 -- Check UPDATE with inherited target and an appendrel subquery
643 update bar set f2 = f2 + 100
644 from
645 ( select f1 from foo union all select f1+3 from foo ) ss
646 where bar.f1 = ss.f1;
647 select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
648 relname | f1 | f2
649 ---------+----+-----
650 bar | 1 | 201
651 bar | 2 | 202
652 bar | 3 | 203
653 bar | 4 | 104
654 bar2 | 1 | 201
655 bar2 | 2 | 202
656 bar2 | 3 | 203
657 bar2 | 4 | 104
658 (8 rows)
660 -- Check UPDATE with *partitioned* inherited target and an appendrel subquery
661 create table some_tab (a int);
662 insert into some_tab values (0);
663 create table some_tab_child () inherits (some_tab);
664 insert into some_tab_child values (1);
665 create table parted_tab (a int, b char) partition by list (a);
666 create table parted_tab_part1 partition of parted_tab for values in (1);
667 create table parted_tab_part2 partition of parted_tab for values in (2);
668 create table parted_tab_part3 partition of parted_tab for values in (3);
669 insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a');
670 update parted_tab set b = 'b'
671 from
672 (select a from some_tab union all select a+1 from some_tab) ss (a)
673 where parted_tab.a = ss.a;
674 select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2;
675 relname | a | b
676 ------------------+---+---
677 parted_tab_part1 | 1 | b
678 parted_tab_part2 | 2 | b
679 parted_tab_part3 | 3 | a
680 (3 rows)
682 truncate parted_tab;
683 insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a');
684 update parted_tab set b = 'b'
685 from
686 (select 0 from parted_tab union all select 1 from parted_tab) ss (a)
687 where parted_tab.a = ss.a;
688 select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2;
689 relname | a | b
690 ------------------+---+---
691 parted_tab_part1 | 1 | b
692 parted_tab_part2 | 2 | a
693 parted_tab_part3 | 3 | a
694 (3 rows)
696 -- modifies partition key, but no rows will actually be updated
697 explain update parted_tab set a = 2 where false;
698 QUERY PLAN
699 --------------------------------------------------------
700 Update on parted_tab (cost=0.00..0.00 rows=0 width=0)
701 -> Result (cost=0.00..0.00 rows=0 width=10)
702 One-Time Filter: false
703 (3 rows)
705 drop table parted_tab;
706 -- Check UPDATE with multi-level partitioned inherited target
707 create table mlparted_tab (a int, b char, c text) partition by list (a);
708 create table mlparted_tab_part1 partition of mlparted_tab for values in (1);
709 create table mlparted_tab_part2 partition of mlparted_tab for values in (2) partition by list (b);
710 create table mlparted_tab_part3 partition of mlparted_tab for values in (3);
711 create table mlparted_tab_part2a partition of mlparted_tab_part2 for values in ('a');
712 create table mlparted_tab_part2b partition of mlparted_tab_part2 for values in ('b');
713 insert into mlparted_tab values (1, 'a'), (2, 'a'), (2, 'b'), (3, 'a');
714 update mlparted_tab mlp set c = 'xxx'
715 from
716 (select a from some_tab union all select a+1 from some_tab) ss (a)
717 where (mlp.a = ss.a and mlp.b = 'b') or mlp.a = 3;
718 select tableoid::regclass::text as relname, mlparted_tab.* from mlparted_tab order by 1,2;
719 relname | a | b | c
720 ---------------------+---+---+-----
721 mlparted_tab_part1 | 1 | a |
722 mlparted_tab_part2a | 2 | a |
723 mlparted_tab_part2b | 2 | b | xxx
724 mlparted_tab_part3 | 3 | a | xxx
725 (4 rows)
727 drop table mlparted_tab;
728 drop table some_tab cascade;
729 NOTICE: drop cascades to table some_tab_child
730 /* Test multiple inheritance of column defaults */
731 CREATE TABLE firstparent (tomorrow date default now()::date + 1);
732 CREATE TABLE secondparent (tomorrow date default now() :: date + 1);
733 CREATE TABLE jointchild () INHERITS (firstparent, secondparent); -- ok
734 NOTICE: merging multiple inherited definitions of column "tomorrow"
735 CREATE TABLE thirdparent (tomorrow date default now()::date - 1);
736 CREATE TABLE otherchild () INHERITS (firstparent, thirdparent); -- not ok
737 NOTICE: merging multiple inherited definitions of column "tomorrow"
738 ERROR: column "tomorrow" inherits conflicting default values
739 HINT: To resolve the conflict, specify a default explicitly.
740 CREATE TABLE otherchild (tomorrow date default now())
741 INHERITS (firstparent, thirdparent); -- ok, child resolves ambiguous default
742 NOTICE: merging multiple inherited definitions of column "tomorrow"
743 NOTICE: merging column "tomorrow" with inherited definition
744 DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild;
745 -- Test changing the type of inherited columns
746 insert into d values('test','one','two','three');
747 alter table a alter column aa type integer using bit_length(aa);
748 select * from d;
749 aa | bb | cc | dd
750 ----+-----+-----+-------
751 32 | one | two | three
752 (1 row)
754 -- The above verified that we can change the type of a multiply-inherited
755 -- column; but we should reject that if any definition was inherited from
756 -- an unrelated parent.
757 create temp table parent1(f1 int, f2 int);
758 create temp table parent2(f1 int, f3 bigint);
759 create temp table childtab(f4 int) inherits(parent1, parent2);
760 NOTICE: merging multiple inherited definitions of column "f1"
761 alter table parent1 alter column f1 type bigint; -- fail, conflict w/parent2
762 ERROR: cannot alter inherited column "f1" of relation "childtab"
763 alter table parent1 alter column f2 type bigint; -- ok
764 -- Test non-inheritable parent constraints
765 create table p1(ff1 int);
766 alter table p1 add constraint p1chk check (ff1 > 0) no inherit;
767 alter table p1 add constraint p2chk check (ff1 > 10);
768 -- connoinherit should be true for NO INHERIT constraint
769 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;
770 relname | conname | contype | conislocal | coninhcount | connoinherit
771 ---------+---------+---------+------------+-------------+--------------
772 p1 | p1chk | c | t | 0 | t
773 p1 | p2chk | c | t | 0 | f
774 (2 rows)
776 -- Test that child does not inherit NO INHERIT constraints
777 create table c1 () inherits (p1);
778 \d p1
779 Table "public.p1"
780 Column | Type | Collation | Nullable | Default
781 --------+---------+-----------+----------+---------
782 ff1 | integer | | |
783 Check constraints:
784 "p1chk" CHECK (ff1 > 0) NO INHERIT
785 "p2chk" CHECK (ff1 > 10)
786 Number of child tables: 1 (Use \d+ to list them.)
788 \d c1
789 Table "public.c1"
790 Column | Type | Collation | Nullable | Default
791 --------+---------+-----------+----------+---------
792 ff1 | integer | | |
793 Check constraints:
794 "p2chk" CHECK (ff1 > 10)
795 Inherits: p1
797 -- Test that child does not override inheritable constraints of the parent
798 create table c2 (constraint p2chk check (ff1 > 10) no inherit) inherits (p1); --fails
799 ERROR: constraint "p2chk" conflicts with inherited constraint on relation "c2"
800 drop table p1 cascade;
801 NOTICE: drop cascades to table c1
802 -- Tests for casting between the rowtypes of parent and child
803 -- tables. See the pgsql-hackers thread beginning Dec. 4/04
804 create table base (i integer);
805 create table derived () inherits (base);
806 create table more_derived (like derived, b int) inherits (derived);
807 NOTICE: merging column "i" with inherited definition
808 insert into derived (i) values (0);
809 select derived::base from derived;
810 derived
811 ---------
812 (0)
813 (1 row)
815 select NULL::derived::base;
816 base
817 ------
819 (1 row)
821 -- remove redundant conversions.
822 explain (verbose on, costs off) select row(i, b)::more_derived::derived::base from more_derived;
823 QUERY PLAN
824 -------------------------------------------
825 Seq Scan on public.more_derived
826 Output: (ROW(i, b)::more_derived)::base
827 (2 rows)
829 explain (verbose on, costs off) select (1, 2)::more_derived::derived::base;
830 QUERY PLAN
831 -----------------------
832 Result
833 Output: '(1)'::base
834 (2 rows)
836 drop table more_derived;
837 drop table derived;
838 drop table base;
839 create table p1(ff1 int);
840 create table p2(f1 text);
841 create function p2text(p2) returns text as 'select $1.f1' language sql;
842 create table c1(f3 int) inherits(p1,p2);
843 insert into c1 values(123456789, 'hi', 42);
844 select p2text(c1.*) from c1;
845 p2text
846 --------
847 hi
848 (1 row)
850 drop function p2text(p2);
851 drop table c1;
852 drop table p2;
853 drop table p1;
854 CREATE TABLE ac (aa TEXT);
855 alter table ac add constraint ac_check check (aa is not null);
856 CREATE TABLE bc (bb TEXT) INHERITS (ac);
857 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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;
858 relname | conname | contype | conislocal | coninhcount | consrc
859 ---------+----------+---------+------------+-------------+------------------
860 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
861 bc | ac_check | c | f | 1 | (aa IS NOT NULL)
862 (2 rows)
864 insert into ac (aa) values (NULL);
865 ERROR: new row for relation "ac" violates check constraint "ac_check"
866 DETAIL: Failing row contains (null).
867 insert into bc (aa) values (NULL);
868 ERROR: new row for relation "bc" violates check constraint "ac_check"
869 DETAIL: Failing row contains (null, null).
870 alter table bc drop constraint ac_check; -- fail, disallowed
871 ERROR: cannot drop inherited constraint "ac_check" of relation "bc"
872 alter table ac drop constraint ac_check;
873 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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;
874 relname | conname | contype | conislocal | coninhcount | consrc
875 ---------+---------+---------+------------+-------------+--------
876 (0 rows)
878 -- try the unnamed-constraint case
879 alter table ac add check (aa is not null);
880 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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;
881 relname | conname | contype | conislocal | coninhcount | consrc
882 ---------+-------------+---------+------------+-------------+------------------
883 ac | ac_aa_check | c | t | 0 | (aa IS NOT NULL)
884 bc | ac_aa_check | c | f | 1 | (aa IS NOT NULL)
885 (2 rows)
887 insert into ac (aa) values (NULL);
888 ERROR: new row for relation "ac" violates check constraint "ac_aa_check"
889 DETAIL: Failing row contains (null).
890 insert into bc (aa) values (NULL);
891 ERROR: new row for relation "bc" violates check constraint "ac_aa_check"
892 DETAIL: Failing row contains (null, null).
893 alter table bc drop constraint ac_aa_check; -- fail, disallowed
894 ERROR: cannot drop inherited constraint "ac_aa_check" of relation "bc"
895 alter table ac drop constraint ac_aa_check;
896 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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;
897 relname | conname | contype | conislocal | coninhcount | consrc
898 ---------+---------+---------+------------+-------------+--------
899 (0 rows)
901 alter table ac add constraint ac_check check (aa is not null);
902 alter table bc no inherit ac;
903 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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;
904 relname | conname | contype | conislocal | coninhcount | consrc
905 ---------+----------+---------+------------+-------------+------------------
906 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
907 bc | ac_check | c | t | 0 | (aa IS NOT NULL)
908 (2 rows)
910 alter table bc drop constraint ac_check;
911 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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;
912 relname | conname | contype | conislocal | coninhcount | consrc
913 ---------+----------+---------+------------+-------------+------------------
914 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
915 (1 row)
917 alter table ac drop constraint ac_check;
918 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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;
919 relname | conname | contype | conislocal | coninhcount | consrc
920 ---------+---------+---------+------------+-------------+--------
921 (0 rows)
923 drop table bc;
924 drop table ac;
925 create table ac (a int constraint check_a check (a <> 0));
926 create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac);
927 NOTICE: merging column "a" with inherited definition
928 NOTICE: merging constraint "check_a" with inherited definition
929 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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;
930 relname | conname | contype | conislocal | coninhcount | consrc
931 ---------+---------+---------+------------+-------------+----------
932 ac | check_a | c | t | 0 | (a <> 0)
933 bc | check_a | c | t | 1 | (a <> 0)
934 bc | check_b | c | t | 0 | (b <> 0)
935 (3 rows)
937 drop table bc;
938 drop table ac;
939 create table ac (a int constraint check_a check (a <> 0));
940 create table bc (b int constraint check_b check (b <> 0));
941 create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc);
942 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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;
943 relname | conname | contype | conislocal | coninhcount | consrc
944 ---------+---------+---------+------------+-------------+----------
945 ac | check_a | c | t | 0 | (a <> 0)
946 bc | check_b | c | t | 0 | (b <> 0)
947 cc | check_a | c | f | 1 | (a <> 0)
948 cc | check_b | c | f | 1 | (b <> 0)
949 cc | check_c | c | t | 0 | (c <> 0)
950 (5 rows)
952 alter table cc no inherit bc;
953 select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as 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;
954 relname | conname | contype | conislocal | coninhcount | consrc
955 ---------+---------+---------+------------+-------------+----------
956 ac | check_a | c | t | 0 | (a <> 0)
957 bc | check_b | c | t | 0 | (b <> 0)
958 cc | check_a | c | f | 1 | (a <> 0)
959 cc | check_b | c | t | 0 | (b <> 0)
960 cc | check_c | c | t | 0 | (c <> 0)
961 (5 rows)
963 drop table cc;
964 drop table bc;
965 drop table ac;
966 create table p1(f1 int);
967 create table p2(f2 int);
968 create table c1(f3 int) inherits(p1,p2);
969 insert into c1 values(1,-1,2);
970 alter table p2 add constraint cc check (f2>0); -- fail
971 ERROR: check constraint "cc" of relation "c1" is violated by some row
972 alter table p2 add check (f2>0); -- check it without a name, too
973 ERROR: check constraint "p2_f2_check" of relation "c1" is violated by some row
974 delete from c1;
975 insert into c1 values(1,1,2);
976 alter table p2 add check (f2>0);
977 insert into c1 values(1,-1,2); -- fail
978 ERROR: new row for relation "c1" violates check constraint "p2_f2_check"
979 DETAIL: Failing row contains (1, -1, 2).
980 create table c2(f3 int) inherits(p1,p2);
981 \d c2
982 Table "public.c2"
983 Column | Type | Collation | Nullable | Default
984 --------+---------+-----------+----------+---------
985 f1 | integer | | |
986 f2 | integer | | |
987 f3 | integer | | |
988 Check constraints:
989 "p2_f2_check" CHECK (f2 > 0)
990 Inherits: p1,
991 p2
993 create table c3 (f4 int) inherits(c1,c2);
994 NOTICE: merging multiple inherited definitions of column "f1"
995 NOTICE: merging multiple inherited definitions of column "f2"
996 NOTICE: merging multiple inherited definitions of column "f3"
997 \d c3
998 Table "public.c3"
999 Column | Type | Collation | Nullable | Default
1000 --------+---------+-----------+----------+---------
1001 f1 | integer | | |
1002 f2 | integer | | |
1003 f3 | integer | | |
1004 f4 | integer | | |
1005 Check constraints:
1006 "p2_f2_check" CHECK (f2 > 0)
1007 Inherits: c1,
1008 c2
1010 drop table p1 cascade;
1011 NOTICE: drop cascades to 3 other objects
1012 DETAIL: drop cascades to table c1
1013 drop cascades to table c2
1014 drop cascades to table c3
1015 drop table p2 cascade;
1016 create table pp1 (f1 int);
1017 create table cc1 (f2 text, f3 int) inherits (pp1);
1018 alter table pp1 add column a1 int check (a1 > 0);
1019 \d cc1
1020 Table "public.cc1"
1021 Column | Type | Collation | Nullable | Default
1022 --------+---------+-----------+----------+---------
1023 f1 | integer | | |
1024 f2 | text | | |
1025 f3 | integer | | |
1026 a1 | integer | | |
1027 Check constraints:
1028 "pp1_a1_check" CHECK (a1 > 0)
1029 Inherits: pp1
1031 create table cc2(f4 float) inherits(pp1,cc1);
1032 NOTICE: merging multiple inherited definitions of column "f1"
1033 NOTICE: merging multiple inherited definitions of column "a1"
1034 \d cc2
1035 Table "public.cc2"
1036 Column | Type | Collation | Nullable | Default
1037 --------+------------------+-----------+----------+---------
1038 f1 | integer | | |
1039 a1 | integer | | |
1040 f2 | text | | |
1041 f3 | integer | | |
1042 f4 | double precision | | |
1043 Check constraints:
1044 "pp1_a1_check" CHECK (a1 > 0)
1045 Inherits: pp1,
1046 cc1
1048 alter table pp1 add column a2 int check (a2 > 0);
1049 NOTICE: merging definition of column "a2" for child "cc2"
1050 NOTICE: merging constraint "pp1_a2_check" with inherited definition
1051 \d cc2
1052 Table "public.cc2"
1053 Column | Type | Collation | Nullable | Default
1054 --------+------------------+-----------+----------+---------
1055 f1 | integer | | |
1056 a1 | integer | | |
1057 f2 | text | | |
1058 f3 | integer | | |
1059 f4 | double precision | | |
1060 a2 | integer | | |
1061 Check constraints:
1062 "pp1_a1_check" CHECK (a1 > 0)
1063 "pp1_a2_check" CHECK (a2 > 0)
1064 Inherits: pp1,
1065 cc1
1067 drop table pp1 cascade;
1068 NOTICE: drop cascades to 2 other objects
1069 DETAIL: drop cascades to table cc1
1070 drop cascades to table cc2
1071 -- Test for renaming in simple multiple inheritance
1072 CREATE TABLE inht1 (a int, b int);
1073 CREATE TABLE inhs1 (b int, c int);
1074 CREATE TABLE inhts (d int) INHERITS (inht1, inhs1);
1075 NOTICE: merging multiple inherited definitions of column "b"
1076 ALTER TABLE inht1 RENAME a TO aa;
1077 ALTER TABLE inht1 RENAME b TO bb; -- to be failed
1078 ERROR: cannot rename inherited column "b"
1079 ALTER TABLE inhts RENAME aa TO aaa; -- to be failed
1080 ERROR: cannot rename inherited column "aa"
1081 ALTER TABLE inhts RENAME d TO dd;
1082 \d+ inhts
1083 Table "public.inhts"
1084 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1085 --------+---------+-----------+----------+---------+---------+--------------+-------------
1086 aa | integer | | | | plain | |
1087 b | integer | | | | plain | |
1088 c | integer | | | | plain | |
1089 dd | integer | | | | plain | |
1090 Inherits: inht1,
1091 inhs1
1093 DROP TABLE inhts;
1094 -- Test for adding a column to a parent table with complex inheritance
1095 CREATE TABLE inhta ();
1096 CREATE TABLE inhtb () INHERITS (inhta);
1097 CREATE TABLE inhtc () INHERITS (inhtb);
1098 CREATE TABLE inhtd () INHERITS (inhta, inhtb, inhtc);
1099 ALTER TABLE inhta ADD COLUMN i int;
1100 NOTICE: merging definition of column "i" for child "inhtd"
1101 NOTICE: merging definition of column "i" for child "inhtd"
1102 \d+ inhta
1103 Table "public.inhta"
1104 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1105 --------+---------+-----------+----------+---------+---------+--------------+-------------
1106 i | integer | | | | plain | |
1107 Child tables: inhtb,
1108 inhtd
1110 DROP TABLE inhta, inhtb, inhtc, inhtd;
1111 -- Test for renaming in diamond inheritance
1112 CREATE TABLE inht2 (x int) INHERITS (inht1);
1113 CREATE TABLE inht3 (y int) INHERITS (inht1);
1114 CREATE TABLE inht4 (z int) INHERITS (inht2, inht3);
1115 NOTICE: merging multiple inherited definitions of column "aa"
1116 NOTICE: merging multiple inherited definitions of column "b"
1117 ALTER TABLE inht1 RENAME aa TO aaa;
1118 \d+ inht4
1119 Table "public.inht4"
1120 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1121 --------+---------+-----------+----------+---------+---------+--------------+-------------
1122 aaa | integer | | | | plain | |
1123 b | integer | | | | plain | |
1124 x | integer | | | | plain | |
1125 y | integer | | | | plain | |
1126 z | integer | | | | plain | |
1127 Inherits: inht2,
1128 inht3
1130 CREATE TABLE inhts (d int) INHERITS (inht2, inhs1);
1131 NOTICE: merging multiple inherited definitions of column "b"
1132 ALTER TABLE inht1 RENAME aaa TO aaaa;
1133 ALTER TABLE inht1 RENAME b TO bb; -- to be failed
1134 ERROR: cannot rename inherited column "b"
1135 \d+ inhts
1136 Table "public.inhts"
1137 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1138 --------+---------+-----------+----------+---------+---------+--------------+-------------
1139 aaaa | integer | | | | plain | |
1140 b | integer | | | | plain | |
1141 x | integer | | | | plain | |
1142 c | integer | | | | plain | |
1143 d | integer | | | | plain | |
1144 Inherits: inht2,
1145 inhs1
1147 WITH RECURSIVE r AS (
1148 SELECT 'inht1'::regclass AS inhrelid
1149 UNION ALL
1150 SELECT c.inhrelid FROM pg_inherits c, r WHERE r.inhrelid = c.inhparent
1151 )
1152 SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected
1153 FROM (SELECT inhrelid, count(*) AS expected FROM pg_inherits
1154 WHERE inhparent IN (SELECT inhrelid FROM r) GROUP BY inhrelid) e
1155 JOIN pg_attribute a ON e.inhrelid = a.attrelid WHERE NOT attislocal
1156 ORDER BY a.attrelid::regclass::name, a.attnum;
1157 attrelid | attname | attinhcount | expected
1158 ----------+---------+-------------+----------
1159 inht2 | aaaa | 1 | 1
1160 inht2 | b | 1 | 1
1161 inht3 | aaaa | 1 | 1
1162 inht3 | b | 1 | 1
1163 inht4 | aaaa | 2 | 2
1164 inht4 | b | 2 | 2
1165 inht4 | x | 1 | 2
1166 inht4 | y | 1 | 2
1167 inhts | aaaa | 1 | 1
1168 inhts | b | 2 | 1
1169 inhts | x | 1 | 1
1170 inhts | c | 1 | 1
1171 (12 rows)
1173 DROP TABLE inht1, inhs1 CASCADE;
1174 NOTICE: drop cascades to 4 other objects
1175 DETAIL: drop cascades to table inht2
1176 drop cascades to table inhts
1177 drop cascades to table inht3
1178 drop cascades to table inht4
1179 -- Test non-inheritable indices [UNIQUE, EXCLUDE] constraints
1180 CREATE TABLE test_constraints (id int, val1 varchar, val2 int, UNIQUE(val1, val2));
1181 CREATE TABLE test_constraints_inh () INHERITS (test_constraints);
1182 \d+ test_constraints
1183 Table "public.test_constraints"
1184 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1185 --------+-------------------+-----------+----------+---------+----------+--------------+-------------
1186 id | integer | | | | plain | |
1187 val1 | character varying | | | | extended | |
1188 val2 | integer | | | | plain | |
1189 Indexes:
1190 "test_constraints_val1_val2_key" UNIQUE CONSTRAINT, btree (val1, val2)
1191 Child tables: test_constraints_inh
1193 ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key;
1194 \d+ test_constraints
1195 Table "public.test_constraints"
1196 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1197 --------+-------------------+-----------+----------+---------+----------+--------------+-------------
1198 id | integer | | | | plain | |
1199 val1 | character varying | | | | extended | |
1200 val2 | integer | | | | plain | |
1201 Child tables: test_constraints_inh
1203 \d+ test_constraints_inh
1204 Table "public.test_constraints_inh"
1205 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1206 --------+-------------------+-----------+----------+---------+----------+--------------+-------------
1207 id | integer | | | | plain | |
1208 val1 | character varying | | | | extended | |
1209 val2 | integer | | | | plain | |
1210 Inherits: test_constraints
1212 DROP TABLE test_constraints_inh;
1213 DROP TABLE test_constraints;
1214 CREATE TABLE test_ex_constraints (
1215 c circle,
1216 EXCLUDE USING gist (c WITH &&)
1217 );
1218 CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints);
1219 \d+ test_ex_constraints
1220 Table "public.test_ex_constraints"
1221 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1222 --------+--------+-----------+----------+---------+---------+--------------+-------------
1223 c | circle | | | | plain | |
1224 Indexes:
1225 "test_ex_constraints_c_excl" EXCLUDE USING gist (c WITH &&)
1226 Child tables: test_ex_constraints_inh
1228 ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl;
1229 \d+ test_ex_constraints
1230 Table "public.test_ex_constraints"
1231 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1232 --------+--------+-----------+----------+---------+---------+--------------+-------------
1233 c | circle | | | | plain | |
1234 Child tables: test_ex_constraints_inh
1236 \d+ test_ex_constraints_inh
1237 Table "public.test_ex_constraints_inh"
1238 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1239 --------+--------+-----------+----------+---------+---------+--------------+-------------
1240 c | circle | | | | plain | |
1241 Inherits: test_ex_constraints
1243 DROP TABLE test_ex_constraints_inh;
1244 DROP TABLE test_ex_constraints;
1245 -- Test non-inheritable foreign key constraints
1246 CREATE TABLE test_primary_constraints(id int PRIMARY KEY);
1247 CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id));
1248 CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints);
1249 \d+ test_primary_constraints
1250 Table "public.test_primary_constraints"
1251 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1252 --------+---------+-----------+----------+---------+---------+--------------+-------------
1253 id | integer | | not null | | plain | |
1254 Indexes:
1255 "test_primary_constraints_pkey" PRIMARY KEY, btree (id)
1256 Referenced by:
1257 TABLE "test_foreign_constraints" CONSTRAINT "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
1258 Not-null constraints:
1259 "test_primary_constraints_id_not_null" NOT NULL "id"
1261 \d+ test_foreign_constraints
1262 Table "public.test_foreign_constraints"
1263 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1264 --------+---------+-----------+----------+---------+---------+--------------+-------------
1265 id1 | integer | | | | plain | |
1266 Foreign-key constraints:
1267 "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
1268 Child tables: test_foreign_constraints_inh
1270 ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey;
1271 \d+ test_foreign_constraints
1272 Table "public.test_foreign_constraints"
1273 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1274 --------+---------+-----------+----------+---------+---------+--------------+-------------
1275 id1 | integer | | | | plain | |
1276 Child tables: test_foreign_constraints_inh
1278 \d+ test_foreign_constraints_inh
1279 Table "public.test_foreign_constraints_inh"
1280 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1281 --------+---------+-----------+----------+---------+---------+--------------+-------------
1282 id1 | integer | | | | plain | |
1283 Inherits: test_foreign_constraints
1285 DROP TABLE test_foreign_constraints_inh;
1286 DROP TABLE test_foreign_constraints;
1287 DROP TABLE test_primary_constraints;
1288 -- Test foreign key behavior
1289 create table inh_fk_1 (a int primary key);
1290 insert into inh_fk_1 values (1), (2), (3);
1291 create table inh_fk_2 (x int primary key, y int references inh_fk_1 on delete cascade);
1292 insert into inh_fk_2 values (11, 1), (22, 2), (33, 3);
1293 create table inh_fk_2_child () inherits (inh_fk_2);
1294 insert into inh_fk_2_child values (111, 1), (222, 2);
1295 delete from inh_fk_1 where a = 1;
1296 select * from inh_fk_1 order by 1;
1297 a
1298 ---
1299 2
1300 3
1301 (2 rows)
1303 select * from inh_fk_2 order by 1, 2;
1304 x | y
1305 -----+---
1306 22 | 2
1307 33 | 3
1308 111 | 1
1309 222 | 2
1310 (4 rows)
1312 drop table inh_fk_1, inh_fk_2, inh_fk_2_child;
1313 -- Test that parent and child CHECK constraints can be created in either order
1314 create table p1(f1 int);
1315 create table p1_c1() inherits(p1);
1316 alter table p1 add constraint inh_check_constraint1 check (f1 > 0);
1317 alter table p1_c1 add constraint inh_check_constraint1 check (f1 > 0);
1318 NOTICE: merging constraint "inh_check_constraint1" with inherited definition
1319 alter table p1_c1 add constraint inh_check_constraint2 check (f1 < 10);
1320 alter table p1 add constraint inh_check_constraint2 check (f1 < 10);
1321 NOTICE: merging constraint "inh_check_constraint2" with inherited definition
1322 select conrelid::regclass::text as relname, conname, conislocal, coninhcount
1323 from pg_constraint where conname like 'inh\_check\_constraint%'
1324 order by 1, 2;
1325 relname | conname | conislocal | coninhcount
1326 ---------+-----------------------+------------+-------------
1327 p1 | inh_check_constraint1 | t | 0
1328 p1 | inh_check_constraint2 | t | 0
1329 p1_c1 | inh_check_constraint1 | t | 1
1330 p1_c1 | inh_check_constraint2 | t | 1
1331 (4 rows)
1333 drop table p1 cascade;
1334 NOTICE: drop cascades to table p1_c1
1335 --
1336 -- Test DROP behavior of multiply-defined CHECK constraints
1337 --
1338 create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1339 create table p1_c1 (f1 int constraint f1_pos CHECK (f1 > 0)) inherits (p1);
1340 NOTICE: merging column "f1" with inherited definition
1341 NOTICE: merging constraint "f1_pos" with inherited definition
1342 alter table p1_c1 drop constraint f1_pos;
1343 ERROR: cannot drop inherited constraint "f1_pos" of relation "p1_c1"
1344 alter table p1 drop constraint f1_pos;
1345 \d p1_c1
1346 Table "public.p1_c1"
1347 Column | Type | Collation | Nullable | Default
1348 --------+---------+-----------+----------+---------
1349 f1 | integer | | |
1350 Check constraints:
1351 "f1_pos" CHECK (f1 > 0)
1352 Inherits: p1
1354 drop table p1 cascade;
1355 NOTICE: drop cascades to table p1_c1
1356 create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1357 create table p2(f1 int constraint f1_pos CHECK (f1 > 0));
1358 create table p1p2_c1 (f1 int) inherits (p1, p2);
1359 NOTICE: merging multiple inherited definitions of column "f1"
1360 NOTICE: merging column "f1" with inherited definition
1361 create table p1p2_c2 (f1 int constraint f1_pos CHECK (f1 > 0)) inherits (p1, p2);
1362 NOTICE: merging multiple inherited definitions of column "f1"
1363 NOTICE: merging column "f1" with inherited definition
1364 NOTICE: merging constraint "f1_pos" with inherited definition
1365 alter table p2 drop constraint f1_pos;
1366 alter table p1 drop constraint f1_pos;
1367 \d p1p2_c*
1368 Table "public.p1p2_c1"
1369 Column | Type | Collation | Nullable | Default
1370 --------+---------+-----------+----------+---------
1371 f1 | integer | | |
1372 Inherits: p1,
1373 p2
1375 Table "public.p1p2_c2"
1376 Column | Type | Collation | Nullable | Default
1377 --------+---------+-----------+----------+---------
1378 f1 | integer | | |
1379 Check constraints:
1380 "f1_pos" CHECK (f1 > 0)
1381 Inherits: p1,
1382 p2
1384 drop table p1, p2 cascade;
1385 NOTICE: drop cascades to 2 other objects
1386 DETAIL: drop cascades to table p1p2_c1
1387 drop cascades to table p1p2_c2
1388 create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1389 create table p1_c1() inherits (p1);
1390 create table p1_c2() inherits (p1);
1391 create table p1_c1c2() inherits (p1_c1, p1_c2);
1392 NOTICE: merging multiple inherited definitions of column "f1"
1393 \d p1_c1c2
1394 Table "public.p1_c1c2"
1395 Column | Type | Collation | Nullable | Default
1396 --------+---------+-----------+----------+---------
1397 f1 | integer | | |
1398 Check constraints:
1399 "f1_pos" CHECK (f1 > 0)
1400 Inherits: p1_c1,
1401 p1_c2
1403 alter table p1 drop constraint f1_pos;
1404 \d p1_c1c2
1405 Table "public.p1_c1c2"
1406 Column | Type | Collation | Nullable | Default
1407 --------+---------+-----------+----------+---------
1408 f1 | integer | | |
1409 Inherits: p1_c1,
1410 p1_c2
1412 drop table p1 cascade;
1413 NOTICE: drop cascades to 3 other objects
1414 DETAIL: drop cascades to table p1_c1
1415 drop cascades to table p1_c2
1416 drop cascades to table p1_c1c2
1417 create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1418 create table p1_c1() inherits (p1);
1419 create table p1_c2(constraint f1_pos CHECK (f1 > 0)) inherits (p1);
1420 NOTICE: merging constraint "f1_pos" with inherited definition
1421 create table p1_c1c2() inherits (p1_c1, p1_c2, p1);
1422 NOTICE: merging multiple inherited definitions of column "f1"
1423 NOTICE: merging multiple inherited definitions of column "f1"
1424 alter table p1_c2 drop constraint f1_pos;
1425 ERROR: cannot drop inherited constraint "f1_pos" of relation "p1_c2"
1426 alter table p1 drop constraint f1_pos;
1427 alter table p1_c1c2 drop constraint f1_pos;
1428 ERROR: cannot drop inherited constraint "f1_pos" of relation "p1_c1c2"
1429 alter table p1_c2 drop constraint f1_pos;
1430 \d p1_c1c2
1431 Table "public.p1_c1c2"
1432 Column | Type | Collation | Nullable | Default
1433 --------+---------+-----------+----------+---------
1434 f1 | integer | | |
1435 Inherits: p1_c1,
1436 p1_c2,
1437 p1
1439 drop table p1 cascade;
1440 NOTICE: drop cascades to 3 other objects
1441 DETAIL: drop cascades to table p1_c1
1442 drop cascades to table p1_c2
1443 drop cascades to table p1_c1c2
1444 -- Test that a valid child can have not-valid parent, but not vice versa
1445 create table invalid_check_con(f1 int);
1446 create table invalid_check_con_child() inherits(invalid_check_con);
1447 alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0) not valid;
1448 alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0); -- fail
1449 ERROR: constraint "inh_check_constraint" conflicts with NOT VALID constraint on relation "invalid_check_con_child"
1450 alter table invalid_check_con_child drop constraint inh_check_constraint;
1451 insert into invalid_check_con values(0);
1452 alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0);
1453 alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0) not valid;
1454 NOTICE: merging constraint "inh_check_constraint" with inherited definition
1455 insert into invalid_check_con values(0); -- fail
1456 ERROR: new row for relation "invalid_check_con" violates check constraint "inh_check_constraint"
1457 DETAIL: Failing row contains (0).
1458 insert into invalid_check_con_child values(0); -- fail
1459 ERROR: new row for relation "invalid_check_con_child" violates check constraint "inh_check_constraint"
1460 DETAIL: Failing row contains (0).
1461 select conrelid::regclass::text as relname, conname,
1462 convalidated, conislocal, coninhcount, connoinherit
1463 from pg_constraint where conname like 'inh\_check\_constraint%'
1464 order by 1, 2;
1465 relname | conname | convalidated | conislocal | coninhcount | connoinherit
1466 -------------------------+----------------------+--------------+------------+-------------+--------------
1467 invalid_check_con | inh_check_constraint | f | t | 0 | f
1468 invalid_check_con_child | inh_check_constraint | t | t | 1 | f
1469 (2 rows)
1471 -- We don't drop the invalid_check_con* tables, to test dump/reload with
1472 --
1473 -- Test parameterized append plans for inheritance trees
1474 --
1475 create temp table patest0 (id, x) as
1476 select x, x from generate_series(0,1000) x;
1477 create temp table patest1() inherits (patest0);
1478 insert into patest1
1479 select x, x from generate_series(0,1000) x;
1480 create temp table patest2() inherits (patest0);
1481 insert into patest2
1482 select x, x from generate_series(0,1000) x;
1483 create index patest0i on patest0(id);
1484 create index patest1i on patest1(id);
1485 create index patest2i on patest2(id);
1486 analyze patest0;
1487 analyze patest1;
1488 analyze patest2;
1489 explain (costs off)
1490 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1491 QUERY PLAN
1492 ------------------------------------------------------------
1493 Nested Loop
1494 -> Limit
1495 -> Seq Scan on int4_tbl
1496 -> Append
1497 -> Index Scan using patest0i on patest0 patest0_1
1498 Index Cond: (id = int4_tbl.f1)
1499 -> Index Scan using patest1i on patest1 patest0_2
1500 Index Cond: (id = int4_tbl.f1)
1501 -> Index Scan using patest2i on patest2 patest0_3
1502 Index Cond: (id = int4_tbl.f1)
1503 (10 rows)
1505 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1506 id | x | f1
1507 ----+---+----
1508 0 | 0 | 0
1509 0 | 0 | 0
1510 0 | 0 | 0
1511 (3 rows)
1513 drop index patest2i;
1514 explain (costs off)
1515 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1516 QUERY PLAN
1517 ------------------------------------------------------------
1518 Nested Loop
1519 -> Limit
1520 -> Seq Scan on int4_tbl
1521 -> Append
1522 -> Index Scan using patest0i on patest0 patest0_1
1523 Index Cond: (id = int4_tbl.f1)
1524 -> Index Scan using patest1i on patest1 patest0_2
1525 Index Cond: (id = int4_tbl.f1)
1526 -> Seq Scan on patest2 patest0_3
1527 Filter: (int4_tbl.f1 = id)
1528 (10 rows)
1530 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1531 id | x | f1
1532 ----+---+----
1533 0 | 0 | 0
1534 0 | 0 | 0
1535 0 | 0 | 0
1536 (3 rows)
1538 drop table patest0 cascade;
1539 NOTICE: drop cascades to 2 other objects
1540 DETAIL: drop cascades to table patest1
1541 drop cascades to table patest2
1542 --
1543 -- Test merge-append plans for inheritance trees
1544 --
1545 create table matest0 (id serial primary key, name text);
1546 create table matest1 (id integer primary key) inherits (matest0);
1547 NOTICE: merging column "id" with inherited definition
1548 create table matest2 (id integer primary key) inherits (matest0);
1549 NOTICE: merging column "id" with inherited definition
1550 create table matest3 (id integer primary key) inherits (matest0);
1551 NOTICE: merging column "id" with inherited definition
1552 create index matest0i on matest0 ((1-id));
1553 create index matest1i on matest1 ((1-id));
1554 -- create index matest2i on matest2 ((1-id)); -- intentionally missing
1555 create index matest3i on matest3 ((1-id));
1556 insert into matest1 (name) values ('Test 1');
1557 insert into matest1 (name) values ('Test 2');
1558 insert into matest2 (name) values ('Test 3');
1559 insert into matest2 (name) values ('Test 4');
1560 insert into matest3 (name) values ('Test 5');
1561 insert into matest3 (name) values ('Test 6');
1562 set enable_indexscan = off; -- force use of seqscan/sort, so no merge
1563 explain (verbose, costs off) select * from matest0 order by 1-id;
1564 QUERY PLAN
1565 ------------------------------------------------------------
1566 Sort
1567 Output: matest0.id, matest0.name, ((1 - matest0.id))
1568 Sort Key: ((1 - matest0.id))
1569 -> Result
1570 Output: matest0.id, matest0.name, (1 - matest0.id)
1571 -> Append
1572 -> Seq Scan on public.matest0 matest0_1
1573 Output: matest0_1.id, matest0_1.name
1574 -> Seq Scan on public.matest1 matest0_2
1575 Output: matest0_2.id, matest0_2.name
1576 -> Seq Scan on public.matest2 matest0_3
1577 Output: matest0_3.id, matest0_3.name
1578 -> Seq Scan on public.matest3 matest0_4
1579 Output: matest0_4.id, matest0_4.name
1580 (14 rows)
1582 select * from matest0 order by 1-id;
1583 id | name
1584 ----+--------
1585 6 | Test 6
1586 5 | Test 5
1587 4 | Test 4
1588 3 | Test 3
1589 2 | Test 2
1590 1 | Test 1
1591 (6 rows)
1593 explain (verbose, costs off) select min(1-id) from matest0;
1594 QUERY PLAN
1595 --------------------------------------------------
1596 Aggregate
1597 Output: min((1 - matest0.id))
1598 -> Append
1599 -> Seq Scan on public.matest0 matest0_1
1600 Output: matest0_1.id
1601 -> Seq Scan on public.matest1 matest0_2
1602 Output: matest0_2.id
1603 -> Seq Scan on public.matest2 matest0_3
1604 Output: matest0_3.id
1605 -> Seq Scan on public.matest3 matest0_4
1606 Output: matest0_4.id
1607 (11 rows)
1609 select min(1-id) from matest0;
1610 min
1611 -----
1612 -5
1613 (1 row)
1615 reset enable_indexscan;
1616 set enable_seqscan = off; -- plan with fewest seqscans should be merge
1617 set enable_parallel_append = off; -- Don't let parallel-append interfere
1618 explain (verbose, costs off) select * from matest0 order by 1-id;
1619 QUERY PLAN
1620 ------------------------------------------------------------------------
1621 Merge Append
1622 Sort Key: ((1 - matest0.id))
1623 -> Index Scan using matest0i on public.matest0 matest0_1
1624 Output: matest0_1.id, matest0_1.name, (1 - matest0_1.id)
1625 -> Index Scan using matest1i on public.matest1 matest0_2
1626 Output: matest0_2.id, matest0_2.name, (1 - matest0_2.id)
1627 -> Sort
1628 Output: matest0_3.id, matest0_3.name, ((1 - matest0_3.id))
1629 Sort Key: ((1 - matest0_3.id))
1630 -> Seq Scan on public.matest2 matest0_3
1631 Disabled: true
1632 Output: matest0_3.id, matest0_3.name, (1 - matest0_3.id)
1633 -> Index Scan using matest3i on public.matest3 matest0_4
1634 Output: matest0_4.id, matest0_4.name, (1 - matest0_4.id)
1635 (14 rows)
1637 select * from matest0 order by 1-id;
1638 id | name
1639 ----+--------
1640 6 | Test 6
1641 5 | Test 5
1642 4 | Test 4
1643 3 | Test 3
1644 2 | Test 2
1645 1 | Test 1
1646 (6 rows)
1648 explain (verbose, costs off) select min(1-id) from matest0;
1649 QUERY PLAN
1650 ---------------------------------------------------------------------------------
1651 Result
1652 Output: (InitPlan 1).col1
1653 InitPlan 1
1654 -> Limit
1655 Output: ((1 - matest0.id))
1656 -> Result
1657 Output: ((1 - matest0.id))
1658 -> Merge Append
1659 Sort Key: ((1 - matest0.id))
1660 -> Index Scan using matest0i on public.matest0 matest0_1
1661 Output: matest0_1.id, (1 - matest0_1.id)
1662 Index Cond: ((1 - matest0_1.id) IS NOT NULL)
1663 -> Index Scan using matest1i on public.matest1 matest0_2
1664 Output: matest0_2.id, (1 - matest0_2.id)
1665 Index Cond: ((1 - matest0_2.id) IS NOT NULL)
1666 -> Sort
1667 Output: matest0_3.id, ((1 - matest0_3.id))
1668 Sort Key: ((1 - matest0_3.id))
1669 -> Bitmap Heap Scan on public.matest2 matest0_3
1670 Output: matest0_3.id, (1 - matest0_3.id)
1671 Filter: ((1 - matest0_3.id) IS NOT NULL)
1672 -> Bitmap Index Scan on matest2_pkey
1673 -> Index Scan using matest3i on public.matest3 matest0_4
1674 Output: matest0_4.id, (1 - matest0_4.id)
1675 Index Cond: ((1 - matest0_4.id) IS NOT NULL)
1676 (25 rows)
1678 select min(1-id) from matest0;
1679 min
1680 -----
1681 -5
1682 (1 row)
1684 reset enable_seqscan;
1685 reset enable_parallel_append;
1686 explain (verbose, costs off) -- bug #18652
1687 select 1 - id as c from
1688 (select id from matest3 t1 union all select id * 2 from matest3 t2) ss
1689 order by c;
1690 QUERY PLAN
1691 ------------------------------------------------------------
1692 Result
1693 Output: ((1 - t1.id))
1694 -> Merge Append
1695 Sort Key: ((1 - t1.id))
1696 -> Index Scan using matest3i on public.matest3 t1
1697 Output: t1.id, (1 - t1.id)
1698 -> Sort
1699 Output: ((t2.id * 2)), ((1 - (t2.id * 2)))
1700 Sort Key: ((1 - (t2.id * 2)))
1701 -> Seq Scan on public.matest3 t2
1702 Output: (t2.id * 2), (1 - (t2.id * 2))
1703 (11 rows)
1705 select 1 - id as c from
1706 (select id from matest3 t1 union all select id * 2 from matest3 t2) ss
1707 order by c;
1708 c
1709 -----
1710 -11
1711 -9
1712 -5
1713 -4
1714 (4 rows)
1716 drop table matest0 cascade;
1717 NOTICE: drop cascades to 3 other objects
1718 DETAIL: drop cascades to table matest1
1719 drop cascades to table matest2
1720 drop cascades to table matest3
1721 --
1722 -- Check that use of an index with an extraneous column doesn't produce
1723 -- a plan with extraneous sorting
1724 --
1725 create table matest0 (a int, b int, c int, d int);
1726 create table matest1 () inherits(matest0);
1727 create index matest0i on matest0 (b, c);
1728 create index matest1i on matest1 (b, c);
1729 set enable_nestloop = off; -- we want a plan with two MergeAppends
1730 explain (costs off)
1731 select t1.* from matest0 t1, matest0 t2
1732 where t1.b = t2.b and t2.c = t2.d
1733 order by t1.b limit 10;
1734 QUERY PLAN
1735 -------------------------------------------------------------------
1736 Limit
1737 -> Merge Join
1738 Merge Cond: (t1.b = t2.b)
1739 -> Merge Append
1740 Sort Key: t1.b
1741 -> Index Scan using matest0i on matest0 t1_1
1742 -> Index Scan using matest1i on matest1 t1_2
1743 -> Materialize
1744 -> Merge Append
1745 Sort Key: t2.b
1746 -> Index Scan using matest0i on matest0 t2_1
1747 Filter: (c = d)
1748 -> Index Scan using matest1i on matest1 t2_2
1749 Filter: (c = d)
1750 (14 rows)
1752 reset enable_nestloop;
1753 drop table matest0 cascade;
1754 NOTICE: drop cascades to table matest1
1755 -- Test a MergeAppend plan where one child requires a sort
1756 create table matest0(a int primary key);
1757 create table matest1() inherits (matest0);
1758 insert into matest0 select generate_series(1, 400);
1759 insert into matest1 select generate_series(1, 200);
1760 analyze matest0;
1761 analyze matest1;
1762 explain (costs off)
1763 select * from matest0 where a < 100 order by a;
1764 QUERY PLAN
1765 ---------------------------------------------------------------
1766 Merge Append
1767 Sort Key: matest0.a
1768 -> Index Only Scan using matest0_pkey on matest0 matest0_1
1769 Index Cond: (a < 100)
1770 -> Sort
1771 Sort Key: matest0_2.a
1772 -> Seq Scan on matest1 matest0_2
1773 Filter: (a < 100)
1774 (8 rows)
1776 drop table matest0 cascade;
1777 NOTICE: drop cascades to table matest1
1778 --
1779 -- Test merge-append for UNION ALL append relations
1780 --
1781 set enable_seqscan = off;
1782 set enable_indexscan = on;
1783 set enable_bitmapscan = off;
1784 -- Check handling of duplicated, constant, or volatile targetlist items
1785 explain (costs off)
1786 SELECT thousand, tenthous FROM tenk1
1787 UNION ALL
1788 SELECT thousand, thousand FROM tenk1
1789 ORDER BY thousand, tenthous;
1790 QUERY PLAN
1791 -------------------------------------------------------------------------
1792 Merge Append
1793 Sort Key: tenk1.thousand, tenk1.tenthous
1794 -> Index Only Scan using tenk1_thous_tenthous on tenk1
1795 -> Sort
1796 Sort Key: tenk1_1.thousand, tenk1_1.thousand
1797 -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
1798 (6 rows)
1800 explain (costs off)
1801 SELECT thousand, tenthous, thousand+tenthous AS x FROM tenk1
1802 UNION ALL
1803 SELECT 42, 42, hundred FROM tenk1
1804 ORDER BY thousand, tenthous;
1805 QUERY PLAN
1806 ------------------------------------------------------------------
1807 Merge Append
1808 Sort Key: tenk1.thousand, tenk1.tenthous
1809 -> Index Only Scan using tenk1_thous_tenthous on tenk1
1810 -> Sort
1811 Sort Key: 42, 42
1812 -> Index Only Scan using tenk1_hundred on tenk1 tenk1_1
1813 (6 rows)
1815 explain (costs off)
1816 SELECT thousand, tenthous FROM tenk1
1817 UNION ALL
1818 SELECT thousand, random()::integer FROM tenk1
1819 ORDER BY thousand, tenthous;
1820 QUERY PLAN
1821 -------------------------------------------------------------------------
1822 Merge Append
1823 Sort Key: tenk1.thousand, tenk1.tenthous
1824 -> Index Only Scan using tenk1_thous_tenthous on tenk1
1825 -> Sort
1826 Sort Key: tenk1_1.thousand, ((random())::integer)
1827 -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
1828 (6 rows)
1830 -- Check min/max aggregate optimization
1831 explain (costs off)
1832 SELECT min(x) FROM
1833 (SELECT unique1 AS x FROM tenk1 a
1834 UNION ALL
1835 SELECT unique2 AS x FROM tenk1 b) s;
1836 QUERY PLAN
1837 --------------------------------------------------------------------
1838 Result
1839 InitPlan 1
1840 -> Limit
1841 -> Merge Append
1842 Sort Key: a.unique1
1843 -> Index Only Scan using tenk1_unique1 on tenk1 a
1844 Index Cond: (unique1 IS NOT NULL)
1845 -> Index Only Scan using tenk1_unique2 on tenk1 b
1846 Index Cond: (unique2 IS NOT NULL)
1847 (9 rows)
1849 explain (costs off)
1850 SELECT min(y) FROM
1851 (SELECT unique1 AS x, unique1 AS y FROM tenk1 a
1852 UNION ALL
1853 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s;
1854 QUERY PLAN
1855 --------------------------------------------------------------------
1856 Result
1857 InitPlan 1
1858 -> Limit
1859 -> Merge Append
1860 Sort Key: a.unique1
1861 -> Index Only Scan using tenk1_unique1 on tenk1 a
1862 Index Cond: (unique1 IS NOT NULL)
1863 -> Index Only Scan using tenk1_unique2 on tenk1 b
1864 Index Cond: (unique2 IS NOT NULL)
1865 (9 rows)
1867 -- XXX planner doesn't recognize that index on unique2 is sufficiently sorted
1868 explain (costs off)
1869 SELECT x, y FROM
1870 (SELECT thousand AS x, tenthous AS y FROM tenk1 a
1871 UNION ALL
1872 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s
1873 ORDER BY x, y;
1874 QUERY PLAN
1875 -------------------------------------------------------------
1876 Merge Append
1877 Sort Key: a.thousand, a.tenthous
1878 -> Index Only Scan using tenk1_thous_tenthous on tenk1 a
1879 -> Sort
1880 Sort Key: b.unique2, b.unique2
1881 -> Index Only Scan using tenk1_unique2 on tenk1 b
1882 (6 rows)
1884 -- exercise rescan code path via a repeatedly-evaluated subquery
1885 explain (costs off)
1886 SELECT
1887 ARRAY(SELECT f.i FROM (
1888 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1)
1889 UNION ALL
1890 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1)
1891 ) f(i)
1892 ORDER BY f.i LIMIT 10)
1893 FROM generate_series(1, 3) g(i);
1894 QUERY PLAN
1895 ----------------------------------------------------------------
1896 Function Scan on generate_series g
1897 SubPlan 1
1898 -> Limit
1899 -> Merge Append
1900 Sort Key: ((d.d + g.i))
1901 -> Sort
1902 Sort Key: ((d.d + g.i))
1903 -> Function Scan on generate_series d
1904 -> Sort
1905 Sort Key: ((d_1.d + g.i))
1906 -> Function Scan on generate_series d_1
1907 (11 rows)
1909 SELECT
1910 ARRAY(SELECT f.i FROM (
1911 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1)
1912 UNION ALL
1913 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1)
1914 ) f(i)
1915 ORDER BY f.i LIMIT 10)
1916 FROM generate_series(1, 3) g(i);
1917 array
1918 ------------------------------
1919 {1,5,6,8,11,11,14,16,17,20}
1920 {2,6,7,9,12,12,15,17,18,21}
1921 {3,7,8,10,13,13,16,18,19,22}
1922 (3 rows)
1924 reset enable_seqscan;
1925 reset enable_indexscan;
1926 reset enable_bitmapscan;
1927 --
1928 -- Check handling of MULTIEXPR SubPlans in inherited updates
1929 --
1930 create table inhpar(f1 int, f2 name);
1931 create table inhcld(f2 name, f1 int);
1932 alter table inhcld inherit inhpar;
1933 insert into inhpar select x, x::text from generate_series(1,5) x;
1934 insert into inhcld select x::text, x from generate_series(6,10) x;
1935 explain (verbose, costs off)
1936 update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
1937 QUERY PLAN
1938 --------------------------------------------------------------------------------------------
1939 Update on public.inhpar i
1940 Update on public.inhpar i_1
1941 Update on public.inhcld i_2
1942 -> Result
1943 Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), i.tableoid, i.ctid
1944 -> Append
1945 -> Seq Scan on public.inhpar i_1
1946 Output: i_1.f1, i_1.f2, i_1.tableoid, i_1.ctid
1947 -> Seq Scan on public.inhcld i_2
1948 Output: i_2.f1, i_2.f2, i_2.tableoid, i_2.ctid
1949 SubPlan 1
1950 -> Limit
1951 Output: (i.f1), (((i.f2)::text || '-'::text))
1952 -> Seq Scan on public.int4_tbl
1953 Output: i.f1, ((i.f2)::text || '-'::text)
1954 (15 rows)
1956 update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
1957 select * from inhpar;
1958 f1 | f2
1959 ----+-----
1960 1 | 1-
1961 2 | 2-
1962 3 | 3-
1963 4 | 4-
1964 5 | 5-
1965 6 | 6-
1966 7 | 7-
1967 8 | 8-
1968 9 | 9-
1969 10 | 10-
1970 (10 rows)
1972 drop table inhpar cascade;
1973 NOTICE: drop cascades to table inhcld
1974 --
1975 -- And the same for partitioned cases
1976 --
1977 create table inhpar(f1 int primary key, f2 name) partition by range (f1);
1978 create table inhcld1(f2 name, f1 int primary key);
1979 create table inhcld2(f1 int primary key, f2 name);
1980 alter table inhpar attach partition inhcld1 for values from (1) to (5);
1981 alter table inhpar attach partition inhcld2 for values from (5) to (100);
1982 insert into inhpar select x, x::text from generate_series(1,10) x;
1983 explain (verbose, costs off)
1984 update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
1985 QUERY PLAN
1986 ------------------------------------------------------------------------------------------------------
1987 Update on public.inhpar i
1988 Update on public.inhcld1 i_1
1989 Update on public.inhcld2 i_2
1990 -> Append
1991 -> Seq Scan on public.inhcld1 i_1
1992 Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), i_1.tableoid, i_1.ctid
1993 SubPlan 1
1994 -> Limit
1995 Output: (i_1.f1), (((i_1.f2)::text || '-'::text))
1996 -> Seq Scan on public.int4_tbl
1997 Output: i_1.f1, ((i_1.f2)::text || '-'::text)
1998 -> Seq Scan on public.inhcld2 i_2
1999 Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), i_2.tableoid, i_2.ctid
2000 (13 rows)
2002 update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
2003 select * from inhpar;
2004 f1 | f2
2005 ----+-----
2006 1 | 1-
2007 2 | 2-
2008 3 | 3-
2009 4 | 4-
2010 5 | 5-
2011 6 | 6-
2012 7 | 7-
2013 8 | 8-
2014 9 | 9-
2015 10 | 10-
2016 (10 rows)
2018 -- Also check ON CONFLICT
2019 insert into inhpar as i values (3), (7) on conflict (f1)
2020 do update set (f1, f2) = (select i.f1, i.f2 || '+');
2021 select * from inhpar order by f1; -- tuple order might be unstable here
2022 f1 | f2
2023 ----+-----
2024 1 | 1-
2025 2 | 2-
2026 3 | 3-+
2027 4 | 4-
2028 5 | 5-
2029 6 | 6-
2030 7 | 7-+
2031 8 | 8-
2032 9 | 9-
2033 10 | 10-
2034 (10 rows)
2036 drop table inhpar cascade;
2037 --
2038 -- Check handling of a constant-null CHECK constraint
2039 --
2040 create table cnullparent (f1 int);
2041 create table cnullchild (check (f1 = 1 or f1 = null)) inherits(cnullparent);
2042 insert into cnullchild values(1);
2043 insert into cnullchild values(2);
2044 insert into cnullchild values(null);
2045 select * from cnullparent;
2046 f1
2047 ----
2048 1
2049 2
2051 (3 rows)
2053 select * from cnullparent where f1 = 2;
2054 f1
2055 ----
2056 2
2057 (1 row)
2059 drop table cnullparent cascade;
2060 NOTICE: drop cascades to table cnullchild
2061 --
2062 -- Test inheritance of NOT NULL constraints
2063 --
2064 create table pp1 (f1 int);
2065 create table cc1 (f2 text, f3 int) inherits (pp1);
2066 create table cc2 (f4 float) inherits (pp1,cc1);
2067 NOTICE: merging multiple inherited definitions of column "f1"
2068 create table cc3 () inherits (pp1,cc1,cc2);
2069 NOTICE: merging multiple inherited definitions of column "f1"
2070 NOTICE: merging multiple inherited definitions of column "f1"
2071 NOTICE: merging multiple inherited definitions of column "f2"
2072 NOTICE: merging multiple inherited definitions of column "f3"
2073 alter table pp1 alter f1 set not null;
2074 \d+ cc3
2075 Table "public.cc3"
2076 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2077 --------+------------------+-----------+----------+---------+----------+--------------+-------------
2078 f1 | integer | | not null | | plain | |
2079 f2 | text | | | | extended | |
2080 f3 | integer | | | | plain | |
2081 f4 | double precision | | | | plain | |
2082 Not-null constraints:
2083 "pp1_f1_not_null" NOT NULL "f1" (inherited)
2084 Inherits: pp1,
2085 cc1,
2086 cc2
2088 alter table cc3 no inherit pp1;
2089 alter table cc3 no inherit cc1;
2090 alter table cc3 no inherit cc2;
2091 \d+ cc3
2092 Table "public.cc3"
2093 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2094 --------+------------------+-----------+----------+---------+----------+--------------+-------------
2095 f1 | integer | | not null | | plain | |
2096 f2 | text | | | | extended | |
2097 f3 | integer | | | | plain | |
2098 f4 | double precision | | | | plain | |
2099 Not-null constraints:
2100 "pp1_f1_not_null" NOT NULL "f1"
2102 drop table cc3;
2103 -- named NOT NULL constraint
2104 alter table cc1 add column a2 int constraint nn not null;
2105 \d+ cc1
2106 Table "public.cc1"
2107 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2108 --------+---------+-----------+----------+---------+----------+--------------+-------------
2109 f1 | integer | | not null | | plain | |
2110 f2 | text | | | | extended | |
2111 f3 | integer | | | | plain | |
2112 a2 | integer | | not null | | plain | |
2113 Not-null constraints:
2114 "pp1_f1_not_null" NOT NULL "f1" (inherited)
2115 "nn" NOT NULL "a2"
2116 Inherits: pp1
2117 Child tables: cc2
2119 \d+ cc2
2120 Table "public.cc2"
2121 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2122 --------+------------------+-----------+----------+---------+----------+--------------+-------------
2123 f1 | integer | | not null | | plain | |
2124 f2 | text | | | | extended | |
2125 f3 | integer | | | | plain | |
2126 f4 | double precision | | | | plain | |
2127 a2 | integer | | not null | | plain | |
2128 Not-null constraints:
2129 "pp1_f1_not_null" NOT NULL "f1" (inherited)
2130 "nn" NOT NULL "a2" (inherited)
2131 Inherits: pp1,
2132 cc1
2134 alter table pp1 alter column f1 set not null;
2135 \d+ pp1
2136 Table "public.pp1"
2137 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2138 --------+---------+-----------+----------+---------+---------+--------------+-------------
2139 f1 | integer | | not null | | plain | |
2140 Not-null constraints:
2141 "pp1_f1_not_null" NOT NULL "f1"
2142 Child tables: cc1,
2143 cc2
2145 \d+ cc1
2146 Table "public.cc1"
2147 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2148 --------+---------+-----------+----------+---------+----------+--------------+-------------
2149 f1 | integer | | not null | | plain | |
2150 f2 | text | | | | extended | |
2151 f3 | integer | | | | plain | |
2152 a2 | integer | | not null | | plain | |
2153 Not-null constraints:
2154 "pp1_f1_not_null" NOT NULL "f1" (inherited)
2155 "nn" NOT NULL "a2"
2156 Inherits: pp1
2157 Child tables: cc2
2159 \d+ cc2
2160 Table "public.cc2"
2161 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2162 --------+------------------+-----------+----------+---------+----------+--------------+-------------
2163 f1 | integer | | not null | | plain | |
2164 f2 | text | | | | extended | |
2165 f3 | integer | | | | plain | |
2166 f4 | double precision | | | | plain | |
2167 a2 | integer | | not null | | plain | |
2168 Not-null constraints:
2169 "pp1_f1_not_null" NOT NULL "f1" (inherited)
2170 "nn" NOT NULL "a2" (inherited)
2171 Inherits: pp1,
2172 cc1
2174 -- cannot create table with inconsistent NO INHERIT constraint
2175 create table cc3 (a2 int not null no inherit) inherits (cc1);
2176 NOTICE: moving and merging column "a2" with inherited definition
2177 DETAIL: User-specified column moved to the position of the inherited column.
2178 ERROR: cannot define not-null constraint on column "a2" with NO INHERIT
2179 DETAIL: The column has an inherited not-null constraint.
2180 -- change NO INHERIT status of inherited constraint: no dice, it's inherited
2181 alter table cc2 add not null a2 no inherit;
2182 ERROR: cannot change NO INHERIT status of NOT NULL constraint "nn" on relation "cc2"
2183 -- remove constraint from cc2: no dice, it's inherited
2184 alter table cc2 alter column a2 drop not null;
2185 ERROR: cannot drop inherited constraint "nn" of relation "cc2"
2186 -- remove constraint from cc1, should succeed
2187 alter table cc1 alter column a2 drop not null;
2188 \d+ cc1
2189 Table "public.cc1"
2190 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2191 --------+---------+-----------+----------+---------+----------+--------------+-------------
2192 f1 | integer | | not null | | plain | |
2193 f2 | text | | | | extended | |
2194 f3 | integer | | | | plain | |
2195 a2 | integer | | | | plain | |
2196 Not-null constraints:
2197 "pp1_f1_not_null" NOT NULL "f1" (inherited)
2198 Inherits: pp1
2199 Child tables: cc2
2201 -- same for cc2
2202 alter table cc2 alter column f1 drop not null;
2203 ERROR: cannot drop inherited constraint "pp1_f1_not_null" of relation "cc2"
2204 \d+ cc2
2205 Table "public.cc2"
2206 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2207 --------+------------------+-----------+----------+---------+----------+--------------+-------------
2208 f1 | integer | | not null | | plain | |
2209 f2 | text | | | | extended | |
2210 f3 | integer | | | | plain | |
2211 f4 | double precision | | | | plain | |
2212 a2 | integer | | | | plain | |
2213 Not-null constraints:
2214 "pp1_f1_not_null" NOT NULL "f1" (inherited)
2215 Inherits: pp1,
2216 cc1
2218 -- remove from cc1, should fail again
2219 alter table cc1 alter column f1 drop not null;
2220 ERROR: cannot drop inherited constraint "pp1_f1_not_null" of relation "cc1"
2221 -- remove from pp1, should succeed
2222 alter table pp1 alter column f1 drop not null;
2223 \d+ pp1
2224 Table "public.pp1"
2225 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2226 --------+---------+-----------+----------+---------+---------+--------------+-------------
2227 f1 | integer | | | | plain | |
2228 Child tables: cc1,
2229 cc2
2231 alter table pp1 add primary key (f1);
2232 -- Leave these tables around, for pg_upgrade testing
2233 -- test that removing inheritance of NOT NULL NO INHERIT works correctly
2234 create table inh_parent (f1 int not null no inherit, f2 int not null no inherit);
2235 create table inh_child (f1 int not null no inherit, f2 int);
2236 alter table inh_child inherit inh_parent;
2237 alter table inh_child no inherit inh_parent;
2238 \d+ inh_child
2239 Table "public.inh_child"
2240 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2241 --------+---------+-----------+----------+---------+---------+--------------+-------------
2242 f1 | integer | | not null | | plain | |
2243 f2 | integer | | | | plain | |
2244 Not-null constraints:
2245 "inh_child_f1_not_null" NOT NULL "f1" NO INHERIT
2247 drop table inh_parent, inh_child;
2248 -- test that inhcount is updated correctly through multiple inheritance
2249 create table inh_pp1 (f1 int);
2250 create table inh_cc1 (f2 text, f3 int) inherits (inh_pp1);
2251 create table inh_cc2(f4 float) inherits(inh_pp1,inh_cc1);
2252 NOTICE: merging multiple inherited definitions of column "f1"
2253 alter table inh_pp1 alter column f1 set not null;
2254 alter table inh_cc2 no inherit inh_pp1;
2255 alter table inh_cc2 no inherit inh_cc1;
2256 \d+ inh_cc2
2257 Table "public.inh_cc2"
2258 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2259 --------+------------------+-----------+----------+---------+----------+--------------+-------------
2260 f1 | integer | | not null | | plain | |
2261 f2 | text | | | | extended | |
2262 f3 | integer | | | | plain | |
2263 f4 | double precision | | | | plain | |
2264 Not-null constraints:
2265 "inh_pp1_f1_not_null" NOT NULL "f1"
2267 drop table inh_pp1, inh_cc1, inh_cc2;
2268 create table inh_pp1 (f1 int not null);
2269 create table inh_cc1 (f2 text, f3 int) inherits (inh_pp1);
2270 create table inh_cc2(f4 float) inherits(inh_pp1,inh_cc1);
2271 NOTICE: merging multiple inherited definitions of column "f1"
2272 alter table inh_pp1 alter column f1 drop not null;
2273 \d+ inh_cc2
2274 Table "public.inh_cc2"
2275 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2276 --------+------------------+-----------+----------+---------+----------+--------------+-------------
2277 f1 | integer | | | | plain | |
2278 f2 | text | | | | extended | |
2279 f3 | integer | | | | plain | |
2280 f4 | double precision | | | | plain | |
2281 Inherits: inh_pp1,
2282 inh_cc1
2284 drop table inh_pp1, inh_cc1, inh_cc2;
2285 -- Test a not-null addition that must walk down the hierarchy
2286 CREATE TABLE inh_parent ();
2287 CREATE TABLE inh_child (i int) INHERITS (inh_parent);
2288 CREATE TABLE inh_grandchild () INHERITS (inh_parent, inh_child);
2289 ALTER TABLE inh_parent ADD COLUMN i int NOT NULL;
2290 NOTICE: merging definition of column "i" for child "inh_child"
2291 NOTICE: merging definition of column "i" for child "inh_grandchild"
2292 drop table inh_parent, inh_child, inh_grandchild;
2293 -- Test the same constraint name for different columns in different parents
2294 create table inh_parent1(a int constraint nn not null);
2295 create table inh_parent2(b int constraint nn not null);
2296 create table inh_child1 () inherits (inh_parent1, inh_parent2);
2297 \d+ inh_child1
2298 Table "public.inh_child1"
2299 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2300 --------+---------+-----------+----------+---------+---------+--------------+-------------
2301 a | integer | | not null | | plain | |
2302 b | integer | | not null | | plain | |
2303 Not-null constraints:
2304 "nn" NOT NULL "a" (inherited)
2305 "inh_child1_b_not_null" NOT NULL "b" (inherited)
2306 Inherits: inh_parent1,
2307 inh_parent2
2309 create table inh_child2 (constraint foo not null a) inherits (inh_parent1, inh_parent2);
2310 alter table inh_child2 no inherit inh_parent2;
2311 \d+ inh_child2
2312 Table "public.inh_child2"
2313 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2314 --------+---------+-----------+----------+---------+---------+--------------+-------------
2315 a | integer | | not null | | plain | |
2316 b | integer | | not null | | plain | |
2317 Not-null constraints:
2318 "foo" NOT NULL "a" (local, inherited)
2319 "nn" NOT NULL "b"
2320 Inherits: inh_parent1
2322 drop table inh_parent1, inh_parent2, inh_child1, inh_child2;
2323 -- Test multiple parents with overlapping primary keys
2324 create table inh_parent1(a int, b int, c int, primary key (a, b));
2325 create table inh_parent2(d int, e int, b int, primary key (d, b));
2326 create table inh_child() inherits (inh_parent1, inh_parent2);
2327 NOTICE: merging multiple inherited definitions of column "b"
2328 select conrelid::regclass, conname, contype, conkey,
2329 coninhcount, conislocal, connoinherit
2330 from pg_constraint where contype in ('n','p') and
2331 conrelid::regclass::text in ('inh_child', 'inh_parent1', 'inh_parent2')
2332 order by 1, 2;
2333 conrelid | conname | contype | conkey | coninhcount | conislocal | connoinherit
2334 -------------+------------------------+---------+--------+-------------+------------+--------------
2335 inh_parent1 | inh_parent1_a_not_null | n | {1} | 0 | t | f
2336 inh_parent1 | inh_parent1_b_not_null | n | {2} | 0 | t | f
2337 inh_parent1 | inh_parent1_pkey | p | {1,2} | 0 | t | t
2338 inh_parent2 | inh_parent2_b_not_null | n | {3} | 0 | t | f
2339 inh_parent2 | inh_parent2_d_not_null | n | {1} | 0 | t | f
2340 inh_parent2 | inh_parent2_pkey | p | {1,3} | 0 | t | t
2341 inh_child | inh_parent1_a_not_null | n | {1} | 1 | f | f
2342 inh_child | inh_parent1_b_not_null | n | {2} | 2 | f | f
2343 inh_child | inh_parent2_d_not_null | n | {4} | 1 | f | f
2344 (9 rows)
2346 \d+ inh_child
2347 Table "public.inh_child"
2348 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2349 --------+---------+-----------+----------+---------+---------+--------------+-------------
2350 a | integer | | not null | | plain | |
2351 b | integer | | not null | | plain | |
2352 c | integer | | | | plain | |
2353 d | integer | | not null | | plain | |
2354 e | integer | | | | plain | |
2355 Not-null constraints:
2356 "inh_parent1_a_not_null" NOT NULL "a" (inherited)
2357 "inh_parent1_b_not_null" NOT NULL "b" (inherited)
2358 "inh_parent2_d_not_null" NOT NULL "d" (inherited)
2359 Inherits: inh_parent1,
2360 inh_parent2
2362 drop table inh_parent1, inh_parent2, inh_child;
2363 -- NOT NULL NO INHERIT
2364 create table inh_nn_parent(a int);
2365 create table inh_nn_child() inherits (inh_nn_parent);
2366 alter table inh_nn_parent add not null a no inherit;
2367 create table inh_nn_child2() inherits (inh_nn_parent);
2368 select conrelid::regclass, conname, contype, conkey,
2369 (select attname from pg_attribute where attrelid = conrelid and attnum = conkey[1]),
2370 coninhcount, conislocal, connoinherit
2371 from pg_constraint where contype = 'n' and
2372 conrelid::regclass::text like 'inh\_nn\_%'
2373 order by 2, 1;
2374 conrelid | conname | contype | conkey | attname | coninhcount | conislocal | connoinherit
2375 ---------------+--------------------------+---------+--------+---------+-------------+------------+--------------
2376 inh_nn_parent | inh_nn_parent_a_not_null | n | {1} | a | 0 | t | t
2377 (1 row)
2379 \d+ inh_nn*
2380 Table "public.inh_nn_child"
2381 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2382 --------+---------+-----------+----------+---------+---------+--------------+-------------
2383 a | integer | | | | plain | |
2384 Inherits: inh_nn_parent
2386 Table "public.inh_nn_child2"
2387 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2388 --------+---------+-----------+----------+---------+---------+--------------+-------------
2389 a | integer | | | | plain | |
2390 Inherits: inh_nn_parent
2392 Table "public.inh_nn_parent"
2393 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2394 --------+---------+-----------+----------+---------+---------+--------------+-------------
2395 a | integer | | not null | | plain | |
2396 Not-null constraints:
2397 "inh_nn_parent_a_not_null" NOT NULL "a" NO INHERIT
2398 Child tables: inh_nn_child,
2399 inh_nn_child2
2401 drop table inh_nn_parent, inh_nn_child, inh_nn_child2;
2402 CREATE TABLE inh_nn_parent (a int, NOT NULL a NO INHERIT);
2403 CREATE TABLE inh_nn_child() INHERITS (inh_nn_parent);
2404 ALTER TABLE inh_nn_parent ADD CONSTRAINT nna NOT NULL a;
2405 ERROR: cannot change NO INHERIT status of NOT NULL constraint "inh_nn_parent_a_not_null" on relation "inh_nn_parent"
2406 ALTER TABLE inh_nn_parent ALTER a SET NOT NULL;
2407 ERROR: cannot change NO INHERIT status of NOT NULL constraint "inh_nn_parent_a_not_null" on relation "inh_nn_parent"
2408 DROP TABLE inh_nn_parent cascade;
2409 NOTICE: drop cascades to table inh_nn_child
2410 -- Adding a PK at the top level of a hierarchy should cause all descendants
2411 -- to be checked for nulls, even past a no-inherit constraint
2412 CREATE TABLE inh_nn_lvl1 (a int);
2413 CREATE TABLE inh_nn_lvl2 () INHERITS (inh_nn_lvl1);
2414 CREATE TABLE inh_nn_lvl3 (CONSTRAINT foo NOT NULL a NO INHERIT) INHERITS (inh_nn_lvl2);
2415 ALTER TABLE inh_nn_lvl1 ADD PRIMARY KEY (a);
2416 ERROR: cannot change NO INHERIT status of NOT NULL constraint "foo" on relation "inh_nn_lvl3"
2417 DROP TABLE inh_nn_lvl1, inh_nn_lvl2, inh_nn_lvl3;
2418 -- Disallow specifying conflicting NO INHERIT flags for the same constraint
2419 CREATE TABLE inh_nn1 (a int primary key, b int, not null a no inherit);
2420 ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a"
2421 CREATE TABLE inh_nn1 (a int not null);
2422 CREATE TABLE inh_nn2 (a int not null no inherit) INHERITS (inh_nn1);
2423 NOTICE: merging column "a" with inherited definition
2424 ERROR: cannot define not-null constraint on column "a" with NO INHERIT
2425 DETAIL: The column has an inherited not-null constraint.
2426 CREATE TABLE inh_nn3 (a int not null, b int, not null a no inherit);
2427 ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a"
2428 CREATE TABLE inh_nn4 (a int not null no inherit, b int, not null a);
2429 ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a"
2430 DROP TABLE inh_nn1, inh_nn2, inh_nn3, inh_nn4;
2431 ERROR: table "inh_nn2" does not exist
2432 --
2433 -- test inherit/deinherit
2434 --
2435 create table inh_parent(f1 int);
2436 create table inh_child1(f1 int not null);
2437 create table inh_child2(f1 int);
2438 -- inh_child1 should have not null constraint
2439 alter table inh_child1 inherit inh_parent;
2440 -- should fail, missing NOT NULL constraint
2441 alter table inh_child2 inherit inh_child1;
2442 ERROR: column "f1" in child table "inh_child2" must be marked NOT NULL
2443 alter table inh_child2 alter column f1 set not null;
2444 alter table inh_child2 inherit inh_child1;
2445 -- add NOT NULL constraint recursively
2446 alter table inh_parent alter column f1 set not null;
2447 \d+ inh_parent
2448 Table "public.inh_parent"
2449 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2450 --------+---------+-----------+----------+---------+---------+--------------+-------------
2451 f1 | integer | | not null | | plain | |
2452 Not-null constraints:
2453 "inh_parent_f1_not_null" NOT NULL "f1"
2454 Child tables: inh_child1
2456 \d+ inh_child1
2457 Table "public.inh_child1"
2458 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2459 --------+---------+-----------+----------+---------+---------+--------------+-------------
2460 f1 | integer | | not null | | plain | |
2461 Not-null constraints:
2462 "inh_child1_f1_not_null" NOT NULL "f1" (local, inherited)
2463 Inherits: inh_parent
2464 Child tables: inh_child2
2466 \d+ inh_child2
2467 Table "public.inh_child2"
2468 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2469 --------+---------+-----------+----------+---------+---------+--------------+-------------
2470 f1 | integer | | not null | | plain | |
2471 Not-null constraints:
2472 "inh_child2_f1_not_null" NOT NULL "f1" (local, inherited)
2473 Inherits: inh_child1
2475 select conrelid::regclass, conname, contype, coninhcount, conislocal
2476 from pg_constraint where contype = 'n' and
2477 conrelid in ('inh_parent'::regclass, 'inh_child1'::regclass, 'inh_child2'::regclass)
2478 order by 2, 1;
2479 conrelid | conname | contype | coninhcount | conislocal
2480 ------------+------------------------+---------+-------------+------------
2481 inh_child1 | inh_child1_f1_not_null | n | 1 | t
2482 inh_child2 | inh_child2_f1_not_null | n | 1 | t
2483 inh_parent | inh_parent_f1_not_null | n | 0 | t
2484 (3 rows)
2486 --
2487 -- test deinherit procedure
2488 --
2489 -- deinherit inh_child1
2490 create table inh_child3 () inherits (inh_child1);
2491 alter table inh_child1 no inherit inh_parent;
2492 \d+ inh_parent
2493 Table "public.inh_parent"
2494 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2495 --------+---------+-----------+----------+---------+---------+--------------+-------------
2496 f1 | integer | | not null | | plain | |
2497 Not-null constraints:
2498 "inh_parent_f1_not_null" NOT NULL "f1"
2500 \d+ inh_child1
2501 Table "public.inh_child1"
2502 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2503 --------+---------+-----------+----------+---------+---------+--------------+-------------
2504 f1 | integer | | not null | | plain | |
2505 Not-null constraints:
2506 "inh_child1_f1_not_null" NOT NULL "f1"
2507 Child tables: inh_child2,
2508 inh_child3
2510 \d+ inh_child2
2511 Table "public.inh_child2"
2512 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
2513 --------+---------+-----------+----------+---------+---------+--------------+-------------
2514 f1 | integer | | not null | | plain | |
2515 Not-null constraints:
2516 "inh_child2_f1_not_null" NOT NULL "f1" (local, inherited)
2517 Inherits: inh_child1
2519 select conrelid::regclass, conname, contype, coninhcount, conislocal
2520 from pg_constraint where contype = 'n' and
2521 conrelid::regclass::text in ('inh_parent', 'inh_child1', 'inh_child2', 'inh_child3')
2522 order by 2, 1;
2523 conrelid | conname | contype | coninhcount | conislocal
2524 ------------+------------------------+---------+-------------+------------
2525 inh_child1 | inh_child1_f1_not_null | n | 0 | t
2526 inh_child3 | inh_child1_f1_not_null | n | 1 | f
2527 inh_child2 | inh_child2_f1_not_null | n | 1 | t
2528 inh_parent | inh_parent_f1_not_null | n | 0 | t
2529 (4 rows)
2531 drop table inh_parent, inh_child1, inh_child2, inh_child3;
2532 -- ALTER TABLE INHERIT ensures that the child has not-null constraints
2533 create table inh_parent (a int not null);
2534 create table inh_child (a int);
2535 alter table inh_child inherit inh_parent; -- nope
2536 ERROR: column "a" in child table "inh_child" must be marked NOT NULL
2537 drop table inh_parent, inh_child;
2538 -- Can't merge a NO INHERIT constraint with a normal one
2539 create table inh_parent (a int not null);
2540 create table inh_child (a int not null no inherit);
2541 alter table inh_child inherit inh_parent;
2542 ERROR: constraint "inh_child_a_not_null" conflicts with non-inherited constraint on child table "inh_child"
2543 drop table inh_parent, inh_child;
2544 -- don't interfere with other types of constraints
2545 create table inh_parent (a int primary key);
2546 create table inh_child (a int primary key) inherits (inh_parent);
2547 NOTICE: merging column "a" with inherited definition
2548 alter table inh_parent add constraint inh_parent_excl exclude ((1) with =);
2549 alter table inh_parent add constraint inh_parent_uq unique (a);
2550 alter table inh_parent add constraint inh_parent_fk foreign key (a) references inh_parent (a);
2551 create table inh_child2 () inherits (inh_parent);
2552 create table inh_child3 (like inh_parent);
2553 alter table inh_child3 inherit inh_parent;
2554 select conrelid::regclass, conname, contype, coninhcount, conislocal
2555 from pg_constraint
2556 where conrelid::regclass::text in ('inh_parent', 'inh_child', 'inh_child2', 'inh_child3')
2557 order by 2, 1;
2558 conrelid | conname | contype | coninhcount | conislocal
2559 ------------+-----------------------+---------+-------------+------------
2560 inh_child | inh_child_a_not_null | n | 1 | t
2561 inh_child | inh_child_pkey | p | 0 | t
2562 inh_parent | inh_parent_a_not_null | n | 0 | t
2563 inh_child2 | inh_parent_a_not_null | n | 1 | f
2564 inh_child3 | inh_parent_a_not_null | n | 1 | t
2565 inh_parent | inh_parent_excl | x | 0 | t
2566 inh_parent | inh_parent_fk | f | 0 | t
2567 inh_parent | inh_parent_pkey | p | 0 | t
2568 inh_parent | inh_parent_uq | u | 0 | t
2569 (9 rows)
2571 drop table inh_parent, inh_child, inh_child2, inh_child3;
2572 --
2573 -- test multi inheritance tree
2574 --
2575 create table inh_parent(f1 int not null);
2576 create table inh_child1() inherits(inh_parent);
2577 create table inh_child2() inherits(inh_parent);
2578 create table inh_child3() inherits(inh_child1, inh_child2);
2579 NOTICE: merging multiple inherited definitions of column "f1"
2580 -- show constraint info
2581 select conrelid::regclass, conname, contype, coninhcount, conislocal
2582 from pg_constraint where contype = 'n' and
2583 conrelid in ('inh_parent'::regclass, 'inh_child1'::regclass, 'inh_child2'::regclass, 'inh_child3'::regclass)
2584 order by 2, conrelid::regclass::text;
2585 conrelid | conname | contype | coninhcount | conislocal
2586 ------------+------------------------+---------+-------------+------------
2587 inh_child1 | inh_parent_f1_not_null | n | 1 | f
2588 inh_child2 | inh_parent_f1_not_null | n | 1 | f
2589 inh_child3 | inh_parent_f1_not_null | n | 2 | f
2590 inh_parent | inh_parent_f1_not_null | n | 0 | t
2591 (4 rows)
2593 drop table inh_parent cascade;
2594 NOTICE: drop cascades to 3 other objects
2595 DETAIL: drop cascades to table inh_child1
2596 drop cascades to table inh_child2
2597 drop cascades to table inh_child3
2598 -- test child table with inherited columns and
2599 -- with explicitly specified not null constraints
2600 create table inh_parent_1(f1 int);
2601 create table inh_parent_2(f2 text);
2602 create table inh_child(f1 int not null, f2 text not null) inherits(inh_parent_1, inh_parent_2);
2603 NOTICE: merging column "f1" with inherited definition
2604 NOTICE: merging column "f2" with inherited definition
2605 -- show constraint info
2606 select conrelid::regclass, conname, contype, coninhcount, conislocal
2607 from pg_constraint where contype = 'n' and
2608 conrelid in ('inh_parent_1'::regclass, 'inh_parent_2'::regclass, 'inh_child'::regclass)
2609 order by 2, conrelid::regclass::text;
2610 conrelid | conname | contype | coninhcount | conislocal
2611 -----------+-----------------------+---------+-------------+------------
2612 inh_child | inh_child_f1_not_null | n | 0 | t
2613 inh_child | inh_child_f2_not_null | n | 0 | t
2614 (2 rows)
2616 -- also drops inh_child table
2617 drop table inh_parent_1 cascade;
2618 NOTICE: drop cascades to table inh_child
2619 drop table inh_parent_2;
2620 -- test multi layer inheritance tree
2621 create table inh_p1(f1 int not null);
2622 create table inh_p2(f1 int not null);
2623 create table inh_p3(f2 int);
2624 create table inh_p4(f1 int not null, f3 text not null);
2625 create table inh_multiparent() inherits(inh_p1, inh_p2, inh_p3, inh_p4);
2626 NOTICE: merging multiple inherited definitions of column "f1"
2627 NOTICE: merging multiple inherited definitions of column "f1"
2628 -- constraint on f1 should have three parents
2629 select conrelid::regclass, contype, conname,
2630 (select attname from pg_attribute where attrelid = conrelid and attnum = conkey[1]),
2631 coninhcount, conislocal
2632 from pg_constraint where contype = 'n' and
2633 conrelid::regclass in ('inh_p1', 'inh_p2', 'inh_p3', 'inh_p4',
2634 'inh_multiparent')
2635 order by conrelid::regclass::text, conname;
2636 conrelid | contype | conname | attname | coninhcount | conislocal
2637 -----------------+---------+--------------------+---------+-------------+------------
2638 inh_multiparent | n | inh_p1_f1_not_null | f1 | 3 | f
2639 inh_multiparent | n | inh_p4_f3_not_null | f3 | 1 | f
2640 inh_p1 | n | inh_p1_f1_not_null | f1 | 0 | t
2641 inh_p2 | n | inh_p2_f1_not_null | f1 | 0 | t
2642 inh_p4 | n | inh_p4_f1_not_null | f1 | 0 | t
2643 inh_p4 | n | inh_p4_f3_not_null | f3 | 0 | t
2644 (6 rows)
2646 create table inh_multiparent2 (a int not null, f1 int) inherits(inh_p3, inh_multiparent);
2647 NOTICE: merging multiple inherited definitions of column "f2"
2648 NOTICE: merging column "f1" with inherited definition
2649 select conrelid::regclass, contype, conname,
2650 (select attname from pg_attribute where attrelid = conrelid and attnum = conkey[1]),
2651 coninhcount, conislocal
2652 from pg_constraint where contype = 'n' and
2653 conrelid::regclass in ('inh_p3', 'inh_multiparent', 'inh_multiparent2')
2654 order by conrelid::regclass::text, conname;
2655 conrelid | contype | conname | attname | coninhcount | conislocal
2656 ------------------+---------+-----------------------------+---------+-------------+------------
2657 inh_multiparent | n | inh_p1_f1_not_null | f1 | 3 | f
2658 inh_multiparent | n | inh_p4_f3_not_null | f3 | 1 | f
2659 inh_multiparent2 | n | inh_multiparent2_a_not_null | a | 0 | t
2660 inh_multiparent2 | n | inh_p1_f1_not_null | f1 | 1 | f
2661 inh_multiparent2 | n | inh_p4_f3_not_null | f3 | 1 | f
2662 (5 rows)
2664 drop table inh_p1, inh_p2, inh_p3, inh_p4 cascade;
2665 NOTICE: drop cascades to 2 other objects
2666 DETAIL: drop cascades to table inh_multiparent
2667 drop cascades to table inh_multiparent2
2668 --
2669 -- Mixed ownership inheritance tree
2670 --
2671 create role regress_alice;
2672 create role regress_bob;
2673 grant all on schema public to regress_alice, regress_bob;
2674 grant regress_alice to regress_bob;
2675 set session authorization regress_alice;
2676 create table inh_parent (a int not null);
2677 set session authorization regress_bob;
2678 create table inh_child () inherits (inh_parent);
2679 set session authorization regress_alice;
2680 -- alice can't do this: she doesn't own inh_child
2681 alter table inh_parent alter a drop not null;
2682 ERROR: must be owner of table inh_child
2683 set session authorization regress_bob;
2684 alter table inh_parent alter a drop not null;
2685 reset session authorization;
2686 drop table inh_parent, inh_child;
2687 revoke all on schema public from regress_alice, regress_bob;
2688 drop role regress_alice, regress_bob;
2689 --
2690 -- Check use of temporary tables with inheritance trees
2691 --
2692 create table inh_perm_parent (a1 int);
2693 create temp table inh_temp_parent (a1 int);
2694 create temp table inh_temp_child () inherits (inh_perm_parent); -- ok
2695 create table inh_perm_child () inherits (inh_temp_parent); -- error
2696 ERROR: cannot inherit from temporary relation "inh_temp_parent"
2697 create temp table inh_temp_child_2 () inherits (inh_temp_parent); -- ok
2698 insert into inh_perm_parent values (1);
2699 insert into inh_temp_parent values (2);
2700 insert into inh_temp_child values (3);
2701 insert into inh_temp_child_2 values (4);
2702 select tableoid::regclass, a1 from inh_perm_parent;
2703 tableoid | a1
2704 -----------------+----
2705 inh_perm_parent | 1
2706 inh_temp_child | 3
2707 (2 rows)
2709 select tableoid::regclass, a1 from inh_temp_parent;
2710 tableoid | a1
2711 ------------------+----
2712 inh_temp_parent | 2
2713 inh_temp_child_2 | 4
2714 (2 rows)
2716 drop table inh_perm_parent cascade;
2717 NOTICE: drop cascades to table inh_temp_child
2718 drop table inh_temp_parent cascade;
2719 NOTICE: drop cascades to table inh_temp_child_2
2720 --
2721 -- Check that constraint exclusion works correctly with partitions using
2722 -- implicit constraints generated from the partition bound information.
2723 --
2724 create table list_parted (
2725 a varchar
2726 ) partition by list (a);
2727 create table part_ab_cd partition of list_parted for values in ('ab', 'cd');
2728 create table part_ef_gh partition of list_parted for values in ('ef', 'gh');
2729 create table part_null_xy partition of list_parted for values in (null, 'xy');
2730 explain (costs off) select * from list_parted;
2731 QUERY PLAN
2732 ----------------------------------------------
2733 Append
2734 -> Seq Scan on part_ab_cd list_parted_1
2735 -> Seq Scan on part_ef_gh list_parted_2
2736 -> Seq Scan on part_null_xy list_parted_3
2737 (4 rows)
2739 explain (costs off) select * from list_parted where a is null;
2740 QUERY PLAN
2741 --------------------------------------
2742 Seq Scan on part_null_xy list_parted
2743 Filter: (a IS NULL)
2744 (2 rows)
2746 explain (costs off) select * from list_parted where a is not null;
2747 QUERY PLAN
2748 ----------------------------------------------
2749 Append
2750 -> Seq Scan on part_ab_cd list_parted_1
2751 Filter: (a IS NOT NULL)
2752 -> Seq Scan on part_ef_gh list_parted_2
2753 Filter: (a IS NOT NULL)
2754 -> Seq Scan on part_null_xy list_parted_3
2755 Filter: (a IS NOT NULL)
2756 (7 rows)
2758 explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef');
2759 QUERY PLAN
2760 ----------------------------------------------------------
2761 Append
2762 -> Seq Scan on part_ab_cd list_parted_1
2763 Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
2764 -> Seq Scan on part_ef_gh list_parted_2
2765 Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
2766 (5 rows)
2768 explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd');
2769 QUERY PLAN
2770 ---------------------------------------------------------------------------------
2771 Seq Scan on part_ab_cd list_parted
2772 Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
2773 (2 rows)
2775 explain (costs off) select * from list_parted where a = 'ab';
2776 QUERY PLAN
2777 ------------------------------------
2778 Seq Scan on part_ab_cd list_parted
2779 Filter: ((a)::text = 'ab'::text)
2780 (2 rows)
2782 create table range_list_parted (
2783 a int,
2784 b char(2)
2785 ) partition by range (a);
2786 create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b);
2787 create table part_1_10_ab partition of part_1_10 for values in ('ab');
2788 create table part_1_10_cd partition of part_1_10 for values in ('cd');
2789 create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b);
2790 create table part_10_20_ab partition of part_10_20 for values in ('ab');
2791 create table part_10_20_cd partition of part_10_20 for values in ('cd');
2792 create table part_21_30 partition of range_list_parted for values from (21) to (30) partition by list (b);
2793 create table part_21_30_ab partition of part_21_30 for values in ('ab');
2794 create table part_21_30_cd partition of part_21_30 for values in ('cd');
2795 create table part_40_inf partition of range_list_parted for values from (40) to (maxvalue) partition by list (b);
2796 create table part_40_inf_ab partition of part_40_inf for values in ('ab');
2797 create table part_40_inf_cd partition of part_40_inf for values in ('cd');
2798 create table part_40_inf_null partition of part_40_inf for values in (null);
2799 explain (costs off) select * from range_list_parted;
2800 QUERY PLAN
2801 --------------------------------------------------------
2802 Append
2803 -> Seq Scan on part_1_10_ab range_list_parted_1
2804 -> Seq Scan on part_1_10_cd range_list_parted_2
2805 -> Seq Scan on part_10_20_ab range_list_parted_3
2806 -> Seq Scan on part_10_20_cd range_list_parted_4
2807 -> Seq Scan on part_21_30_ab range_list_parted_5
2808 -> Seq Scan on part_21_30_cd range_list_parted_6
2809 -> Seq Scan on part_40_inf_ab range_list_parted_7
2810 -> Seq Scan on part_40_inf_cd range_list_parted_8
2811 -> Seq Scan on part_40_inf_null range_list_parted_9
2812 (10 rows)
2814 explain (costs off) select * from range_list_parted where a = 5;
2815 QUERY PLAN
2816 ----------------------------------------------------
2817 Append
2818 -> Seq Scan on part_1_10_ab range_list_parted_1
2819 Filter: (a = 5)
2820 -> Seq Scan on part_1_10_cd range_list_parted_2
2821 Filter: (a = 5)
2822 (5 rows)
2824 explain (costs off) select * from range_list_parted where b = 'ab';
2825 QUERY PLAN
2826 ------------------------------------------------------
2827 Append
2828 -> Seq Scan on part_1_10_ab range_list_parted_1
2829 Filter: (b = 'ab'::bpchar)
2830 -> Seq Scan on part_10_20_ab range_list_parted_2
2831 Filter: (b = 'ab'::bpchar)
2832 -> Seq Scan on part_21_30_ab range_list_parted_3
2833 Filter: (b = 'ab'::bpchar)
2834 -> Seq Scan on part_40_inf_ab range_list_parted_4
2835 Filter: (b = 'ab'::bpchar)
2836 (9 rows)
2838 explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab');
2839 QUERY PLAN
2840 -----------------------------------------------------------------
2841 Append
2842 -> Seq Scan on part_1_10_ab range_list_parted_1
2843 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
2844 -> Seq Scan on part_10_20_ab range_list_parted_2
2845 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
2846 -> Seq Scan on part_21_30_ab range_list_parted_3
2847 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
2848 (7 rows)
2850 /* Should select no rows because range partition key cannot be null */
2851 explain (costs off) select * from range_list_parted where a is null;
2852 QUERY PLAN
2853 --------------------------
2854 Result
2855 One-Time Filter: false
2856 (2 rows)
2858 /* Should only select rows from the null-accepting partition */
2859 explain (costs off) select * from range_list_parted where b is null;
2860 QUERY PLAN
2861 ------------------------------------------------
2862 Seq Scan on part_40_inf_null range_list_parted
2863 Filter: (b IS NULL)
2864 (2 rows)
2866 explain (costs off) select * from range_list_parted where a is not null and a < 67;
2867 QUERY PLAN
2868 --------------------------------------------------------
2869 Append
2870 -> Seq Scan on part_1_10_ab range_list_parted_1
2871 Filter: ((a IS NOT NULL) AND (a < 67))
2872 -> Seq Scan on part_1_10_cd range_list_parted_2
2873 Filter: ((a IS NOT NULL) AND (a < 67))
2874 -> Seq Scan on part_10_20_ab range_list_parted_3
2875 Filter: ((a IS NOT NULL) AND (a < 67))
2876 -> Seq Scan on part_10_20_cd range_list_parted_4
2877 Filter: ((a IS NOT NULL) AND (a < 67))
2878 -> Seq Scan on part_21_30_ab range_list_parted_5
2879 Filter: ((a IS NOT NULL) AND (a < 67))
2880 -> Seq Scan on part_21_30_cd range_list_parted_6
2881 Filter: ((a IS NOT NULL) AND (a < 67))
2882 -> Seq Scan on part_40_inf_ab range_list_parted_7
2883 Filter: ((a IS NOT NULL) AND (a < 67))
2884 -> Seq Scan on part_40_inf_cd range_list_parted_8
2885 Filter: ((a IS NOT NULL) AND (a < 67))
2886 -> Seq Scan on part_40_inf_null range_list_parted_9
2887 Filter: ((a IS NOT NULL) AND (a < 67))
2888 (19 rows)
2890 explain (costs off) select * from range_list_parted where a >= 30;
2891 QUERY PLAN
2892 --------------------------------------------------------
2893 Append
2894 -> Seq Scan on part_40_inf_ab range_list_parted_1
2895 Filter: (a >= 30)
2896 -> Seq Scan on part_40_inf_cd range_list_parted_2
2897 Filter: (a >= 30)
2898 -> Seq Scan on part_40_inf_null range_list_parted_3
2899 Filter: (a >= 30)
2900 (7 rows)
2902 drop table list_parted;
2903 drop table range_list_parted;
2904 -- check that constraint exclusion is able to cope with the partition
2905 -- constraint emitted for multi-column range partitioned tables
2906 create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
2907 create table mcrparted_def partition of mcrparted default;
2908 create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, 1, 1);
2909 create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
2910 create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
2911 create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
2912 create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
2913 create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue);
2914 explain (costs off) select * from mcrparted where a = 0; -- scans mcrparted0, mcrparted_def
2915 QUERY PLAN
2916 ---------------------------------------------
2917 Append
2918 -> Seq Scan on mcrparted0 mcrparted_1
2919 Filter: (a = 0)
2920 -> Seq Scan on mcrparted_def mcrparted_2
2921 Filter: (a = 0)
2922 (5 rows)
2924 explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5; -- scans mcrparted1, mcrparted_def
2925 QUERY PLAN
2926 ---------------------------------------------
2927 Append
2928 -> Seq Scan on mcrparted1 mcrparted_1
2929 Filter: ((a = 10) AND (abs(b) < 5))
2930 -> Seq Scan on mcrparted_def mcrparted_2
2931 Filter: ((a = 10) AND (abs(b) < 5))
2932 (5 rows)
2934 explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scans mcrparted1, mcrparted2, mcrparted_def
2935 QUERY PLAN
2936 ---------------------------------------------
2937 Append
2938 -> Seq Scan on mcrparted1 mcrparted_1
2939 Filter: ((a = 10) AND (abs(b) = 5))
2940 -> Seq Scan on mcrparted2 mcrparted_2
2941 Filter: ((a = 10) AND (abs(b) = 5))
2942 -> Seq Scan on mcrparted_def mcrparted_3
2943 Filter: ((a = 10) AND (abs(b) = 5))
2944 (7 rows)
2946 explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all partitions
2947 QUERY PLAN
2948 ---------------------------------------------
2949 Append
2950 -> Seq Scan on mcrparted0 mcrparted_1
2951 Filter: (abs(b) = 5)
2952 -> Seq Scan on mcrparted1 mcrparted_2
2953 Filter: (abs(b) = 5)
2954 -> Seq Scan on mcrparted2 mcrparted_3
2955 Filter: (abs(b) = 5)
2956 -> Seq Scan on mcrparted3 mcrparted_4
2957 Filter: (abs(b) = 5)
2958 -> Seq Scan on mcrparted4 mcrparted_5
2959 Filter: (abs(b) = 5)
2960 -> Seq Scan on mcrparted5 mcrparted_6
2961 Filter: (abs(b) = 5)
2962 -> Seq Scan on mcrparted_def mcrparted_7
2963 Filter: (abs(b) = 5)
2964 (15 rows)
2966 explain (costs off) select * from mcrparted where a > -1; -- scans all partitions
2967 QUERY PLAN
2968 ---------------------------------------------
2969 Append
2970 -> Seq Scan on mcrparted0 mcrparted_1
2971 Filter: (a > '-1'::integer)
2972 -> Seq Scan on mcrparted1 mcrparted_2
2973 Filter: (a > '-1'::integer)
2974 -> Seq Scan on mcrparted2 mcrparted_3
2975 Filter: (a > '-1'::integer)
2976 -> Seq Scan on mcrparted3 mcrparted_4
2977 Filter: (a > '-1'::integer)
2978 -> Seq Scan on mcrparted4 mcrparted_5
2979 Filter: (a > '-1'::integer)
2980 -> Seq Scan on mcrparted5 mcrparted_6
2981 Filter: (a > '-1'::integer)
2982 -> Seq Scan on mcrparted_def mcrparted_7
2983 Filter: (a > '-1'::integer)
2984 (15 rows)
2986 explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10; -- scans mcrparted4
2987 QUERY PLAN
2988 -----------------------------------------------------
2989 Seq Scan on mcrparted4 mcrparted
2990 Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
2991 (2 rows)
2993 explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5, mcrparted_def
2994 QUERY PLAN
2995 ---------------------------------------------
2996 Append
2997 -> Seq Scan on mcrparted3 mcrparted_1
2998 Filter: ((c > 20) AND (a = 20))
2999 -> Seq Scan on mcrparted4 mcrparted_2
3000 Filter: ((c > 20) AND (a = 20))
3001 -> Seq Scan on mcrparted5 mcrparted_3
3002 Filter: ((c > 20) AND (a = 20))
3003 -> Seq Scan on mcrparted_def mcrparted_4
3004 Filter: ((c > 20) AND (a = 20))
3005 (9 rows)
3007 -- check that partitioned table Appends cope with being referenced in
3008 -- subplans
3009 create table parted_minmax (a int, b varchar(16)) partition by range (a);
3010 create table parted_minmax1 partition of parted_minmax for values from (1) to (10);
3011 create index parted_minmax1i on parted_minmax1 (a, b);
3012 insert into parted_minmax values (1,'12345');
3013 explain (costs off) select min(a), max(a) from parted_minmax where b = '12345';
3014 QUERY PLAN
3015 ------------------------------------------------------------------------------------------------
3016 Result
3017 InitPlan 1
3018 -> Limit
3019 -> Index Only Scan using parted_minmax1i on parted_minmax1 parted_minmax
3020 Index Cond: ((a IS NOT NULL) AND (b = '12345'::text))
3021 InitPlan 2
3022 -> Limit
3023 -> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax_1
3024 Index Cond: ((a IS NOT NULL) AND (b = '12345'::text))
3025 (9 rows)
3027 select min(a), max(a) from parted_minmax where b = '12345';
3028 min | max
3029 -----+-----
3030 1 | 1
3031 (1 row)
3033 drop table parted_minmax;
3034 -- Test code that uses Append nodes in place of MergeAppend when the
3035 -- partition ordering matches the desired ordering.
3036 create index mcrparted_a_abs_c_idx on mcrparted (a, abs(b), c);
3037 -- MergeAppend must be used when a default partition exists
3038 explain (costs off) select * from mcrparted order by a, abs(b), c;
3039 QUERY PLAN
3040 -------------------------------------------------------------------------------
3041 Merge Append
3042 Sort Key: mcrparted.a, (abs(mcrparted.b)), mcrparted.c
3043 -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
3044 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
3045 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
3046 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
3047 -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
3048 -> Index Scan using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6
3049 -> Index Scan using mcrparted_def_a_abs_c_idx on mcrparted_def mcrparted_7
3050 (9 rows)
3052 drop table mcrparted_def;
3053 -- Append is used for a RANGE partitioned table with no default
3054 -- and no subpartitions
3055 explain (costs off) select * from mcrparted order by a, abs(b), c;
3056 QUERY PLAN
3057 -------------------------------------------------------------------------
3058 Append
3059 -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
3060 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
3061 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
3062 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
3063 -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
3064 -> Index Scan using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6
3065 (7 rows)
3067 -- Append is used with subpaths in reverse order with backwards index scans
3068 explain (costs off) select * from mcrparted order by a desc, abs(b) desc, c desc;
3069 QUERY PLAN
3070 ----------------------------------------------------------------------------------
3071 Append
3072 -> Index Scan Backward using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6
3073 -> Index Scan Backward using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
3074 -> Index Scan Backward using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
3075 -> Index Scan Backward using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
3076 -> Index Scan Backward using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
3077 -> Index Scan Backward using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
3078 (7 rows)
3080 -- check that Append plan is used containing a MergeAppend for sub-partitions
3081 -- that are unordered.
3082 drop table mcrparted5;
3083 create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue) partition by list (a);
3084 create table mcrparted5a partition of mcrparted5 for values in(20);
3085 create table mcrparted5_def partition of mcrparted5 default;
3086 explain (costs off) select * from mcrparted order by a, abs(b), c;
3087 QUERY PLAN
3088 ---------------------------------------------------------------------------------------
3089 Append
3090 -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
3091 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
3092 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
3093 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
3094 -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
3095 -> Merge Append
3096 Sort Key: mcrparted_7.a, (abs(mcrparted_7.b)), mcrparted_7.c
3097 -> Index Scan using mcrparted5a_a_abs_c_idx on mcrparted5a mcrparted_7
3098 -> Index Scan using mcrparted5_def_a_abs_c_idx on mcrparted5_def mcrparted_8
3099 (10 rows)
3101 drop table mcrparted5_def;
3102 -- check that an Append plan is used and the sub-partitions are flattened
3103 -- into the main Append when the sub-partition is unordered but contains
3104 -- just a single sub-partition.
3105 explain (costs off) select a, abs(b) from mcrparted order by a, abs(b), c;
3106 QUERY PLAN
3107 ---------------------------------------------------------------------------
3108 Append
3109 -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
3110 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
3111 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
3112 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
3113 -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
3114 -> Index Scan using mcrparted5a_a_abs_c_idx on mcrparted5a mcrparted_6
3115 (7 rows)
3117 -- check that Append is used when the sub-partitioned tables are pruned
3118 -- during planning.
3119 explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c;
3120 QUERY PLAN
3121 -------------------------------------------------------------------------
3122 Append
3123 -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
3124 Index Cond: (a < 20)
3125 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
3126 Index Cond: (a < 20)
3127 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
3128 Index Cond: (a < 20)
3129 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
3130 Index Cond: (a < 20)
3131 (9 rows)
3133 set enable_bitmapscan to off;
3134 set enable_sort to off;
3135 create table mclparted (a int) partition by list(a);
3136 create table mclparted1 partition of mclparted for values in(1);
3137 create table mclparted2 partition of mclparted for values in(2);
3138 create index on mclparted (a);
3139 -- Ensure an Append is used for a list partition with an order by.
3140 explain (costs off) select * from mclparted order by a;
3141 QUERY PLAN
3142 ------------------------------------------------------------------------
3143 Append
3144 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
3145 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
3146 (3 rows)
3148 -- Ensure a MergeAppend is used when a partition exists with interleaved
3149 -- datums in the partition bound.
3150 create table mclparted3_5 partition of mclparted for values in(3,5);
3151 create table mclparted4 partition of mclparted for values in(4);
3152 explain (costs off) select * from mclparted order by a;
3153 QUERY PLAN
3154 ----------------------------------------------------------------------------
3155 Merge Append
3156 Sort Key: mclparted.a
3157 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
3158 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
3159 -> Index Only Scan using mclparted3_5_a_idx on mclparted3_5 mclparted_3
3160 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4
3161 (6 rows)
3163 explain (costs off) select * from mclparted where a in(3,4,5) order by a;
3164 QUERY PLAN
3165 ----------------------------------------------------------------------------
3166 Merge Append
3167 Sort Key: mclparted.a
3168 -> Index Only Scan using mclparted3_5_a_idx on mclparted3_5 mclparted_1
3169 Index Cond: (a = ANY ('{3,4,5}'::integer[]))
3170 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_2
3171 Index Cond: (a = ANY ('{3,4,5}'::integer[]))
3172 (6 rows)
3174 -- Introduce a NULL and DEFAULT partition so we can test more complex cases
3175 create table mclparted_null partition of mclparted for values in(null);
3176 create table mclparted_def partition of mclparted default;
3177 -- Append can be used providing we don't scan the interleaved partition
3178 explain (costs off) select * from mclparted where a in(1,2,4) order by a;
3179 QUERY PLAN
3180 ------------------------------------------------------------------------
3181 Append
3182 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
3183 Index Cond: (a = ANY ('{1,2,4}'::integer[]))
3184 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
3185 Index Cond: (a = ANY ('{1,2,4}'::integer[]))
3186 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
3187 Index Cond: (a = ANY ('{1,2,4}'::integer[]))
3188 (7 rows)
3190 explain (costs off) select * from mclparted where a in(1,2,4) or a is null order by a;
3191 QUERY PLAN
3192 --------------------------------------------------------------------------------
3193 Append
3194 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
3195 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
3196 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
3197 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
3198 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
3199 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
3200 -> Index Only Scan using mclparted_null_a_idx on mclparted_null mclparted_4
3201 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
3202 (9 rows)
3204 -- Test a more complex case where the NULL partition allows some other value
3205 drop table mclparted_null;
3206 create table mclparted_0_null partition of mclparted for values in(0,null);
3207 -- Ensure MergeAppend is used since 0 and NULLs are in the same partition.
3208 explain (costs off) select * from mclparted where a in(1,2,4) or a is null order by a;
3209 QUERY PLAN
3210 ------------------------------------------------------------------------------------
3211 Merge Append
3212 Sort Key: mclparted.a
3213 -> Index Only Scan using mclparted_0_null_a_idx on mclparted_0_null mclparted_1
3214 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
3215 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_2
3216 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
3217 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_3
3218 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
3219 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4
3220 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
3221 (10 rows)
3223 explain (costs off) select * from mclparted where a in(0,1,2,4) order by a;
3224 QUERY PLAN
3225 ------------------------------------------------------------------------------------
3226 Merge Append
3227 Sort Key: mclparted.a
3228 -> Index Only Scan using mclparted_0_null_a_idx on mclparted_0_null mclparted_1
3229 Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
3230 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_2
3231 Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
3232 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_3
3233 Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
3234 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4
3235 Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
3236 (10 rows)
3238 -- Ensure Append is used when the null partition is pruned
3239 explain (costs off) select * from mclparted where a in(1,2,4) order by a;
3240 QUERY PLAN
3241 ------------------------------------------------------------------------
3242 Append
3243 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
3244 Index Cond: (a = ANY ('{1,2,4}'::integer[]))
3245 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
3246 Index Cond: (a = ANY ('{1,2,4}'::integer[]))
3247 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
3248 Index Cond: (a = ANY ('{1,2,4}'::integer[]))
3249 (7 rows)
3251 -- Ensure MergeAppend is used when the default partition is not pruned
3252 explain (costs off) select * from mclparted where a in(1,2,4,100) order by a;
3253 QUERY PLAN
3254 ------------------------------------------------------------------------------
3255 Merge Append
3256 Sort Key: mclparted.a
3257 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
3258 Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
3259 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
3260 Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
3261 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
3262 Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
3263 -> Index Only Scan using mclparted_def_a_idx on mclparted_def mclparted_4
3264 Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
3265 (10 rows)
3267 drop table mclparted;
3268 reset enable_sort;
3269 reset enable_bitmapscan;
3270 -- Ensure subplans which don't have a path with the correct pathkeys get
3271 -- sorted correctly.
3272 drop index mcrparted_a_abs_c_idx;
3273 create index on mcrparted1 (a, abs(b), c);
3274 create index on mcrparted2 (a, abs(b), c);
3275 create index on mcrparted3 (a, abs(b), c);
3276 create index on mcrparted4 (a, abs(b), c);
3277 explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c limit 1;
3278 QUERY PLAN
3279 -------------------------------------------------------------------------------
3280 Limit
3281 -> Append
3282 -> Sort
3283 Sort Key: mcrparted_1.a, (abs(mcrparted_1.b)), mcrparted_1.c
3284 -> Seq Scan on mcrparted0 mcrparted_1
3285 Filter: (a < 20)
3286 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
3287 Index Cond: (a < 20)
3288 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
3289 Index Cond: (a < 20)
3290 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
3291 Index Cond: (a < 20)
3292 (12 rows)
3294 set enable_bitmapscan = 0;
3295 -- Ensure Append node can be used when the partition is ordered by some
3296 -- pathkeys which were deemed redundant.
3297 explain (costs off) select * from mcrparted where a = 10 order by a, abs(b), c;
3298 QUERY PLAN
3299 -------------------------------------------------------------------------
3300 Append
3301 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_1
3302 Index Cond: (a = 10)
3303 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_2
3304 Index Cond: (a = 10)
3305 (5 rows)
3307 reset enable_bitmapscan;
3308 drop table mcrparted;
3309 -- Ensure LIST partitions allow an Append to be used instead of a MergeAppend
3310 create table bool_lp (b bool) partition by list(b);
3311 create table bool_lp_true partition of bool_lp for values in(true);
3312 create table bool_lp_false partition of bool_lp for values in(false);
3313 create index on bool_lp (b);
3314 explain (costs off) select * from bool_lp order by b;
3315 QUERY PLAN
3316 ----------------------------------------------------------------------------
3317 Append
3318 -> Index Only Scan using bool_lp_false_b_idx on bool_lp_false bool_lp_1
3319 -> Index Only Scan using bool_lp_true_b_idx on bool_lp_true bool_lp_2
3320 (3 rows)
3322 drop table bool_lp;
3323 -- Ensure const bool quals can be properly detected as redundant
3324 create table bool_rp (b bool, a int) partition by range(b,a);
3325 create table bool_rp_false_1k partition of bool_rp for values from (false,0) to (false,1000);
3326 create table bool_rp_true_1k partition of bool_rp for values from (true,0) to (true,1000);
3327 create table bool_rp_false_2k partition of bool_rp for values from (false,1000) to (false,2000);
3328 create table bool_rp_true_2k partition of bool_rp for values from (true,1000) to (true,2000);
3329 create index on bool_rp (b,a);
3330 explain (costs off) select * from bool_rp where b = true order by b,a;
3331 QUERY PLAN
3332 ----------------------------------------------------------------------------------
3333 Append
3334 -> Index Only Scan using bool_rp_true_1k_b_a_idx on bool_rp_true_1k bool_rp_1
3335 Index Cond: (b = true)
3336 -> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k bool_rp_2
3337 Index Cond: (b = true)
3338 (5 rows)
3340 explain (costs off) select * from bool_rp where b = false order by b,a;
3341 QUERY PLAN
3342 ------------------------------------------------------------------------------------
3343 Append
3344 -> Index Only Scan using bool_rp_false_1k_b_a_idx on bool_rp_false_1k bool_rp_1
3345 Index Cond: (b = false)
3346 -> Index Only Scan using bool_rp_false_2k_b_a_idx on bool_rp_false_2k bool_rp_2
3347 Index Cond: (b = false)
3348 (5 rows)
3350 explain (costs off) select * from bool_rp where b = true order by a;
3351 QUERY PLAN
3352 ----------------------------------------------------------------------------------
3353 Append
3354 -> Index Only Scan using bool_rp_true_1k_b_a_idx on bool_rp_true_1k bool_rp_1
3355 Index Cond: (b = true)
3356 -> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k bool_rp_2
3357 Index Cond: (b = true)
3358 (5 rows)
3360 explain (costs off) select * from bool_rp where b = false order by a;
3361 QUERY PLAN
3362 ------------------------------------------------------------------------------------
3363 Append
3364 -> Index Only Scan using bool_rp_false_1k_b_a_idx on bool_rp_false_1k bool_rp_1
3365 Index Cond: (b = false)
3366 -> Index Only Scan using bool_rp_false_2k_b_a_idx on bool_rp_false_2k bool_rp_2
3367 Index Cond: (b = false)
3368 (5 rows)
3370 drop table bool_rp;
3371 -- Ensure an Append scan is chosen when the partition order is a subset of
3372 -- the required order.
3373 create table range_parted (a int, b int, c int) partition by range(a, b);
3374 create table range_parted1 partition of range_parted for values from (0,0) to (10,10);
3375 create table range_parted2 partition of range_parted for values from (10,10) to (20,20);
3376 create index on range_parted (a,b,c);
3377 explain (costs off) select * from range_parted order by a,b,c;
3378 QUERY PLAN
3379 -------------------------------------------------------------------------------------
3380 Append
3381 -> Index Only Scan using range_parted1_a_b_c_idx on range_parted1 range_parted_1
3382 -> Index Only Scan using range_parted2_a_b_c_idx on range_parted2 range_parted_2
3383 (3 rows)
3385 explain (costs off) select * from range_parted order by a desc,b desc,c desc;
3386 QUERY PLAN
3387 ----------------------------------------------------------------------------------------------
3388 Append
3389 -> Index Only Scan Backward using range_parted2_a_b_c_idx on range_parted2 range_parted_2
3390 -> Index Only Scan Backward using range_parted1_a_b_c_idx on range_parted1 range_parted_1
3391 (3 rows)
3393 drop table range_parted;
3394 -- Check that we allow access to a child table's statistics when the user
3395 -- has permissions only for the parent table.
3396 create table permtest_parent (a int, b text, c text) partition by list (a);
3397 create table permtest_child (b text, c text, a int) partition by list (b);
3398 create table permtest_grandchild (c text, b text, a int);
3399 alter table permtest_child attach partition permtest_grandchild for values in ('a');
3400 alter table permtest_parent attach partition permtest_child for values in (1);
3401 create index on permtest_parent (left(c, 3));
3402 insert into permtest_parent
3403 select 1, 'a', left(fipshash(i::text), 5) from generate_series(0, 100) i;
3404 analyze permtest_parent;
3405 create role regress_no_child_access;
3406 revoke all on permtest_grandchild from regress_no_child_access;
3407 grant select on permtest_parent to regress_no_child_access;
3408 set session authorization regress_no_child_access;
3409 -- without stats access, these queries would produce hash join plans:
3410 explain (costs off)
3411 select * from permtest_parent p1 inner join permtest_parent p2
3412 on p1.a = p2.a and p1.c ~ 'a1$';
3413 QUERY PLAN
3414 ------------------------------------------
3415 Nested Loop
3416 Join Filter: (p1.a = p2.a)
3417 -> Seq Scan on permtest_grandchild p1
3418 Filter: (c ~ 'a1$'::text)
3419 -> Seq Scan on permtest_grandchild p2
3420 (5 rows)
3422 explain (costs off)
3423 select * from permtest_parent p1 inner join permtest_parent p2
3424 on p1.a = p2.a and left(p1.c, 3) ~ 'a1$';
3425 QUERY PLAN
3426 ----------------------------------------------
3427 Nested Loop
3428 Join Filter: (p1.a = p2.a)
3429 -> Seq Scan on permtest_grandchild p1
3430 Filter: ("left"(c, 3) ~ 'a1$'::text)
3431 -> Seq Scan on permtest_grandchild p2
3432 (5 rows)
3434 reset session authorization;
3435 revoke all on permtest_parent from regress_no_child_access;
3436 grant select(a,c) on permtest_parent to regress_no_child_access;
3437 set session authorization regress_no_child_access;
3438 explain (costs off)
3439 select p2.a, p1.c from permtest_parent p1 inner join permtest_parent p2
3440 on p1.a = p2.a and p1.c ~ 'a1$';
3441 QUERY PLAN
3442 ------------------------------------------
3443 Nested Loop
3444 Join Filter: (p1.a = p2.a)
3445 -> Seq Scan on permtest_grandchild p1
3446 Filter: (c ~ 'a1$'::text)
3447 -> Seq Scan on permtest_grandchild p2
3448 (5 rows)
3450 -- we will not have access to the expression index's stats here:
3451 explain (costs off)
3452 select p2.a, p1.c from permtest_parent p1 inner join permtest_parent p2
3453 on p1.a = p2.a and left(p1.c, 3) ~ 'a1$';
3454 QUERY PLAN
3455 ----------------------------------------------------
3456 Hash Join
3457 Hash Cond: (p2.a = p1.a)
3458 -> Seq Scan on permtest_grandchild p2
3459 -> Hash
3460 -> Seq Scan on permtest_grandchild p1
3461 Filter: ("left"(c, 3) ~ 'a1$'::text)
3462 (6 rows)
3464 reset session authorization;
3465 revoke all on permtest_parent from regress_no_child_access;
3466 drop role regress_no_child_access;
3467 drop table permtest_parent;
3468 -- Verify that constraint errors across partition root / child are
3469 -- handled correctly (Bug #16293)
3470 CREATE TABLE errtst_parent (
3471 partid int not null,
3472 shdata int not null,
3473 data int NOT NULL DEFAULT 0,
3474 CONSTRAINT shdata_small CHECK(shdata < 3)
3475 ) PARTITION BY RANGE (partid);
3476 -- fast defaults lead to attribute mapping being used in one
3477 -- direction, but not the other
3478 CREATE TABLE errtst_child_fastdef (
3479 partid int not null,
3480 shdata int not null,
3481 CONSTRAINT shdata_small CHECK(shdata < 3)
3482 );
3483 -- no remapping in either direction necessary
3484 CREATE TABLE errtst_child_plaindef (
3485 partid int not null,
3486 shdata int not null,
3487 data int NOT NULL DEFAULT 0,
3488 CONSTRAINT shdata_small CHECK(shdata < 3),
3489 CHECK(data < 10)
3490 );
3491 -- remapping in both direction
3492 CREATE TABLE errtst_child_reorder (
3493 data int NOT NULL DEFAULT 0,
3494 shdata int not null,
3495 partid int not null,
3496 CONSTRAINT shdata_small CHECK(shdata < 3),
3497 CHECK(data < 10)
3498 );
3499 ALTER TABLE errtst_child_fastdef ADD COLUMN data int NOT NULL DEFAULT 0;
3500 ALTER TABLE errtst_child_fastdef ADD CONSTRAINT errtest_child_fastdef_data_check CHECK (data < 10);
3501 ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_fastdef FOR VALUES FROM (0) TO (10);
3502 ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_plaindef FOR VALUES FROM (10) TO (20);
3503 ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_reorder FOR VALUES FROM (20) TO (30);
3504 -- insert without child check constraint error
3505 INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '5');
3506 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '5');
3507 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '5');
3508 -- insert with child check constraint error
3509 INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '10');
3510 ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
3511 DETAIL: Failing row contains (0, 1, 10).
3512 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '10');
3513 ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
3514 DETAIL: Failing row contains (10, 1, 10).
3515 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '10');
3516 ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
3517 DETAIL: Failing row contains (20, 1, 10).
3518 -- insert with child not null constraint error
3519 INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', NULL);
3520 ERROR: null value in column "data" of relation "errtst_child_fastdef" violates not-null constraint
3521 DETAIL: Failing row contains (0, 1, null).
3522 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', NULL);
3523 ERROR: null value in column "data" of relation "errtst_child_plaindef" violates not-null constraint
3524 DETAIL: Failing row contains (10, 1, null).
3525 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', NULL);
3526 ERROR: null value in column "data" of relation "errtst_child_reorder" violates not-null constraint
3527 DETAIL: Failing row contains (20, 1, null).
3528 -- insert with shared check constraint error
3529 INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '5', '5');
3530 ERROR: new row for relation "errtst_child_fastdef" violates check constraint "shdata_small"
3531 DETAIL: Failing row contains (0, 5, 5).
3532 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '5', '5');
3533 ERROR: new row for relation "errtst_child_plaindef" violates check constraint "shdata_small"
3534 DETAIL: Failing row contains (10, 5, 5).
3535 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '5', '5');
3536 ERROR: new row for relation "errtst_child_reorder" violates check constraint "shdata_small"
3537 DETAIL: Failing row contains (20, 5, 5).
3538 -- within partition update without child check constraint violation
3539 BEGIN;
3540 UPDATE errtst_parent SET data = data + 1 WHERE partid = 0;
3541 UPDATE errtst_parent SET data = data + 1 WHERE partid = 10;
3542 UPDATE errtst_parent SET data = data + 1 WHERE partid = 20;
3543 ROLLBACK;
3544 -- within partition update with child check constraint violation
3545 UPDATE errtst_parent SET data = data + 10 WHERE partid = 0;
3546 ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
3547 DETAIL: Failing row contains (0, 1, 15).
3548 UPDATE errtst_parent SET data = data + 10 WHERE partid = 10;
3549 ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
3550 DETAIL: Failing row contains (10, 1, 15).
3551 UPDATE errtst_parent SET data = data + 10 WHERE partid = 20;
3552 ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
3553 DETAIL: Failing row contains (20, 1, 15).
3554 -- direct leaf partition update, without partition id violation
3555 BEGIN;
3556 UPDATE errtst_child_fastdef SET partid = 1 WHERE partid = 0;
3557 UPDATE errtst_child_plaindef SET partid = 11 WHERE partid = 10;
3558 UPDATE errtst_child_reorder SET partid = 21 WHERE partid = 20;
3559 ROLLBACK;
3560 -- direct leaf partition update, with partition id violation
3561 UPDATE errtst_child_fastdef SET partid = partid + 10 WHERE partid = 0;
3562 ERROR: new row for relation "errtst_child_fastdef" violates partition constraint
3563 DETAIL: Failing row contains (10, 1, 5).
3564 UPDATE errtst_child_plaindef SET partid = partid + 10 WHERE partid = 10;
3565 ERROR: new row for relation "errtst_child_plaindef" violates partition constraint
3566 DETAIL: Failing row contains (20, 1, 5).
3567 UPDATE errtst_child_reorder SET partid = partid + 10 WHERE partid = 20;
3568 ERROR: new row for relation "errtst_child_reorder" violates partition constraint
3569 DETAIL: Failing row contains (5, 1, 30).
3570 -- partition move, without child check constraint violation
3571 BEGIN;
3572 UPDATE errtst_parent SET partid = 10, data = data + 1 WHERE partid = 0;
3573 UPDATE errtst_parent SET partid = 20, data = data + 1 WHERE partid = 10;
3574 UPDATE errtst_parent SET partid = 0, data = data + 1 WHERE partid = 20;
3575 ROLLBACK;
3576 -- partition move, with child check constraint violation
3577 UPDATE errtst_parent SET partid = 10, data = data + 10 WHERE partid = 0;
3578 ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
3579 DETAIL: Failing row contains (10, 1, 15).
3580 UPDATE errtst_parent SET partid = 20, data = data + 10 WHERE partid = 10;
3581 ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
3582 DETAIL: Failing row contains (20, 1, 15).
3583 UPDATE errtst_parent SET partid = 0, data = data + 10 WHERE partid = 20;
3584 ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
3585 DETAIL: Failing row contains (0, 1, 15).
3586 -- partition move, without target partition
3587 UPDATE errtst_parent SET partid = 30, data = data + 10 WHERE partid = 20;
3588 ERROR: no partition of relation "errtst_parent" found for row
3589 DETAIL: Partition key of the failing row contains (partid) = (30).
3590 DROP TABLE errtst_parent;