| blob | dbb748a2d2f8d60d734d28fa220d65411a2e7ace |
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 -- Check inherited UPDATE with first child excluded
543 create table some_tab (f1 int, f2 int, f3 int, check (f1 < 10) no inherit);
544 create table some_tab_child () inherits(some_tab);
545 insert into some_tab_child select i, i+1, 0 from generate_series(1,1000) i;
546 create index on some_tab_child(f1, f2);
547 -- while at it, also check that statement-level triggers fire
548 create function some_tab_stmt_trig_func() returns trigger as
549 $$begin raise notice 'updating some_tab'; return NULL; end;$$
550 language plpgsql;
551 create trigger some_tab_stmt_trig
552 before update on some_tab execute function some_tab_stmt_trig_func();
553 explain (costs off)
554 update some_tab set f3 = 11 where f1 = 12 and f2 = 13;
555 QUERY PLAN
556 ------------------------------------------------------------------------------------
557 Update on some_tab
558 Update on some_tab_child some_tab_1
559 -> Result
560 -> Index Scan using some_tab_child_f1_f2_idx on some_tab_child some_tab_1
561 Index Cond: ((f1 = 12) AND (f2 = 13))
562 (5 rows)
564 update some_tab set f3 = 11 where f1 = 12 and f2 = 13;
565 NOTICE: updating some_tab
566 drop table some_tab cascade;
567 NOTICE: drop cascades to table some_tab_child
568 drop function some_tab_stmt_trig_func();
569 -- Check inherited UPDATE with all children excluded
570 create table some_tab (a int, b int);
571 create table some_tab_child () inherits (some_tab);
572 insert into some_tab_child values(1,2);
573 explain (verbose, costs off)
574 update some_tab set a = a + 1 where false;
575 QUERY PLAN
576 --------------------------------------------------------
577 Update on public.some_tab
578 -> Result
579 Output: (some_tab.a + 1), NULL::oid, NULL::tid
580 One-Time Filter: false
581 (4 rows)
583 update some_tab set a = a + 1 where false;
584 explain (verbose, costs off)
585 update some_tab set a = a + 1 where false returning b, a;
586 QUERY PLAN
587 --------------------------------------------------------
588 Update on public.some_tab
589 Output: some_tab.b, some_tab.a
590 -> Result
591 Output: (some_tab.a + 1), NULL::oid, NULL::tid
592 One-Time Filter: false
593 (5 rows)
595 update some_tab set a = a + 1 where false returning b, a;
596 b | a
597 ---+---
598 (0 rows)
600 table some_tab;
601 a | b
602 ---+---
603 1 | 2
604 (1 row)
606 drop table some_tab cascade;
607 NOTICE: drop cascades to table some_tab_child
608 -- Check UPDATE with inherited target and an inherited source table
609 create temp table foo(f1 int, f2 int);
610 create temp table foo2(f3 int) inherits (foo);
611 create temp table bar(f1 int, f2 int);
612 create temp table bar2(f3 int) inherits (bar);
613 insert into foo values(1,1);
614 insert into foo values(3,3);
615 insert into foo2 values(2,2,2);
616 insert into foo2 values(3,3,3);
617 insert into bar values(1,1);
618 insert into bar values(2,2);
619 insert into bar values(3,3);
620 insert into bar values(4,4);
621 insert into bar2 values(1,1,1);
622 insert into bar2 values(2,2,2);
623 insert into bar2 values(3,3,3);
624 insert into bar2 values(4,4,4);
625 update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
626 select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
627 relname | f1 | f2
628 ---------+----+-----
629 bar | 1 | 101
630 bar | 2 | 102
631 bar | 3 | 103
632 bar | 4 | 4
633 bar2 | 1 | 101
634 bar2 | 2 | 102
635 bar2 | 3 | 103
636 bar2 | 4 | 4
637 (8 rows)
639 -- Check UPDATE with inherited target and an appendrel subquery
640 update bar set f2 = f2 + 100
641 from
642 ( select f1 from foo union all select f1+3 from foo ) ss
643 where bar.f1 = ss.f1;
644 select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
645 relname | f1 | f2
646 ---------+----+-----
647 bar | 1 | 201
648 bar | 2 | 202
649 bar | 3 | 203
650 bar | 4 | 104
651 bar2 | 1 | 201
652 bar2 | 2 | 202
653 bar2 | 3 | 203
654 bar2 | 4 | 104
655 (8 rows)
657 -- Check UPDATE with *partitioned* inherited target and an appendrel subquery
658 create table some_tab (a int);
659 insert into some_tab values (0);
660 create table some_tab_child () inherits (some_tab);
661 insert into some_tab_child values (1);
662 create table parted_tab (a int, b char) partition by list (a);
663 create table parted_tab_part1 partition of parted_tab for values in (1);
664 create table parted_tab_part2 partition of parted_tab for values in (2);
665 create table parted_tab_part3 partition of parted_tab for values in (3);
666 insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a');
667 update parted_tab set b = 'b'
668 from
669 (select a from some_tab union all select a+1 from some_tab) ss (a)
670 where parted_tab.a = ss.a;
671 select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2;
672 relname | a | b
673 ------------------+---+---
674 parted_tab_part1 | 1 | b
675 parted_tab_part2 | 2 | b
676 parted_tab_part3 | 3 | a
677 (3 rows)
679 truncate parted_tab;
680 insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a');
681 update parted_tab set b = 'b'
682 from
683 (select 0 from parted_tab union all select 1 from parted_tab) ss (a)
684 where parted_tab.a = ss.a;
685 select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2;
686 relname | a | b
687 ------------------+---+---
688 parted_tab_part1 | 1 | b
689 parted_tab_part2 | 2 | a
690 parted_tab_part3 | 3 | a
691 (3 rows)
693 -- modifies partition key, but no rows will actually be updated
694 explain update parted_tab set a = 2 where false;
695 QUERY PLAN
696 --------------------------------------------------------
697 Update on parted_tab (cost=0.00..0.00 rows=0 width=0)
698 -> Result (cost=0.00..0.00 rows=0 width=10)
699 One-Time Filter: false
700 (3 rows)
702 drop table parted_tab;
703 -- Check UPDATE with multi-level partitioned inherited target
704 create table mlparted_tab (a int, b char, c text) partition by list (a);
705 create table mlparted_tab_part1 partition of mlparted_tab for values in (1);
706 create table mlparted_tab_part2 partition of mlparted_tab for values in (2) partition by list (b);
707 create table mlparted_tab_part3 partition of mlparted_tab for values in (3);
708 create table mlparted_tab_part2a partition of mlparted_tab_part2 for values in ('a');
709 create table mlparted_tab_part2b partition of mlparted_tab_part2 for values in ('b');
710 insert into mlparted_tab values (1, 'a'), (2, 'a'), (2, 'b'), (3, 'a');
711 update mlparted_tab mlp set c = 'xxx'
712 from
713 (select a from some_tab union all select a+1 from some_tab) ss (a)
714 where (mlp.a = ss.a and mlp.b = 'b') or mlp.a = 3;
715 select tableoid::regclass::text as relname, mlparted_tab.* from mlparted_tab order by 1,2;
716 relname | a | b | c
717 ---------------------+---+---+-----
718 mlparted_tab_part1 | 1 | a |
719 mlparted_tab_part2a | 2 | a |
720 mlparted_tab_part2b | 2 | b | xxx
721 mlparted_tab_part3 | 3 | a | xxx
722 (4 rows)
724 drop table mlparted_tab;
725 drop table some_tab cascade;
726 NOTICE: drop cascades to table some_tab_child
727 /* Test multiple inheritance of column defaults */
728 CREATE TABLE firstparent (tomorrow date default now()::date + 1);
729 CREATE TABLE secondparent (tomorrow date default now() :: date + 1);
730 CREATE TABLE jointchild () INHERITS (firstparent, secondparent); -- ok
731 NOTICE: merging multiple inherited definitions of column "tomorrow"
732 CREATE TABLE thirdparent (tomorrow date default now()::date - 1);
733 CREATE TABLE otherchild () INHERITS (firstparent, thirdparent); -- not ok
734 NOTICE: merging multiple inherited definitions of column "tomorrow"
735 ERROR: column "tomorrow" inherits conflicting default values
736 HINT: To resolve the conflict, specify a default explicitly.
737 CREATE TABLE otherchild (tomorrow date default now())
738 INHERITS (firstparent, thirdparent); -- ok, child resolves ambiguous default
739 NOTICE: merging multiple inherited definitions of column "tomorrow"
740 NOTICE: merging column "tomorrow" with inherited definition
741 DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild;
742 -- Test changing the type of inherited columns
743 insert into d values('test','one','two','three');
744 alter table a alter column aa type integer using bit_length(aa);
745 select * from d;
746 aa | bb | cc | dd
747 ----+-----+-----+-------
748 32 | one | two | three
749 (1 row)
751 -- The above verified that we can change the type of a multiply-inherited
752 -- column; but we should reject that if any definition was inherited from
753 -- an unrelated parent.
754 create temp table parent1(f1 int, f2 int);
755 create temp table parent2(f1 int, f3 bigint);
756 create temp table childtab(f4 int) inherits(parent1, parent2);
757 NOTICE: merging multiple inherited definitions of column "f1"
758 alter table parent1 alter column f1 type bigint; -- fail, conflict w/parent2
759 ERROR: cannot alter inherited column "f1" of relation "childtab"
760 alter table parent1 alter column f2 type bigint; -- ok
761 -- Test non-inheritable parent constraints
762 create table p1(ff1 int);
763 alter table p1 add constraint p1chk check (ff1 > 0) no inherit;
764 alter table p1 add constraint p2chk check (ff1 > 10);
765 -- connoinherit should be true for NO INHERIT constraint
766 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;
767 relname | conname | contype | conislocal | coninhcount | connoinherit
768 ---------+---------+---------+------------+-------------+--------------
769 p1 | p1chk | c | t | 0 | t
770 p1 | p2chk | c | t | 0 | f
771 (2 rows)
773 -- Test that child does not inherit NO INHERIT constraints
774 create table c1 () inherits (p1);
775 \d p1
776 Table "public.p1"
777 Column | Type | Collation | Nullable | Default
778 --------+---------+-----------+----------+---------
779 ff1 | integer | | |
780 Check constraints:
781 "p1chk" CHECK (ff1 > 0) NO INHERIT
782 "p2chk" CHECK (ff1 > 10)
783 Number of child tables: 1 (Use \d+ to list them.)
785 \d c1
786 Table "public.c1"
787 Column | Type | Collation | Nullable | Default
788 --------+---------+-----------+----------+---------
789 ff1 | integer | | |
790 Check constraints:
791 "p2chk" CHECK (ff1 > 10)
792 Inherits: p1
794 -- Test that child does not override inheritable constraints of the parent
795 create table c2 (constraint p2chk check (ff1 > 10) no inherit) inherits (p1); --fails
796 ERROR: constraint "p2chk" conflicts with inherited constraint on relation "c2"
797 drop table p1 cascade;
798 NOTICE: drop cascades to table c1
799 -- Tests for casting between the rowtypes of parent and child
800 -- tables. See the pgsql-hackers thread beginning Dec. 4/04
801 create table base (i integer);
802 create table derived () inherits (base);
803 create table more_derived (like derived, b int) inherits (derived);
804 NOTICE: merging column "i" with inherited definition
805 insert into derived (i) values (0);
806 select derived::base from derived;
807 derived
808 ---------
809 (0)
810 (1 row)
812 select NULL::derived::base;
813 base
814 ------
816 (1 row)
818 -- remove redundant conversions.
819 explain (verbose on, costs off) select row(i, b)::more_derived::derived::base from more_derived;
820 QUERY PLAN
821 -------------------------------------------
822 Seq Scan on public.more_derived
823 Output: (ROW(i, b)::more_derived)::base
824 (2 rows)
826 explain (verbose on, costs off) select (1, 2)::more_derived::derived::base;
827 QUERY PLAN
828 -----------------------
829 Result
830 Output: '(1)'::base
831 (2 rows)
833 drop table more_derived;
834 drop table derived;
835 drop table base;
836 create table p1(ff1 int);
837 create table p2(f1 text);
838 create function p2text(p2) returns text as 'select $1.f1' language sql;
839 create table c1(f3 int) inherits(p1,p2);
840 insert into c1 values(123456789, 'hi', 42);
841 select p2text(c1.*) from c1;
842 p2text
843 --------
844 hi
845 (1 row)
847 drop function p2text(p2);
848 drop table c1;
849 drop table p2;
850 drop table p1;
851 CREATE TABLE ac (aa TEXT);
852 alter table ac add constraint ac_check check (aa is not null);
853 CREATE TABLE bc (bb TEXT) INHERITS (ac);
854 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;
855 relname | conname | contype | conislocal | coninhcount | consrc
856 ---------+----------+---------+------------+-------------+------------------
857 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
858 bc | ac_check | c | f | 1 | (aa IS NOT NULL)
859 (2 rows)
861 insert into ac (aa) values (NULL);
862 ERROR: new row for relation "ac" violates check constraint "ac_check"
863 DETAIL: Failing row contains (null).
864 insert into bc (aa) values (NULL);
865 ERROR: new row for relation "bc" violates check constraint "ac_check"
866 DETAIL: Failing row contains (null, null).
867 alter table bc drop constraint ac_check; -- fail, disallowed
868 ERROR: cannot drop inherited constraint "ac_check" of relation "bc"
869 alter table ac drop constraint ac_check;
870 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;
871 relname | conname | contype | conislocal | coninhcount | consrc
872 ---------+---------+---------+------------+-------------+--------
873 (0 rows)
875 -- try the unnamed-constraint case
876 alter table ac add check (aa is not null);
877 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;
878 relname | conname | contype | conislocal | coninhcount | consrc
879 ---------+-------------+---------+------------+-------------+------------------
880 ac | ac_aa_check | c | t | 0 | (aa IS NOT NULL)
881 bc | ac_aa_check | c | f | 1 | (aa IS NOT NULL)
882 (2 rows)
884 insert into ac (aa) values (NULL);
885 ERROR: new row for relation "ac" violates check constraint "ac_aa_check"
886 DETAIL: Failing row contains (null).
887 insert into bc (aa) values (NULL);
888 ERROR: new row for relation "bc" violates check constraint "ac_aa_check"
889 DETAIL: Failing row contains (null, null).
890 alter table bc drop constraint ac_aa_check; -- fail, disallowed
891 ERROR: cannot drop inherited constraint "ac_aa_check" of relation "bc"
892 alter table ac drop constraint ac_aa_check;
893 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;
894 relname | conname | contype | conislocal | coninhcount | consrc
895 ---------+---------+---------+------------+-------------+--------
896 (0 rows)
898 alter table ac add constraint ac_check check (aa is not null);
899 alter table bc no inherit ac;
900 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;
901 relname | conname | contype | conislocal | coninhcount | consrc
902 ---------+----------+---------+------------+-------------+------------------
903 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
904 bc | ac_check | c | t | 0 | (aa IS NOT NULL)
905 (2 rows)
907 alter table bc drop constraint ac_check;
908 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;
909 relname | conname | contype | conislocal | coninhcount | consrc
910 ---------+----------+---------+------------+-------------+------------------
911 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
912 (1 row)
914 alter table ac drop constraint ac_check;
915 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;
916 relname | conname | contype | conislocal | coninhcount | consrc
917 ---------+---------+---------+------------+-------------+--------
918 (0 rows)
920 drop table bc;
921 drop table ac;
922 create table ac (a int constraint check_a check (a <> 0));
923 create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac);
924 NOTICE: merging column "a" with inherited definition
925 NOTICE: merging constraint "check_a" with inherited definition
926 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;
927 relname | conname | contype | conislocal | coninhcount | consrc
928 ---------+---------+---------+------------+-------------+----------
929 ac | check_a | c | t | 0 | (a <> 0)
930 bc | check_a | c | t | 1 | (a <> 0)
931 bc | check_b | c | t | 0 | (b <> 0)
932 (3 rows)
934 drop table bc;
935 drop table ac;
936 create table ac (a int constraint check_a check (a <> 0));
937 create table bc (b int constraint check_b check (b <> 0));
938 create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc);
939 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;
940 relname | conname | contype | conislocal | coninhcount | consrc
941 ---------+---------+---------+------------+-------------+----------
942 ac | check_a | c | t | 0 | (a <> 0)
943 bc | check_b | c | t | 0 | (b <> 0)
944 cc | check_a | c | f | 1 | (a <> 0)
945 cc | check_b | c | f | 1 | (b <> 0)
946 cc | check_c | c | t | 0 | (c <> 0)
947 (5 rows)
949 alter table cc no inherit bc;
950 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;
951 relname | conname | contype | conislocal | coninhcount | consrc
952 ---------+---------+---------+------------+-------------+----------
953 ac | check_a | c | t | 0 | (a <> 0)
954 bc | check_b | c | t | 0 | (b <> 0)
955 cc | check_a | c | f | 1 | (a <> 0)
956 cc | check_b | c | t | 0 | (b <> 0)
957 cc | check_c | c | t | 0 | (c <> 0)
958 (5 rows)
960 drop table cc;
961 drop table bc;
962 drop table ac;
963 create table p1(f1 int);
964 create table p2(f2 int);
965 create table c1(f3 int) inherits(p1,p2);
966 insert into c1 values(1,-1,2);
967 alter table p2 add constraint cc check (f2>0); -- fail
968 ERROR: check constraint "cc" of relation "c1" is violated by some row
969 alter table p2 add check (f2>0); -- check it without a name, too
970 ERROR: check constraint "p2_f2_check" of relation "c1" is violated by some row
971 delete from c1;
972 insert into c1 values(1,1,2);
973 alter table p2 add check (f2>0);
974 insert into c1 values(1,-1,2); -- fail
975 ERROR: new row for relation "c1" violates check constraint "p2_f2_check"
976 DETAIL: Failing row contains (1, -1, 2).
977 create table c2(f3 int) inherits(p1,p2);
978 \d c2
979 Table "public.c2"
980 Column | Type | Collation | Nullable | Default
981 --------+---------+-----------+----------+---------
982 f1 | integer | | |
983 f2 | integer | | |
984 f3 | integer | | |
985 Check constraints:
986 "p2_f2_check" CHECK (f2 > 0)
987 Inherits: p1,
988 p2
990 create table c3 (f4 int) inherits(c1,c2);
991 NOTICE: merging multiple inherited definitions of column "f1"
992 NOTICE: merging multiple inherited definitions of column "f2"
993 NOTICE: merging multiple inherited definitions of column "f3"
994 \d c3
995 Table "public.c3"
996 Column | Type | Collation | Nullable | Default
997 --------+---------+-----------+----------+---------
998 f1 | integer | | |
999 f2 | integer | | |
1000 f3 | integer | | |
1001 f4 | integer | | |
1002 Check constraints:
1003 "p2_f2_check" CHECK (f2 > 0)
1004 Inherits: c1,
1005 c2
1007 drop table p1 cascade;
1008 NOTICE: drop cascades to 3 other objects
1009 DETAIL: drop cascades to table c1
1010 drop cascades to table c2
1011 drop cascades to table c3
1012 drop table p2 cascade;
1013 create table pp1 (f1 int);
1014 create table cc1 (f2 text, f3 int) inherits (pp1);
1015 alter table pp1 add column a1 int check (a1 > 0);
1016 \d cc1
1017 Table "public.cc1"
1018 Column | Type | Collation | Nullable | Default
1019 --------+---------+-----------+----------+---------
1020 f1 | integer | | |
1021 f2 | text | | |
1022 f3 | integer | | |
1023 a1 | integer | | |
1024 Check constraints:
1025 "pp1_a1_check" CHECK (a1 > 0)
1026 Inherits: pp1
1028 create table cc2(f4 float) inherits(pp1,cc1);
1029 NOTICE: merging multiple inherited definitions of column "f1"
1030 NOTICE: merging multiple inherited definitions of column "a1"
1031 \d cc2
1032 Table "public.cc2"
1033 Column | Type | Collation | Nullable | Default
1034 --------+------------------+-----------+----------+---------
1035 f1 | integer | | |
1036 a1 | integer | | |
1037 f2 | text | | |
1038 f3 | integer | | |
1039 f4 | double precision | | |
1040 Check constraints:
1041 "pp1_a1_check" CHECK (a1 > 0)
1042 Inherits: pp1,
1043 cc1
1045 alter table pp1 add column a2 int check (a2 > 0);
1046 NOTICE: merging definition of column "a2" for child "cc2"
1047 NOTICE: merging constraint "pp1_a2_check" with inherited definition
1048 \d cc2
1049 Table "public.cc2"
1050 Column | Type | Collation | Nullable | Default
1051 --------+------------------+-----------+----------+---------
1052 f1 | integer | | |
1053 a1 | integer | | |
1054 f2 | text | | |
1055 f3 | integer | | |
1056 f4 | double precision | | |
1057 a2 | integer | | |
1058 Check constraints:
1059 "pp1_a1_check" CHECK (a1 > 0)
1060 "pp1_a2_check" CHECK (a2 > 0)
1061 Inherits: pp1,
1062 cc1
1064 drop table pp1 cascade;
1065 NOTICE: drop cascades to 2 other objects
1066 DETAIL: drop cascades to table cc1
1067 drop cascades to table cc2
1068 -- Test for renaming in simple multiple inheritance
1069 CREATE TABLE inht1 (a int, b int);
1070 CREATE TABLE inhs1 (b int, c int);
1071 CREATE TABLE inhts (d int) INHERITS (inht1, inhs1);
1072 NOTICE: merging multiple inherited definitions of column "b"
1073 ALTER TABLE inht1 RENAME a TO aa;
1074 ALTER TABLE inht1 RENAME b TO bb; -- to be failed
1075 ERROR: cannot rename inherited column "b"
1076 ALTER TABLE inhts RENAME aa TO aaa; -- to be failed
1077 ERROR: cannot rename inherited column "aa"
1078 ALTER TABLE inhts RENAME d TO dd;
1079 \d+ inhts
1080 Table "public.inhts"
1081 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1082 --------+---------+-----------+----------+---------+---------+--------------+-------------
1083 aa | integer | | | | plain | |
1084 b | integer | | | | plain | |
1085 c | integer | | | | plain | |
1086 dd | integer | | | | plain | |
1087 Inherits: inht1,
1088 inhs1
1090 DROP TABLE inhts;
1091 -- Test for adding a column to a parent table with complex inheritance
1092 CREATE TABLE inhta ();
1093 CREATE TABLE inhtb () INHERITS (inhta);
1094 CREATE TABLE inhtc () INHERITS (inhtb);
1095 CREATE TABLE inhtd () INHERITS (inhta, inhtb, inhtc);
1096 ALTER TABLE inhta ADD COLUMN i int;
1097 NOTICE: merging definition of column "i" for child "inhtd"
1098 NOTICE: merging definition of column "i" for child "inhtd"
1099 \d+ inhta
1100 Table "public.inhta"
1101 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1102 --------+---------+-----------+----------+---------+---------+--------------+-------------
1103 i | integer | | | | plain | |
1104 Child tables: inhtb,
1105 inhtd
1107 DROP TABLE inhta, inhtb, inhtc, inhtd;
1108 -- Test for renaming in diamond inheritance
1109 CREATE TABLE inht2 (x int) INHERITS (inht1);
1110 CREATE TABLE inht3 (y int) INHERITS (inht1);
1111 CREATE TABLE inht4 (z int) INHERITS (inht2, inht3);
1112 NOTICE: merging multiple inherited definitions of column "aa"
1113 NOTICE: merging multiple inherited definitions of column "b"
1114 ALTER TABLE inht1 RENAME aa TO aaa;
1115 \d+ inht4
1116 Table "public.inht4"
1117 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1118 --------+---------+-----------+----------+---------+---------+--------------+-------------
1119 aaa | integer | | | | plain | |
1120 b | integer | | | | plain | |
1121 x | integer | | | | plain | |
1122 y | integer | | | | plain | |
1123 z | integer | | | | plain | |
1124 Inherits: inht2,
1125 inht3
1127 CREATE TABLE inhts (d int) INHERITS (inht2, inhs1);
1128 NOTICE: merging multiple inherited definitions of column "b"
1129 ALTER TABLE inht1 RENAME aaa TO aaaa;
1130 ALTER TABLE inht1 RENAME b TO bb; -- to be failed
1131 ERROR: cannot rename inherited column "b"
1132 \d+ inhts
1133 Table "public.inhts"
1134 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1135 --------+---------+-----------+----------+---------+---------+--------------+-------------
1136 aaaa | integer | | | | plain | |
1137 b | integer | | | | plain | |
1138 x | integer | | | | plain | |
1139 c | integer | | | | plain | |
1140 d | integer | | | | plain | |
1141 Inherits: inht2,
1142 inhs1
1144 WITH RECURSIVE r AS (
1145 SELECT 'inht1'::regclass AS inhrelid
1146 UNION ALL
1147 SELECT c.inhrelid FROM pg_inherits c, r WHERE r.inhrelid = c.inhparent
1148 )
1149 SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected
1150 FROM (SELECT inhrelid, count(*) AS expected FROM pg_inherits
1151 WHERE inhparent IN (SELECT inhrelid FROM r) GROUP BY inhrelid) e
1152 JOIN pg_attribute a ON e.inhrelid = a.attrelid WHERE NOT attislocal
1153 ORDER BY a.attrelid::regclass::name, a.attnum;
1154 attrelid | attname | attinhcount | expected
1155 ----------+---------+-------------+----------
1156 inht2 | aaaa | 1 | 1
1157 inht2 | b | 1 | 1
1158 inht3 | aaaa | 1 | 1
1159 inht3 | b | 1 | 1
1160 inht4 | aaaa | 2 | 2
1161 inht4 | b | 2 | 2
1162 inht4 | x | 1 | 2
1163 inht4 | y | 1 | 2
1164 inhts | aaaa | 1 | 1
1165 inhts | b | 2 | 1
1166 inhts | x | 1 | 1
1167 inhts | c | 1 | 1
1168 (12 rows)
1170 DROP TABLE inht1, inhs1 CASCADE;
1171 NOTICE: drop cascades to 4 other objects
1172 DETAIL: drop cascades to table inht2
1173 drop cascades to table inhts
1174 drop cascades to table inht3
1175 drop cascades to table inht4
1176 -- Test non-inheritable indices [UNIQUE, EXCLUDE] constraints
1177 CREATE TABLE test_constraints (id int, val1 varchar, val2 int, UNIQUE(val1, val2));
1178 CREATE TABLE test_constraints_inh () INHERITS (test_constraints);
1179 \d+ test_constraints
1180 Table "public.test_constraints"
1181 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1182 --------+-------------------+-----------+----------+---------+----------+--------------+-------------
1183 id | integer | | | | plain | |
1184 val1 | character varying | | | | extended | |
1185 val2 | integer | | | | plain | |
1186 Indexes:
1187 "test_constraints_val1_val2_key" UNIQUE CONSTRAINT, btree (val1, val2)
1188 Child tables: test_constraints_inh
1190 ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key;
1191 \d+ test_constraints
1192 Table "public.test_constraints"
1193 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1194 --------+-------------------+-----------+----------+---------+----------+--------------+-------------
1195 id | integer | | | | plain | |
1196 val1 | character varying | | | | extended | |
1197 val2 | integer | | | | plain | |
1198 Child tables: test_constraints_inh
1200 \d+ test_constraints_inh
1201 Table "public.test_constraints_inh"
1202 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1203 --------+-------------------+-----------+----------+---------+----------+--------------+-------------
1204 id | integer | | | | plain | |
1205 val1 | character varying | | | | extended | |
1206 val2 | integer | | | | plain | |
1207 Inherits: test_constraints
1209 DROP TABLE test_constraints_inh;
1210 DROP TABLE test_constraints;
1211 CREATE TABLE test_ex_constraints (
1212 c circle,
1213 EXCLUDE USING gist (c WITH &&)
1214 );
1215 CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints);
1216 \d+ test_ex_constraints
1217 Table "public.test_ex_constraints"
1218 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1219 --------+--------+-----------+----------+---------+---------+--------------+-------------
1220 c | circle | | | | plain | |
1221 Indexes:
1222 "test_ex_constraints_c_excl" EXCLUDE USING gist (c WITH &&)
1223 Child tables: test_ex_constraints_inh
1225 ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl;
1226 \d+ test_ex_constraints
1227 Table "public.test_ex_constraints"
1228 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1229 --------+--------+-----------+----------+---------+---------+--------------+-------------
1230 c | circle | | | | plain | |
1231 Child tables: test_ex_constraints_inh
1233 \d+ test_ex_constraints_inh
1234 Table "public.test_ex_constraints_inh"
1235 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1236 --------+--------+-----------+----------+---------+---------+--------------+-------------
1237 c | circle | | | | plain | |
1238 Inherits: test_ex_constraints
1240 DROP TABLE test_ex_constraints_inh;
1241 DROP TABLE test_ex_constraints;
1242 -- Test non-inheritable foreign key constraints
1243 CREATE TABLE test_primary_constraints(id int PRIMARY KEY);
1244 CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id));
1245 CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints);
1246 \d+ test_primary_constraints
1247 Table "public.test_primary_constraints"
1248 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1249 --------+---------+-----------+----------+---------+---------+--------------+-------------
1250 id | integer | | not null | | plain | |
1251 Indexes:
1252 "test_primary_constraints_pkey" PRIMARY KEY, btree (id)
1253 Referenced by:
1254 TABLE "test_foreign_constraints" CONSTRAINT "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
1256 \d+ test_foreign_constraints
1257 Table "public.test_foreign_constraints"
1258 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1259 --------+---------+-----------+----------+---------+---------+--------------+-------------
1260 id1 | integer | | | | plain | |
1261 Foreign-key constraints:
1262 "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
1263 Child tables: test_foreign_constraints_inh
1265 ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey;
1266 \d+ test_foreign_constraints
1267 Table "public.test_foreign_constraints"
1268 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1269 --------+---------+-----------+----------+---------+---------+--------------+-------------
1270 id1 | integer | | | | plain | |
1271 Child tables: test_foreign_constraints_inh
1273 \d+ test_foreign_constraints_inh
1274 Table "public.test_foreign_constraints_inh"
1275 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1276 --------+---------+-----------+----------+---------+---------+--------------+-------------
1277 id1 | integer | | | | plain | |
1278 Inherits: test_foreign_constraints
1280 DROP TABLE test_foreign_constraints_inh;
1281 DROP TABLE test_foreign_constraints;
1282 DROP TABLE test_primary_constraints;
1283 -- Test foreign key behavior
1284 create table inh_fk_1 (a int primary key);
1285 insert into inh_fk_1 values (1), (2), (3);
1286 create table inh_fk_2 (x int primary key, y int references inh_fk_1 on delete cascade);
1287 insert into inh_fk_2 values (11, 1), (22, 2), (33, 3);
1288 create table inh_fk_2_child () inherits (inh_fk_2);
1289 insert into inh_fk_2_child values (111, 1), (222, 2);
1290 delete from inh_fk_1 where a = 1;
1291 select * from inh_fk_1 order by 1;
1292 a
1293 ---
1294 2
1295 3
1296 (2 rows)
1298 select * from inh_fk_2 order by 1, 2;
1299 x | y
1300 -----+---
1301 22 | 2
1302 33 | 3
1303 111 | 1
1304 222 | 2
1305 (4 rows)
1307 drop table inh_fk_1, inh_fk_2, inh_fk_2_child;
1308 -- Test that parent and child CHECK constraints can be created in either order
1309 create table p1(f1 int);
1310 create table p1_c1() inherits(p1);
1311 alter table p1 add constraint inh_check_constraint1 check (f1 > 0);
1312 alter table p1_c1 add constraint inh_check_constraint1 check (f1 > 0);
1313 NOTICE: merging constraint "inh_check_constraint1" with inherited definition
1314 alter table p1_c1 add constraint inh_check_constraint2 check (f1 < 10);
1315 alter table p1 add constraint inh_check_constraint2 check (f1 < 10);
1316 NOTICE: merging constraint "inh_check_constraint2" with inherited definition
1317 select conrelid::regclass::text as relname, conname, conislocal, coninhcount
1318 from pg_constraint where conname like 'inh\_check\_constraint%'
1319 order by 1, 2;
1320 relname | conname | conislocal | coninhcount
1321 ---------+-----------------------+------------+-------------
1322 p1 | inh_check_constraint1 | t | 0
1323 p1 | inh_check_constraint2 | t | 0
1324 p1_c1 | inh_check_constraint1 | t | 1
1325 p1_c1 | inh_check_constraint2 | t | 1
1326 (4 rows)
1328 drop table p1 cascade;
1329 NOTICE: drop cascades to table p1_c1
1330 --
1331 -- Test DROP behavior of multiply-defined CHECK constraints
1332 --
1333 create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1334 create table p1_c1 (f1 int constraint f1_pos CHECK (f1 > 0)) inherits (p1);
1335 NOTICE: merging column "f1" with inherited definition
1336 NOTICE: merging constraint "f1_pos" with inherited definition
1337 alter table p1_c1 drop constraint f1_pos;
1338 ERROR: cannot drop inherited constraint "f1_pos" of relation "p1_c1"
1339 alter table p1 drop constraint f1_pos;
1340 \d p1_c1
1341 Table "public.p1_c1"
1342 Column | Type | Collation | Nullable | Default
1343 --------+---------+-----------+----------+---------
1344 f1 | integer | | |
1345 Check constraints:
1346 "f1_pos" CHECK (f1 > 0)
1347 Inherits: p1
1349 drop table p1 cascade;
1350 NOTICE: drop cascades to table p1_c1
1351 create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1352 create table p2(f1 int constraint f1_pos CHECK (f1 > 0));
1353 create table p1p2_c1 (f1 int) inherits (p1, p2);
1354 NOTICE: merging multiple inherited definitions of column "f1"
1355 NOTICE: merging column "f1" with inherited definition
1356 create table p1p2_c2 (f1 int constraint f1_pos CHECK (f1 > 0)) inherits (p1, p2);
1357 NOTICE: merging multiple inherited definitions of column "f1"
1358 NOTICE: merging column "f1" with inherited definition
1359 NOTICE: merging constraint "f1_pos" with inherited definition
1360 alter table p2 drop constraint f1_pos;
1361 alter table p1 drop constraint f1_pos;
1362 \d p1p2_c*
1363 Table "public.p1p2_c1"
1364 Column | Type | Collation | Nullable | Default
1365 --------+---------+-----------+----------+---------
1366 f1 | integer | | |
1367 Inherits: p1,
1368 p2
1370 Table "public.p1p2_c2"
1371 Column | Type | Collation | Nullable | Default
1372 --------+---------+-----------+----------+---------
1373 f1 | integer | | |
1374 Check constraints:
1375 "f1_pos" CHECK (f1 > 0)
1376 Inherits: p1,
1377 p2
1379 drop table p1, p2 cascade;
1380 NOTICE: drop cascades to 2 other objects
1381 DETAIL: drop cascades to table p1p2_c1
1382 drop cascades to table p1p2_c2
1383 create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1384 create table p1_c1() inherits (p1);
1385 create table p1_c2() inherits (p1);
1386 create table p1_c1c2() inherits (p1_c1, p1_c2);
1387 NOTICE: merging multiple inherited definitions of column "f1"
1388 \d p1_c1c2
1389 Table "public.p1_c1c2"
1390 Column | Type | Collation | Nullable | Default
1391 --------+---------+-----------+----------+---------
1392 f1 | integer | | |
1393 Check constraints:
1394 "f1_pos" CHECK (f1 > 0)
1395 Inherits: p1_c1,
1396 p1_c2
1398 alter table p1 drop constraint f1_pos;
1399 \d p1_c1c2
1400 Table "public.p1_c1c2"
1401 Column | Type | Collation | Nullable | Default
1402 --------+---------+-----------+----------+---------
1403 f1 | integer | | |
1404 Inherits: p1_c1,
1405 p1_c2
1407 drop table p1 cascade;
1408 NOTICE: drop cascades to 3 other objects
1409 DETAIL: drop cascades to table p1_c1
1410 drop cascades to table p1_c2
1411 drop cascades to table p1_c1c2
1412 create table p1(f1 int constraint f1_pos CHECK (f1 > 0));
1413 create table p1_c1() inherits (p1);
1414 create table p1_c2(constraint f1_pos CHECK (f1 > 0)) inherits (p1);
1415 NOTICE: merging constraint "f1_pos" with inherited definition
1416 create table p1_c1c2() inherits (p1_c1, p1_c2, p1);
1417 NOTICE: merging multiple inherited definitions of column "f1"
1418 NOTICE: merging multiple inherited definitions of column "f1"
1419 alter table p1_c2 drop constraint f1_pos;
1420 ERROR: cannot drop inherited constraint "f1_pos" of relation "p1_c2"
1421 alter table p1 drop constraint f1_pos;
1422 alter table p1_c1c2 drop constraint f1_pos;
1423 ERROR: cannot drop inherited constraint "f1_pos" of relation "p1_c1c2"
1424 alter table p1_c2 drop constraint f1_pos;
1425 \d p1_c1c2
1426 Table "public.p1_c1c2"
1427 Column | Type | Collation | Nullable | Default
1428 --------+---------+-----------+----------+---------
1429 f1 | integer | | |
1430 Inherits: p1_c1,
1431 p1_c2,
1432 p1
1434 drop table p1 cascade;
1435 NOTICE: drop cascades to 3 other objects
1436 DETAIL: drop cascades to table p1_c1
1437 drop cascades to table p1_c2
1438 drop cascades to table p1_c1c2
1439 -- Test that a valid child can have not-valid parent, but not vice versa
1440 create table invalid_check_con(f1 int);
1441 create table invalid_check_con_child() inherits(invalid_check_con);
1442 alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0) not valid;
1443 alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0); -- fail
1444 ERROR: constraint "inh_check_constraint" conflicts with NOT VALID constraint on relation "invalid_check_con_child"
1445 alter table invalid_check_con_child drop constraint inh_check_constraint;
1446 insert into invalid_check_con values(0);
1447 alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0);
1448 alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0) not valid;
1449 NOTICE: merging constraint "inh_check_constraint" with inherited definition
1450 insert into invalid_check_con values(0); -- fail
1451 ERROR: new row for relation "invalid_check_con" violates check constraint "inh_check_constraint"
1452 DETAIL: Failing row contains (0).
1453 insert into invalid_check_con_child values(0); -- fail
1454 ERROR: new row for relation "invalid_check_con_child" violates check constraint "inh_check_constraint"
1455 DETAIL: Failing row contains (0).
1456 select conrelid::regclass::text as relname, conname,
1457 convalidated, conislocal, coninhcount, connoinherit
1458 from pg_constraint where conname like 'inh\_check\_constraint%'
1459 order by 1, 2;
1460 relname | conname | convalidated | conislocal | coninhcount | connoinherit
1461 -------------------------+----------------------+--------------+------------+-------------+--------------
1462 invalid_check_con | inh_check_constraint | f | t | 0 | f
1463 invalid_check_con_child | inh_check_constraint | t | t | 1 | f
1464 (2 rows)
1466 -- We don't drop the invalid_check_con* tables, to test dump/reload with
1467 --
1468 -- Test parameterized append plans for inheritance trees
1469 --
1470 create temp table patest0 (id, x) as
1471 select x, x from generate_series(0,1000) x;
1472 create temp table patest1() inherits (patest0);
1473 insert into patest1
1474 select x, x from generate_series(0,1000) x;
1475 create temp table patest2() inherits (patest0);
1476 insert into patest2
1477 select x, x from generate_series(0,1000) x;
1478 create index patest0i on patest0(id);
1479 create index patest1i on patest1(id);
1480 create index patest2i on patest2(id);
1481 analyze patest0;
1482 analyze patest1;
1483 analyze patest2;
1484 explain (costs off)
1485 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1486 QUERY PLAN
1487 ------------------------------------------------------------
1488 Nested Loop
1489 -> Limit
1490 -> Seq Scan on int4_tbl
1491 -> Append
1492 -> Index Scan using patest0i on patest0 patest0_1
1493 Index Cond: (id = int4_tbl.f1)
1494 -> Index Scan using patest1i on patest1 patest0_2
1495 Index Cond: (id = int4_tbl.f1)
1496 -> Index Scan using patest2i on patest2 patest0_3
1497 Index Cond: (id = int4_tbl.f1)
1498 (10 rows)
1500 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1501 id | x | f1
1502 ----+---+----
1503 0 | 0 | 0
1504 0 | 0 | 0
1505 0 | 0 | 0
1506 (3 rows)
1508 drop index patest2i;
1509 explain (costs off)
1510 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1511 QUERY PLAN
1512 ------------------------------------------------------------
1513 Nested Loop
1514 -> Limit
1515 -> Seq Scan on int4_tbl
1516 -> Append
1517 -> Index Scan using patest0i on patest0 patest0_1
1518 Index Cond: (id = int4_tbl.f1)
1519 -> Index Scan using patest1i on patest1 patest0_2
1520 Index Cond: (id = int4_tbl.f1)
1521 -> Seq Scan on patest2 patest0_3
1522 Filter: (int4_tbl.f1 = id)
1523 (10 rows)
1525 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1526 id | x | f1
1527 ----+---+----
1528 0 | 0 | 0
1529 0 | 0 | 0
1530 0 | 0 | 0
1531 (3 rows)
1533 drop table patest0 cascade;
1534 NOTICE: drop cascades to 2 other objects
1535 DETAIL: drop cascades to table patest1
1536 drop cascades to table patest2
1537 --
1538 -- Test merge-append plans for inheritance trees
1539 --
1540 create table matest0 (id serial primary key, name text);
1541 create table matest1 (id integer primary key) inherits (matest0);
1542 NOTICE: merging column "id" with inherited definition
1543 create table matest2 (id integer primary key) inherits (matest0);
1544 NOTICE: merging column "id" with inherited definition
1545 create table matest3 (id integer primary key) inherits (matest0);
1546 NOTICE: merging column "id" with inherited definition
1547 create index matest0i on matest0 ((1-id));
1548 create index matest1i on matest1 ((1-id));
1549 -- create index matest2i on matest2 ((1-id)); -- intentionally missing
1550 create index matest3i on matest3 ((1-id));
1551 insert into matest1 (name) values ('Test 1');
1552 insert into matest1 (name) values ('Test 2');
1553 insert into matest2 (name) values ('Test 3');
1554 insert into matest2 (name) values ('Test 4');
1555 insert into matest3 (name) values ('Test 5');
1556 insert into matest3 (name) values ('Test 6');
1557 set enable_indexscan = off; -- force use of seqscan/sort, so no merge
1558 explain (verbose, costs off) select * from matest0 order by 1-id;
1559 QUERY PLAN
1560 ------------------------------------------------------------
1561 Sort
1562 Output: matest0.id, matest0.name, ((1 - matest0.id))
1563 Sort Key: ((1 - matest0.id))
1564 -> Result
1565 Output: matest0.id, matest0.name, (1 - matest0.id)
1566 -> Append
1567 -> Seq Scan on public.matest0 matest0_1
1568 Output: matest0_1.id, matest0_1.name
1569 -> Seq Scan on public.matest1 matest0_2
1570 Output: matest0_2.id, matest0_2.name
1571 -> Seq Scan on public.matest2 matest0_3
1572 Output: matest0_3.id, matest0_3.name
1573 -> Seq Scan on public.matest3 matest0_4
1574 Output: matest0_4.id, matest0_4.name
1575 (14 rows)
1577 select * from matest0 order by 1-id;
1578 id | name
1579 ----+--------
1580 6 | Test 6
1581 5 | Test 5
1582 4 | Test 4
1583 3 | Test 3
1584 2 | Test 2
1585 1 | Test 1
1586 (6 rows)
1588 explain (verbose, costs off) select min(1-id) from matest0;
1589 QUERY PLAN
1590 --------------------------------------------------
1591 Aggregate
1592 Output: min((1 - matest0.id))
1593 -> Append
1594 -> Seq Scan on public.matest0 matest0_1
1595 Output: matest0_1.id
1596 -> Seq Scan on public.matest1 matest0_2
1597 Output: matest0_2.id
1598 -> Seq Scan on public.matest2 matest0_3
1599 Output: matest0_3.id
1600 -> Seq Scan on public.matest3 matest0_4
1601 Output: matest0_4.id
1602 (11 rows)
1604 select min(1-id) from matest0;
1605 min
1606 -----
1607 -5
1608 (1 row)
1610 reset enable_indexscan;
1611 set enable_seqscan = off; -- plan with fewest seqscans should be merge
1612 set enable_parallel_append = off; -- Don't let parallel-append interfere
1613 explain (verbose, costs off) select * from matest0 order by 1-id;
1614 QUERY PLAN
1615 ------------------------------------------------------------------------
1616 Merge Append
1617 Disabled Nodes: 1
1618 Sort Key: ((1 - matest0.id))
1619 -> Index Scan using matest0i on public.matest0 matest0_1
1620 Output: matest0_1.id, matest0_1.name, (1 - matest0_1.id)
1621 -> Index Scan using matest1i on public.matest1 matest0_2
1622 Output: matest0_2.id, matest0_2.name, (1 - matest0_2.id)
1623 -> Sort
1624 Output: matest0_3.id, matest0_3.name, ((1 - matest0_3.id))
1625 Sort Key: ((1 - matest0_3.id))
1626 -> Seq Scan on public.matest2 matest0_3
1627 Disabled Nodes: 1
1628 Output: matest0_3.id, matest0_3.name, (1 - matest0_3.id)
1629 -> Index Scan using matest3i on public.matest3 matest0_4
1630 Output: matest0_4.id, matest0_4.name, (1 - matest0_4.id)
1631 (15 rows)
1633 select * from matest0 order by 1-id;
1634 id | name
1635 ----+--------
1636 6 | Test 6
1637 5 | Test 5
1638 4 | Test 4
1639 3 | Test 3
1640 2 | Test 2
1641 1 | Test 1
1642 (6 rows)
1644 explain (verbose, costs off) select min(1-id) from matest0;
1645 QUERY PLAN
1646 ---------------------------------------------------------------------------------
1647 Result
1648 Output: (InitPlan 1).col1
1649 InitPlan 1
1650 -> Limit
1651 Output: ((1 - matest0.id))
1652 -> Result
1653 Output: ((1 - matest0.id))
1654 -> Merge Append
1655 Sort Key: ((1 - matest0.id))
1656 -> Index Scan using matest0i on public.matest0 matest0_1
1657 Output: matest0_1.id, (1 - matest0_1.id)
1658 Index Cond: ((1 - matest0_1.id) IS NOT NULL)
1659 -> Index Scan using matest1i on public.matest1 matest0_2
1660 Output: matest0_2.id, (1 - matest0_2.id)
1661 Index Cond: ((1 - matest0_2.id) IS NOT NULL)
1662 -> Sort
1663 Output: matest0_3.id, ((1 - matest0_3.id))
1664 Sort Key: ((1 - matest0_3.id))
1665 -> Bitmap Heap Scan on public.matest2 matest0_3
1666 Output: matest0_3.id, (1 - matest0_3.id)
1667 Filter: ((1 - matest0_3.id) IS NOT NULL)
1668 -> Bitmap Index Scan on matest2_pkey
1669 -> Index Scan using matest3i on public.matest3 matest0_4
1670 Output: matest0_4.id, (1 - matest0_4.id)
1671 Index Cond: ((1 - matest0_4.id) IS NOT NULL)
1672 (25 rows)
1674 select min(1-id) from matest0;
1675 min
1676 -----
1677 -5
1678 (1 row)
1680 reset enable_seqscan;
1681 reset enable_parallel_append;
1682 drop table matest0 cascade;
1683 NOTICE: drop cascades to 3 other objects
1684 DETAIL: drop cascades to table matest1
1685 drop cascades to table matest2
1686 drop cascades to table matest3
1687 --
1688 -- Check that use of an index with an extraneous column doesn't produce
1689 -- a plan with extraneous sorting
1690 --
1691 create table matest0 (a int, b int, c int, d int);
1692 create table matest1 () inherits(matest0);
1693 create index matest0i on matest0 (b, c);
1694 create index matest1i on matest1 (b, c);
1695 set enable_nestloop = off; -- we want a plan with two MergeAppends
1696 explain (costs off)
1697 select t1.* from matest0 t1, matest0 t2
1698 where t1.b = t2.b and t2.c = t2.d
1699 order by t1.b limit 10;
1700 QUERY PLAN
1701 -------------------------------------------------------------------
1702 Limit
1703 -> Merge Join
1704 Merge Cond: (t1.b = t2.b)
1705 -> Merge Append
1706 Sort Key: t1.b
1707 -> Index Scan using matest0i on matest0 t1_1
1708 -> Index Scan using matest1i on matest1 t1_2
1709 -> Materialize
1710 -> Merge Append
1711 Sort Key: t2.b
1712 -> Index Scan using matest0i on matest0 t2_1
1713 Filter: (c = d)
1714 -> Index Scan using matest1i on matest1 t2_2
1715 Filter: (c = d)
1716 (14 rows)
1718 reset enable_nestloop;
1719 drop table matest0 cascade;
1720 NOTICE: drop cascades to table matest1
1721 -- Test a MergeAppend plan where one child requires a sort
1722 create table matest0(a int primary key);
1723 create table matest1() inherits (matest0);
1724 insert into matest0 select generate_series(1, 400);
1725 insert into matest1 select generate_series(1, 200);
1726 analyze matest0;
1727 analyze matest1;
1728 explain (costs off)
1729 select * from matest0 where a < 100 order by a;
1730 QUERY PLAN
1731 ---------------------------------------------------------------
1732 Merge Append
1733 Sort Key: matest0.a
1734 -> Index Only Scan using matest0_pkey on matest0 matest0_1
1735 Index Cond: (a < 100)
1736 -> Sort
1737 Sort Key: matest0_2.a
1738 -> Seq Scan on matest1 matest0_2
1739 Filter: (a < 100)
1740 (8 rows)
1742 drop table matest0 cascade;
1743 NOTICE: drop cascades to table matest1
1744 --
1745 -- Test merge-append for UNION ALL append relations
1746 --
1747 set enable_seqscan = off;
1748 set enable_indexscan = on;
1749 set enable_bitmapscan = off;
1750 -- Check handling of duplicated, constant, or volatile targetlist items
1751 explain (costs off)
1752 SELECT thousand, tenthous FROM tenk1
1753 UNION ALL
1754 SELECT thousand, thousand FROM tenk1
1755 ORDER BY thousand, tenthous;
1756 QUERY PLAN
1757 -------------------------------------------------------------------------
1758 Merge Append
1759 Sort Key: tenk1.thousand, tenk1.tenthous
1760 -> Index Only Scan using tenk1_thous_tenthous on tenk1
1761 -> Sort
1762 Sort Key: tenk1_1.thousand, tenk1_1.thousand
1763 -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
1764 (6 rows)
1766 explain (costs off)
1767 SELECT thousand, tenthous, thousand+tenthous AS x FROM tenk1
1768 UNION ALL
1769 SELECT 42, 42, hundred FROM tenk1
1770 ORDER BY thousand, tenthous;
1771 QUERY PLAN
1772 ------------------------------------------------------------------
1773 Merge Append
1774 Sort Key: tenk1.thousand, tenk1.tenthous
1775 -> Index Only Scan using tenk1_thous_tenthous on tenk1
1776 -> Sort
1777 Sort Key: 42, 42
1778 -> Index Only Scan using tenk1_hundred on tenk1 tenk1_1
1779 (6 rows)
1781 explain (costs off)
1782 SELECT thousand, tenthous FROM tenk1
1783 UNION ALL
1784 SELECT thousand, random()::integer FROM tenk1
1785 ORDER BY thousand, tenthous;
1786 QUERY PLAN
1787 -------------------------------------------------------------------------
1788 Merge Append
1789 Sort Key: tenk1.thousand, tenk1.tenthous
1790 -> Index Only Scan using tenk1_thous_tenthous on tenk1
1791 -> Sort
1792 Sort Key: tenk1_1.thousand, ((random())::integer)
1793 -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
1794 (6 rows)
1796 -- Check min/max aggregate optimization
1797 explain (costs off)
1798 SELECT min(x) FROM
1799 (SELECT unique1 AS x FROM tenk1 a
1800 UNION ALL
1801 SELECT unique2 AS x FROM tenk1 b) s;
1802 QUERY PLAN
1803 --------------------------------------------------------------------
1804 Result
1805 InitPlan 1
1806 -> Limit
1807 -> Merge Append
1808 Sort Key: a.unique1
1809 -> Index Only Scan using tenk1_unique1 on tenk1 a
1810 Index Cond: (unique1 IS NOT NULL)
1811 -> Index Only Scan using tenk1_unique2 on tenk1 b
1812 Index Cond: (unique2 IS NOT NULL)
1813 (9 rows)
1815 explain (costs off)
1816 SELECT min(y) FROM
1817 (SELECT unique1 AS x, unique1 AS y FROM tenk1 a
1818 UNION ALL
1819 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s;
1820 QUERY PLAN
1821 --------------------------------------------------------------------
1822 Result
1823 InitPlan 1
1824 -> Limit
1825 -> Merge Append
1826 Sort Key: a.unique1
1827 -> Index Only Scan using tenk1_unique1 on tenk1 a
1828 Index Cond: (unique1 IS NOT NULL)
1829 -> Index Only Scan using tenk1_unique2 on tenk1 b
1830 Index Cond: (unique2 IS NOT NULL)
1831 (9 rows)
1833 -- XXX planner doesn't recognize that index on unique2 is sufficiently sorted
1834 explain (costs off)
1835 SELECT x, y FROM
1836 (SELECT thousand AS x, tenthous AS y FROM tenk1 a
1837 UNION ALL
1838 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s
1839 ORDER BY x, y;
1840 QUERY PLAN
1841 -------------------------------------------------------------
1842 Merge Append
1843 Sort Key: a.thousand, a.tenthous
1844 -> Index Only Scan using tenk1_thous_tenthous on tenk1 a
1845 -> Sort
1846 Sort Key: b.unique2, b.unique2
1847 -> Index Only Scan using tenk1_unique2 on tenk1 b
1848 (6 rows)
1850 -- exercise rescan code path via a repeatedly-evaluated subquery
1851 explain (costs off)
1852 SELECT
1853 ARRAY(SELECT f.i FROM (
1854 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1)
1855 UNION ALL
1856 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1)
1857 ) f(i)
1858 ORDER BY f.i LIMIT 10)
1859 FROM generate_series(1, 3) g(i);
1860 QUERY PLAN
1861 ----------------------------------------------------------------
1862 Function Scan on generate_series g
1863 SubPlan 1
1864 -> Limit
1865 -> Merge Append
1866 Sort Key: ((d.d + g.i))
1867 -> Sort
1868 Sort Key: ((d.d + g.i))
1869 -> Function Scan on generate_series d
1870 -> Sort
1871 Sort Key: ((d_1.d + g.i))
1872 -> Function Scan on generate_series d_1
1873 (11 rows)
1875 SELECT
1876 ARRAY(SELECT f.i FROM (
1877 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1)
1878 UNION ALL
1879 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1)
1880 ) f(i)
1881 ORDER BY f.i LIMIT 10)
1882 FROM generate_series(1, 3) g(i);
1883 array
1884 ------------------------------
1885 {1,5,6,8,11,11,14,16,17,20}
1886 {2,6,7,9,12,12,15,17,18,21}
1887 {3,7,8,10,13,13,16,18,19,22}
1888 (3 rows)
1890 reset enable_seqscan;
1891 reset enable_indexscan;
1892 reset enable_bitmapscan;
1893 --
1894 -- Check handling of MULTIEXPR SubPlans in inherited updates
1895 --
1896 create table inhpar(f1 int, f2 name);
1897 create table inhcld(f2 name, f1 int);
1898 alter table inhcld inherit inhpar;
1899 insert into inhpar select x, x::text from generate_series(1,5) x;
1900 insert into inhcld select x::text, x from generate_series(6,10) x;
1901 explain (verbose, costs off)
1902 update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
1903 QUERY PLAN
1904 --------------------------------------------------------------------------------------------
1905 Update on public.inhpar i
1906 Update on public.inhpar i_1
1907 Update on public.inhcld i_2
1908 -> Result
1909 Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), i.tableoid, i.ctid
1910 -> Append
1911 -> Seq Scan on public.inhpar i_1
1912 Output: i_1.f1, i_1.f2, i_1.tableoid, i_1.ctid
1913 -> Seq Scan on public.inhcld i_2
1914 Output: i_2.f1, i_2.f2, i_2.tableoid, i_2.ctid
1915 SubPlan 1
1916 -> Limit
1917 Output: (i.f1), (((i.f2)::text || '-'::text))
1918 -> Seq Scan on public.int4_tbl
1919 Output: i.f1, ((i.f2)::text || '-'::text)
1920 (15 rows)
1922 update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
1923 select * from inhpar;
1924 f1 | f2
1925 ----+-----
1926 1 | 1-
1927 2 | 2-
1928 3 | 3-
1929 4 | 4-
1930 5 | 5-
1931 6 | 6-
1932 7 | 7-
1933 8 | 8-
1934 9 | 9-
1935 10 | 10-
1936 (10 rows)
1938 drop table inhpar cascade;
1939 NOTICE: drop cascades to table inhcld
1940 --
1941 -- And the same for partitioned cases
1942 --
1943 create table inhpar(f1 int primary key, f2 name) partition by range (f1);
1944 create table inhcld1(f2 name, f1 int primary key);
1945 create table inhcld2(f1 int primary key, f2 name);
1946 alter table inhpar attach partition inhcld1 for values from (1) to (5);
1947 alter table inhpar attach partition inhcld2 for values from (5) to (100);
1948 insert into inhpar select x, x::text from generate_series(1,10) x;
1949 explain (verbose, costs off)
1950 update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
1951 QUERY PLAN
1952 ------------------------------------------------------------------------------------------------------
1953 Update on public.inhpar i
1954 Update on public.inhcld1 i_1
1955 Update on public.inhcld2 i_2
1956 -> Append
1957 -> Seq Scan on public.inhcld1 i_1
1958 Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), i_1.tableoid, i_1.ctid
1959 SubPlan 1
1960 -> Limit
1961 Output: (i_1.f1), (((i_1.f2)::text || '-'::text))
1962 -> Seq Scan on public.int4_tbl
1963 Output: i_1.f1, ((i_1.f2)::text || '-'::text)
1964 -> Seq Scan on public.inhcld2 i_2
1965 Output: (SubPlan 1).col1, (SubPlan 1).col2, (rescan SubPlan 1), i_2.tableoid, i_2.ctid
1966 (13 rows)
1968 update inhpar i set (f1, f2) = (select i.f1, i.f2 || '-' from int4_tbl limit 1);
1969 select * from inhpar;
1970 f1 | f2
1971 ----+-----
1972 1 | 1-
1973 2 | 2-
1974 3 | 3-
1975 4 | 4-
1976 5 | 5-
1977 6 | 6-
1978 7 | 7-
1979 8 | 8-
1980 9 | 9-
1981 10 | 10-
1982 (10 rows)
1984 -- Also check ON CONFLICT
1985 insert into inhpar as i values (3), (7) on conflict (f1)
1986 do update set (f1, f2) = (select i.f1, i.f2 || '+');
1987 select * from inhpar order by f1; -- tuple order might be unstable here
1988 f1 | f2
1989 ----+-----
1990 1 | 1-
1991 2 | 2-
1992 3 | 3-+
1993 4 | 4-
1994 5 | 5-
1995 6 | 6-
1996 7 | 7-+
1997 8 | 8-
1998 9 | 9-
1999 10 | 10-
2000 (10 rows)
2002 drop table inhpar cascade;
2003 --
2004 -- Check handling of a constant-null CHECK constraint
2005 --
2006 create table cnullparent (f1 int);
2007 create table cnullchild (check (f1 = 1 or f1 = null)) inherits(cnullparent);
2008 insert into cnullchild values(1);
2009 insert into cnullchild values(2);
2010 insert into cnullchild values(null);
2011 select * from cnullparent;
2012 f1
2013 ----
2014 1
2015 2
2017 (3 rows)
2019 select * from cnullparent where f1 = 2;
2020 f1
2021 ----
2022 2
2023 (1 row)
2025 drop table cnullparent cascade;
2026 NOTICE: drop cascades to table cnullchild
2027 --
2028 -- Mixed ownership inheritance tree
2029 --
2030 create role regress_alice;
2031 create role regress_bob;
2032 grant all on schema public to regress_alice, regress_bob;
2033 grant regress_alice to regress_bob;
2034 set session authorization regress_alice;
2035 create table inh_parent (a int not null);
2036 set session authorization regress_bob;
2037 create table inh_child () inherits (inh_parent);
2038 set session authorization regress_alice;
2039 -- alice can't do this: she doesn't own inh_child
2040 alter table inh_parent alter a drop not null;
2041 ERROR: must be owner of table inh_child
2042 set session authorization regress_bob;
2043 alter table inh_parent alter a drop not null;
2044 reset session authorization;
2045 drop table inh_parent, inh_child;
2046 revoke all on schema public from regress_alice, regress_bob;
2047 drop role regress_alice, regress_bob;
2048 --
2049 -- Check use of temporary tables with inheritance trees
2050 --
2051 create table inh_perm_parent (a1 int);
2052 create temp table inh_temp_parent (a1 int);
2053 create temp table inh_temp_child () inherits (inh_perm_parent); -- ok
2054 create table inh_perm_child () inherits (inh_temp_parent); -- error
2055 ERROR: cannot inherit from temporary relation "inh_temp_parent"
2056 create temp table inh_temp_child_2 () inherits (inh_temp_parent); -- ok
2057 insert into inh_perm_parent values (1);
2058 insert into inh_temp_parent values (2);
2059 insert into inh_temp_child values (3);
2060 insert into inh_temp_child_2 values (4);
2061 select tableoid::regclass, a1 from inh_perm_parent;
2062 tableoid | a1
2063 -----------------+----
2064 inh_perm_parent | 1
2065 inh_temp_child | 3
2066 (2 rows)
2068 select tableoid::regclass, a1 from inh_temp_parent;
2069 tableoid | a1
2070 ------------------+----
2071 inh_temp_parent | 2
2072 inh_temp_child_2 | 4
2073 (2 rows)
2075 drop table inh_perm_parent cascade;
2076 NOTICE: drop cascades to table inh_temp_child
2077 drop table inh_temp_parent cascade;
2078 NOTICE: drop cascades to table inh_temp_child_2
2079 --
2080 -- Check that constraint exclusion works correctly with partitions using
2081 -- implicit constraints generated from the partition bound information.
2082 --
2083 create table list_parted (
2084 a varchar
2085 ) partition by list (a);
2086 create table part_ab_cd partition of list_parted for values in ('ab', 'cd');
2087 create table part_ef_gh partition of list_parted for values in ('ef', 'gh');
2088 create table part_null_xy partition of list_parted for values in (null, 'xy');
2089 explain (costs off) select * from list_parted;
2090 QUERY PLAN
2091 ----------------------------------------------
2092 Append
2093 -> Seq Scan on part_ab_cd list_parted_1
2094 -> Seq Scan on part_ef_gh list_parted_2
2095 -> Seq Scan on part_null_xy list_parted_3
2096 (4 rows)
2098 explain (costs off) select * from list_parted where a is null;
2099 QUERY PLAN
2100 --------------------------------------
2101 Seq Scan on part_null_xy list_parted
2102 Filter: (a IS NULL)
2103 (2 rows)
2105 explain (costs off) select * from list_parted where a is not null;
2106 QUERY PLAN
2107 ----------------------------------------------
2108 Append
2109 -> Seq Scan on part_ab_cd list_parted_1
2110 Filter: (a IS NOT NULL)
2111 -> Seq Scan on part_ef_gh list_parted_2
2112 Filter: (a IS NOT NULL)
2113 -> Seq Scan on part_null_xy list_parted_3
2114 Filter: (a IS NOT NULL)
2115 (7 rows)
2117 explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef');
2118 QUERY PLAN
2119 ----------------------------------------------------------
2120 Append
2121 -> Seq Scan on part_ab_cd list_parted_1
2122 Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
2123 -> Seq Scan on part_ef_gh list_parted_2
2124 Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
2125 (5 rows)
2127 explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd');
2128 QUERY PLAN
2129 ---------------------------------------------------------------------------------
2130 Seq Scan on part_ab_cd list_parted
2131 Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
2132 (2 rows)
2134 explain (costs off) select * from list_parted where a = 'ab';
2135 QUERY PLAN
2136 ------------------------------------
2137 Seq Scan on part_ab_cd list_parted
2138 Filter: ((a)::text = 'ab'::text)
2139 (2 rows)
2141 create table range_list_parted (
2142 a int,
2143 b char(2)
2144 ) partition by range (a);
2145 create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b);
2146 create table part_1_10_ab partition of part_1_10 for values in ('ab');
2147 create table part_1_10_cd partition of part_1_10 for values in ('cd');
2148 create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b);
2149 create table part_10_20_ab partition of part_10_20 for values in ('ab');
2150 create table part_10_20_cd partition of part_10_20 for values in ('cd');
2151 create table part_21_30 partition of range_list_parted for values from (21) to (30) partition by list (b);
2152 create table part_21_30_ab partition of part_21_30 for values in ('ab');
2153 create table part_21_30_cd partition of part_21_30 for values in ('cd');
2154 create table part_40_inf partition of range_list_parted for values from (40) to (maxvalue) partition by list (b);
2155 create table part_40_inf_ab partition of part_40_inf for values in ('ab');
2156 create table part_40_inf_cd partition of part_40_inf for values in ('cd');
2157 create table part_40_inf_null partition of part_40_inf for values in (null);
2158 explain (costs off) select * from range_list_parted;
2159 QUERY PLAN
2160 --------------------------------------------------------
2161 Append
2162 -> Seq Scan on part_1_10_ab range_list_parted_1
2163 -> Seq Scan on part_1_10_cd range_list_parted_2
2164 -> Seq Scan on part_10_20_ab range_list_parted_3
2165 -> Seq Scan on part_10_20_cd range_list_parted_4
2166 -> Seq Scan on part_21_30_ab range_list_parted_5
2167 -> Seq Scan on part_21_30_cd range_list_parted_6
2168 -> Seq Scan on part_40_inf_ab range_list_parted_7
2169 -> Seq Scan on part_40_inf_cd range_list_parted_8
2170 -> Seq Scan on part_40_inf_null range_list_parted_9
2171 (10 rows)
2173 explain (costs off) select * from range_list_parted where a = 5;
2174 QUERY PLAN
2175 ----------------------------------------------------
2176 Append
2177 -> Seq Scan on part_1_10_ab range_list_parted_1
2178 Filter: (a = 5)
2179 -> Seq Scan on part_1_10_cd range_list_parted_2
2180 Filter: (a = 5)
2181 (5 rows)
2183 explain (costs off) select * from range_list_parted where b = 'ab';
2184 QUERY PLAN
2185 ------------------------------------------------------
2186 Append
2187 -> Seq Scan on part_1_10_ab range_list_parted_1
2188 Filter: (b = 'ab'::bpchar)
2189 -> Seq Scan on part_10_20_ab range_list_parted_2
2190 Filter: (b = 'ab'::bpchar)
2191 -> Seq Scan on part_21_30_ab range_list_parted_3
2192 Filter: (b = 'ab'::bpchar)
2193 -> Seq Scan on part_40_inf_ab range_list_parted_4
2194 Filter: (b = 'ab'::bpchar)
2195 (9 rows)
2197 explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab');
2198 QUERY PLAN
2199 -----------------------------------------------------------------
2200 Append
2201 -> Seq Scan on part_1_10_ab range_list_parted_1
2202 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
2203 -> Seq Scan on part_10_20_ab range_list_parted_2
2204 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
2205 -> Seq Scan on part_21_30_ab range_list_parted_3
2206 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
2207 (7 rows)
2209 /* Should select no rows because range partition key cannot be null */
2210 explain (costs off) select * from range_list_parted where a is null;
2211 QUERY PLAN
2212 --------------------------
2213 Result
2214 One-Time Filter: false
2215 (2 rows)
2217 /* Should only select rows from the null-accepting partition */
2218 explain (costs off) select * from range_list_parted where b is null;
2219 QUERY PLAN
2220 ------------------------------------------------
2221 Seq Scan on part_40_inf_null range_list_parted
2222 Filter: (b IS NULL)
2223 (2 rows)
2225 explain (costs off) select * from range_list_parted where a is not null and a < 67;
2226 QUERY PLAN
2227 --------------------------------------------------------
2228 Append
2229 -> Seq Scan on part_1_10_ab range_list_parted_1
2230 Filter: ((a IS NOT NULL) AND (a < 67))
2231 -> Seq Scan on part_1_10_cd range_list_parted_2
2232 Filter: ((a IS NOT NULL) AND (a < 67))
2233 -> Seq Scan on part_10_20_ab range_list_parted_3
2234 Filter: ((a IS NOT NULL) AND (a < 67))
2235 -> Seq Scan on part_10_20_cd range_list_parted_4
2236 Filter: ((a IS NOT NULL) AND (a < 67))
2237 -> Seq Scan on part_21_30_ab range_list_parted_5
2238 Filter: ((a IS NOT NULL) AND (a < 67))
2239 -> Seq Scan on part_21_30_cd range_list_parted_6
2240 Filter: ((a IS NOT NULL) AND (a < 67))
2241 -> Seq Scan on part_40_inf_ab range_list_parted_7
2242 Filter: ((a IS NOT NULL) AND (a < 67))
2243 -> Seq Scan on part_40_inf_cd range_list_parted_8
2244 Filter: ((a IS NOT NULL) AND (a < 67))
2245 -> Seq Scan on part_40_inf_null range_list_parted_9
2246 Filter: ((a IS NOT NULL) AND (a < 67))
2247 (19 rows)
2249 explain (costs off) select * from range_list_parted where a >= 30;
2250 QUERY PLAN
2251 --------------------------------------------------------
2252 Append
2253 -> Seq Scan on part_40_inf_ab range_list_parted_1
2254 Filter: (a >= 30)
2255 -> Seq Scan on part_40_inf_cd range_list_parted_2
2256 Filter: (a >= 30)
2257 -> Seq Scan on part_40_inf_null range_list_parted_3
2258 Filter: (a >= 30)
2259 (7 rows)
2261 drop table list_parted;
2262 drop table range_list_parted;
2263 -- check that constraint exclusion is able to cope with the partition
2264 -- constraint emitted for multi-column range partitioned tables
2265 create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
2266 create table mcrparted_def partition of mcrparted default;
2267 create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, 1, 1);
2268 create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10);
2269 create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10);
2270 create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10);
2271 create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20);
2272 create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue);
2273 explain (costs off) select * from mcrparted where a = 0; -- scans mcrparted0, mcrparted_def
2274 QUERY PLAN
2275 ---------------------------------------------
2276 Append
2277 -> Seq Scan on mcrparted0 mcrparted_1
2278 Filter: (a = 0)
2279 -> Seq Scan on mcrparted_def mcrparted_2
2280 Filter: (a = 0)
2281 (5 rows)
2283 explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5; -- scans mcrparted1, mcrparted_def
2284 QUERY PLAN
2285 ---------------------------------------------
2286 Append
2287 -> Seq Scan on mcrparted1 mcrparted_1
2288 Filter: ((a = 10) AND (abs(b) < 5))
2289 -> Seq Scan on mcrparted_def mcrparted_2
2290 Filter: ((a = 10) AND (abs(b) < 5))
2291 (5 rows)
2293 explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scans mcrparted1, mcrparted2, mcrparted_def
2294 QUERY PLAN
2295 ---------------------------------------------
2296 Append
2297 -> Seq Scan on mcrparted1 mcrparted_1
2298 Filter: ((a = 10) AND (abs(b) = 5))
2299 -> Seq Scan on mcrparted2 mcrparted_2
2300 Filter: ((a = 10) AND (abs(b) = 5))
2301 -> Seq Scan on mcrparted_def mcrparted_3
2302 Filter: ((a = 10) AND (abs(b) = 5))
2303 (7 rows)
2305 explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all partitions
2306 QUERY PLAN
2307 ---------------------------------------------
2308 Append
2309 -> Seq Scan on mcrparted0 mcrparted_1
2310 Filter: (abs(b) = 5)
2311 -> Seq Scan on mcrparted1 mcrparted_2
2312 Filter: (abs(b) = 5)
2313 -> Seq Scan on mcrparted2 mcrparted_3
2314 Filter: (abs(b) = 5)
2315 -> Seq Scan on mcrparted3 mcrparted_4
2316 Filter: (abs(b) = 5)
2317 -> Seq Scan on mcrparted4 mcrparted_5
2318 Filter: (abs(b) = 5)
2319 -> Seq Scan on mcrparted5 mcrparted_6
2320 Filter: (abs(b) = 5)
2321 -> Seq Scan on mcrparted_def mcrparted_7
2322 Filter: (abs(b) = 5)
2323 (15 rows)
2325 explain (costs off) select * from mcrparted where a > -1; -- scans all partitions
2326 QUERY PLAN
2327 ---------------------------------------------
2328 Append
2329 -> Seq Scan on mcrparted0 mcrparted_1
2330 Filter: (a > '-1'::integer)
2331 -> Seq Scan on mcrparted1 mcrparted_2
2332 Filter: (a > '-1'::integer)
2333 -> Seq Scan on mcrparted2 mcrparted_3
2334 Filter: (a > '-1'::integer)
2335 -> Seq Scan on mcrparted3 mcrparted_4
2336 Filter: (a > '-1'::integer)
2337 -> Seq Scan on mcrparted4 mcrparted_5
2338 Filter: (a > '-1'::integer)
2339 -> Seq Scan on mcrparted5 mcrparted_6
2340 Filter: (a > '-1'::integer)
2341 -> Seq Scan on mcrparted_def mcrparted_7
2342 Filter: (a > '-1'::integer)
2343 (15 rows)
2345 explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10; -- scans mcrparted4
2346 QUERY PLAN
2347 -----------------------------------------------------
2348 Seq Scan on mcrparted4 mcrparted
2349 Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
2350 (2 rows)
2352 explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5, mcrparted_def
2353 QUERY PLAN
2354 ---------------------------------------------
2355 Append
2356 -> Seq Scan on mcrparted3 mcrparted_1
2357 Filter: ((c > 20) AND (a = 20))
2358 -> Seq Scan on mcrparted4 mcrparted_2
2359 Filter: ((c > 20) AND (a = 20))
2360 -> Seq Scan on mcrparted5 mcrparted_3
2361 Filter: ((c > 20) AND (a = 20))
2362 -> Seq Scan on mcrparted_def mcrparted_4
2363 Filter: ((c > 20) AND (a = 20))
2364 (9 rows)
2366 -- check that partitioned table Appends cope with being referenced in
2367 -- subplans
2368 create table parted_minmax (a int, b varchar(16)) partition by range (a);
2369 create table parted_minmax1 partition of parted_minmax for values from (1) to (10);
2370 create index parted_minmax1i on parted_minmax1 (a, b);
2371 insert into parted_minmax values (1,'12345');
2372 explain (costs off) select min(a), max(a) from parted_minmax where b = '12345';
2373 QUERY PLAN
2374 ------------------------------------------------------------------------------------------------
2375 Result
2376 InitPlan 1
2377 -> Limit
2378 -> Index Only Scan using parted_minmax1i on parted_minmax1 parted_minmax
2379 Index Cond: ((a IS NOT NULL) AND (b = '12345'::text))
2380 InitPlan 2
2381 -> Limit
2382 -> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax_1
2383 Index Cond: ((a IS NOT NULL) AND (b = '12345'::text))
2384 (9 rows)
2386 select min(a), max(a) from parted_minmax where b = '12345';
2387 min | max
2388 -----+-----
2389 1 | 1
2390 (1 row)
2392 drop table parted_minmax;
2393 -- Test code that uses Append nodes in place of MergeAppend when the
2394 -- partition ordering matches the desired ordering.
2395 create index mcrparted_a_abs_c_idx on mcrparted (a, abs(b), c);
2396 -- MergeAppend must be used when a default partition exists
2397 explain (costs off) select * from mcrparted order by a, abs(b), c;
2398 QUERY PLAN
2399 -------------------------------------------------------------------------------
2400 Merge Append
2401 Sort Key: mcrparted.a, (abs(mcrparted.b)), mcrparted.c
2402 -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
2403 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
2404 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
2405 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
2406 -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
2407 -> Index Scan using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6
2408 -> Index Scan using mcrparted_def_a_abs_c_idx on mcrparted_def mcrparted_7
2409 (9 rows)
2411 drop table mcrparted_def;
2412 -- Append is used for a RANGE partitioned table with no default
2413 -- and no subpartitions
2414 explain (costs off) select * from mcrparted order by a, abs(b), c;
2415 QUERY PLAN
2416 -------------------------------------------------------------------------
2417 Append
2418 -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
2419 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
2420 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
2421 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
2422 -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
2423 -> Index Scan using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6
2424 (7 rows)
2426 -- Append is used with subpaths in reverse order with backwards index scans
2427 explain (costs off) select * from mcrparted order by a desc, abs(b) desc, c desc;
2428 QUERY PLAN
2429 ----------------------------------------------------------------------------------
2430 Append
2431 -> Index Scan Backward using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6
2432 -> Index Scan Backward using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
2433 -> Index Scan Backward using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
2434 -> Index Scan Backward using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
2435 -> Index Scan Backward using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
2436 -> Index Scan Backward using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
2437 (7 rows)
2439 -- check that Append plan is used containing a MergeAppend for sub-partitions
2440 -- that are unordered.
2441 drop table mcrparted5;
2442 create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue) partition by list (a);
2443 create table mcrparted5a partition of mcrparted5 for values in(20);
2444 create table mcrparted5_def partition of mcrparted5 default;
2445 explain (costs off) select * from mcrparted order by a, abs(b), c;
2446 QUERY PLAN
2447 ---------------------------------------------------------------------------------------
2448 Append
2449 -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
2450 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
2451 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
2452 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
2453 -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
2454 -> Merge Append
2455 Sort Key: mcrparted_7.a, (abs(mcrparted_7.b)), mcrparted_7.c
2456 -> Index Scan using mcrparted5a_a_abs_c_idx on mcrparted5a mcrparted_7
2457 -> Index Scan using mcrparted5_def_a_abs_c_idx on mcrparted5_def mcrparted_8
2458 (10 rows)
2460 drop table mcrparted5_def;
2461 -- check that an Append plan is used and the sub-partitions are flattened
2462 -- into the main Append when the sub-partition is unordered but contains
2463 -- just a single sub-partition.
2464 explain (costs off) select a, abs(b) from mcrparted order by a, abs(b), c;
2465 QUERY PLAN
2466 ---------------------------------------------------------------------------
2467 Append
2468 -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
2469 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
2470 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
2471 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
2472 -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5
2473 -> Index Scan using mcrparted5a_a_abs_c_idx on mcrparted5a mcrparted_6
2474 (7 rows)
2476 -- check that Append is used when the sub-partitioned tables are pruned
2477 -- during planning.
2478 explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c;
2479 QUERY PLAN
2480 -------------------------------------------------------------------------
2481 Append
2482 -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1
2483 Index Cond: (a < 20)
2484 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
2485 Index Cond: (a < 20)
2486 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
2487 Index Cond: (a < 20)
2488 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
2489 Index Cond: (a < 20)
2490 (9 rows)
2492 set enable_bitmapscan to off;
2493 set enable_sort to off;
2494 create table mclparted (a int) partition by list(a);
2495 create table mclparted1 partition of mclparted for values in(1);
2496 create table mclparted2 partition of mclparted for values in(2);
2497 create index on mclparted (a);
2498 -- Ensure an Append is used for a list partition with an order by.
2499 explain (costs off) select * from mclparted order by a;
2500 QUERY PLAN
2501 ------------------------------------------------------------------------
2502 Append
2503 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
2504 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
2505 (3 rows)
2507 -- Ensure a MergeAppend is used when a partition exists with interleaved
2508 -- datums in the partition bound.
2509 create table mclparted3_5 partition of mclparted for values in(3,5);
2510 create table mclparted4 partition of mclparted for values in(4);
2511 explain (costs off) select * from mclparted order by a;
2512 QUERY PLAN
2513 ----------------------------------------------------------------------------
2514 Merge Append
2515 Sort Key: mclparted.a
2516 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
2517 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
2518 -> Index Only Scan using mclparted3_5_a_idx on mclparted3_5 mclparted_3
2519 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4
2520 (6 rows)
2522 explain (costs off) select * from mclparted where a in(3,4,5) order by a;
2523 QUERY PLAN
2524 ----------------------------------------------------------------------------
2525 Merge Append
2526 Sort Key: mclparted.a
2527 -> Index Only Scan using mclparted3_5_a_idx on mclparted3_5 mclparted_1
2528 Index Cond: (a = ANY ('{3,4,5}'::integer[]))
2529 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_2
2530 Index Cond: (a = ANY ('{3,4,5}'::integer[]))
2531 (6 rows)
2533 -- Introduce a NULL and DEFAULT partition so we can test more complex cases
2534 create table mclparted_null partition of mclparted for values in(null);
2535 create table mclparted_def partition of mclparted default;
2536 -- Append can be used providing we don't scan the interleaved partition
2537 explain (costs off) select * from mclparted where a in(1,2,4) order by a;
2538 QUERY PLAN
2539 ------------------------------------------------------------------------
2540 Append
2541 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
2542 Index Cond: (a = ANY ('{1,2,4}'::integer[]))
2543 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
2544 Index Cond: (a = ANY ('{1,2,4}'::integer[]))
2545 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
2546 Index Cond: (a = ANY ('{1,2,4}'::integer[]))
2547 (7 rows)
2549 explain (costs off) select * from mclparted where a in(1,2,4) or a is null order by a;
2550 QUERY PLAN
2551 --------------------------------------------------------------------------------
2552 Append
2553 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
2554 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
2555 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
2556 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
2557 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
2558 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
2559 -> Index Only Scan using mclparted_null_a_idx on mclparted_null mclparted_4
2560 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
2561 (9 rows)
2563 -- Test a more complex case where the NULL partition allows some other value
2564 drop table mclparted_null;
2565 create table mclparted_0_null partition of mclparted for values in(0,null);
2566 -- Ensure MergeAppend is used since 0 and NULLs are in the same partition.
2567 explain (costs off) select * from mclparted where a in(1,2,4) or a is null order by a;
2568 QUERY PLAN
2569 ------------------------------------------------------------------------------------
2570 Merge Append
2571 Sort Key: mclparted.a
2572 -> Index Only Scan using mclparted_0_null_a_idx on mclparted_0_null mclparted_1
2573 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
2574 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_2
2575 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
2576 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_3
2577 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
2578 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4
2579 Filter: ((a = ANY ('{1,2,4}'::integer[])) OR (a IS NULL))
2580 (10 rows)
2582 explain (costs off) select * from mclparted where a in(0,1,2,4) order by a;
2583 QUERY PLAN
2584 ------------------------------------------------------------------------------------
2585 Merge Append
2586 Sort Key: mclparted.a
2587 -> Index Only Scan using mclparted_0_null_a_idx on mclparted_0_null mclparted_1
2588 Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
2589 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_2
2590 Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
2591 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_3
2592 Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
2593 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_4
2594 Index Cond: (a = ANY ('{0,1,2,4}'::integer[]))
2595 (10 rows)
2597 -- Ensure Append is used when the null partition is pruned
2598 explain (costs off) select * from mclparted where a in(1,2,4) order by a;
2599 QUERY PLAN
2600 ------------------------------------------------------------------------
2601 Append
2602 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
2603 Index Cond: (a = ANY ('{1,2,4}'::integer[]))
2604 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
2605 Index Cond: (a = ANY ('{1,2,4}'::integer[]))
2606 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
2607 Index Cond: (a = ANY ('{1,2,4}'::integer[]))
2608 (7 rows)
2610 -- Ensure MergeAppend is used when the default partition is not pruned
2611 explain (costs off) select * from mclparted where a in(1,2,4,100) order by a;
2612 QUERY PLAN
2613 ------------------------------------------------------------------------------
2614 Merge Append
2615 Sort Key: mclparted.a
2616 -> Index Only Scan using mclparted1_a_idx on mclparted1 mclparted_1
2617 Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
2618 -> Index Only Scan using mclparted2_a_idx on mclparted2 mclparted_2
2619 Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
2620 -> Index Only Scan using mclparted4_a_idx on mclparted4 mclparted_3
2621 Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
2622 -> Index Only Scan using mclparted_def_a_idx on mclparted_def mclparted_4
2623 Index Cond: (a = ANY ('{1,2,4,100}'::integer[]))
2624 (10 rows)
2626 drop table mclparted;
2627 reset enable_sort;
2628 reset enable_bitmapscan;
2629 -- Ensure subplans which don't have a path with the correct pathkeys get
2630 -- sorted correctly.
2631 drop index mcrparted_a_abs_c_idx;
2632 create index on mcrparted1 (a, abs(b), c);
2633 create index on mcrparted2 (a, abs(b), c);
2634 create index on mcrparted3 (a, abs(b), c);
2635 create index on mcrparted4 (a, abs(b), c);
2636 explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c limit 1;
2637 QUERY PLAN
2638 -------------------------------------------------------------------------------
2639 Limit
2640 -> Append
2641 -> Sort
2642 Sort Key: mcrparted_1.a, (abs(mcrparted_1.b)), mcrparted_1.c
2643 -> Seq Scan on mcrparted0 mcrparted_1
2644 Filter: (a < 20)
2645 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2
2646 Index Cond: (a < 20)
2647 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3
2648 Index Cond: (a < 20)
2649 -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4
2650 Index Cond: (a < 20)
2651 (12 rows)
2653 set enable_bitmapscan = 0;
2654 -- Ensure Append node can be used when the partition is ordered by some
2655 -- pathkeys which were deemed redundant.
2656 explain (costs off) select * from mcrparted where a = 10 order by a, abs(b), c;
2657 QUERY PLAN
2658 -------------------------------------------------------------------------
2659 Append
2660 -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_1
2661 Index Cond: (a = 10)
2662 -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_2
2663 Index Cond: (a = 10)
2664 (5 rows)
2666 reset enable_bitmapscan;
2667 drop table mcrparted;
2668 -- Ensure LIST partitions allow an Append to be used instead of a MergeAppend
2669 create table bool_lp (b bool) partition by list(b);
2670 create table bool_lp_true partition of bool_lp for values in(true);
2671 create table bool_lp_false partition of bool_lp for values in(false);
2672 create index on bool_lp (b);
2673 explain (costs off) select * from bool_lp order by b;
2674 QUERY PLAN
2675 ----------------------------------------------------------------------------
2676 Append
2677 -> Index Only Scan using bool_lp_false_b_idx on bool_lp_false bool_lp_1
2678 -> Index Only Scan using bool_lp_true_b_idx on bool_lp_true bool_lp_2
2679 (3 rows)
2681 drop table bool_lp;
2682 -- Ensure const bool quals can be properly detected as redundant
2683 create table bool_rp (b bool, a int) partition by range(b,a);
2684 create table bool_rp_false_1k partition of bool_rp for values from (false,0) to (false,1000);
2685 create table bool_rp_true_1k partition of bool_rp for values from (true,0) to (true,1000);
2686 create table bool_rp_false_2k partition of bool_rp for values from (false,1000) to (false,2000);
2687 create table bool_rp_true_2k partition of bool_rp for values from (true,1000) to (true,2000);
2688 create index on bool_rp (b,a);
2689 explain (costs off) select * from bool_rp where b = true order by b,a;
2690 QUERY PLAN
2691 ----------------------------------------------------------------------------------
2692 Append
2693 -> Index Only Scan using bool_rp_true_1k_b_a_idx on bool_rp_true_1k bool_rp_1
2694 Index Cond: (b = true)
2695 -> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k bool_rp_2
2696 Index Cond: (b = true)
2697 (5 rows)
2699 explain (costs off) select * from bool_rp where b = false order by b,a;
2700 QUERY PLAN
2701 ------------------------------------------------------------------------------------
2702 Append
2703 -> Index Only Scan using bool_rp_false_1k_b_a_idx on bool_rp_false_1k bool_rp_1
2704 Index Cond: (b = false)
2705 -> Index Only Scan using bool_rp_false_2k_b_a_idx on bool_rp_false_2k bool_rp_2
2706 Index Cond: (b = false)
2707 (5 rows)
2709 explain (costs off) select * from bool_rp where b = true order by a;
2710 QUERY PLAN
2711 ----------------------------------------------------------------------------------
2712 Append
2713 -> Index Only Scan using bool_rp_true_1k_b_a_idx on bool_rp_true_1k bool_rp_1
2714 Index Cond: (b = true)
2715 -> Index Only Scan using bool_rp_true_2k_b_a_idx on bool_rp_true_2k bool_rp_2
2716 Index Cond: (b = true)
2717 (5 rows)
2719 explain (costs off) select * from bool_rp where b = false order by a;
2720 QUERY PLAN
2721 ------------------------------------------------------------------------------------
2722 Append
2723 -> Index Only Scan using bool_rp_false_1k_b_a_idx on bool_rp_false_1k bool_rp_1
2724 Index Cond: (b = false)
2725 -> Index Only Scan using bool_rp_false_2k_b_a_idx on bool_rp_false_2k bool_rp_2
2726 Index Cond: (b = false)
2727 (5 rows)
2729 drop table bool_rp;
2730 -- Ensure an Append scan is chosen when the partition order is a subset of
2731 -- the required order.
2732 create table range_parted (a int, b int, c int) partition by range(a, b);
2733 create table range_parted1 partition of range_parted for values from (0,0) to (10,10);
2734 create table range_parted2 partition of range_parted for values from (10,10) to (20,20);
2735 create index on range_parted (a,b,c);
2736 explain (costs off) select * from range_parted order by a,b,c;
2737 QUERY PLAN
2738 -------------------------------------------------------------------------------------
2739 Append
2740 -> Index Only Scan using range_parted1_a_b_c_idx on range_parted1 range_parted_1
2741 -> Index Only Scan using range_parted2_a_b_c_idx on range_parted2 range_parted_2
2742 (3 rows)
2744 explain (costs off) select * from range_parted order by a desc,b desc,c desc;
2745 QUERY PLAN
2746 ----------------------------------------------------------------------------------------------
2747 Append
2748 -> Index Only Scan Backward using range_parted2_a_b_c_idx on range_parted2 range_parted_2
2749 -> Index Only Scan Backward using range_parted1_a_b_c_idx on range_parted1 range_parted_1
2750 (3 rows)
2752 drop table range_parted;
2753 -- Check that we allow access to a child table's statistics when the user
2754 -- has permissions only for the parent table.
2755 create table permtest_parent (a int, b text, c text) partition by list (a);
2756 create table permtest_child (b text, c text, a int) partition by list (b);
2757 create table permtest_grandchild (c text, b text, a int);
2758 alter table permtest_child attach partition permtest_grandchild for values in ('a');
2759 alter table permtest_parent attach partition permtest_child for values in (1);
2760 create index on permtest_parent (left(c, 3));
2761 insert into permtest_parent
2762 select 1, 'a', left(fipshash(i::text), 5) from generate_series(0, 100) i;
2763 analyze permtest_parent;
2764 create role regress_no_child_access;
2765 revoke all on permtest_grandchild from regress_no_child_access;
2766 grant select on permtest_parent to regress_no_child_access;
2767 set session authorization regress_no_child_access;
2768 -- without stats access, these queries would produce hash join plans:
2769 explain (costs off)
2770 select * from permtest_parent p1 inner join permtest_parent p2
2771 on p1.a = p2.a and p1.c ~ 'a1$';
2772 QUERY PLAN
2773 ------------------------------------------
2774 Nested Loop
2775 Join Filter: (p1.a = p2.a)
2776 -> Seq Scan on permtest_grandchild p1
2777 Filter: (c ~ 'a1$'::text)
2778 -> Seq Scan on permtest_grandchild p2
2779 (5 rows)
2781 explain (costs off)
2782 select * from permtest_parent p1 inner join permtest_parent p2
2783 on p1.a = p2.a and left(p1.c, 3) ~ 'a1$';
2784 QUERY PLAN
2785 ----------------------------------------------
2786 Nested Loop
2787 Join Filter: (p1.a = p2.a)
2788 -> Seq Scan on permtest_grandchild p1
2789 Filter: ("left"(c, 3) ~ 'a1$'::text)
2790 -> Seq Scan on permtest_grandchild p2
2791 (5 rows)
2793 reset session authorization;
2794 revoke all on permtest_parent from regress_no_child_access;
2795 grant select(a,c) on permtest_parent to regress_no_child_access;
2796 set session authorization regress_no_child_access;
2797 explain (costs off)
2798 select p2.a, p1.c from permtest_parent p1 inner join permtest_parent p2
2799 on p1.a = p2.a and p1.c ~ 'a1$';
2800 QUERY PLAN
2801 ------------------------------------------
2802 Nested Loop
2803 Join Filter: (p1.a = p2.a)
2804 -> Seq Scan on permtest_grandchild p1
2805 Filter: (c ~ 'a1$'::text)
2806 -> Seq Scan on permtest_grandchild p2
2807 (5 rows)
2809 -- we will not have access to the expression index's stats here:
2810 explain (costs off)
2811 select p2.a, p1.c from permtest_parent p1 inner join permtest_parent p2
2812 on p1.a = p2.a and left(p1.c, 3) ~ 'a1$';
2813 QUERY PLAN
2814 ----------------------------------------------------
2815 Hash Join
2816 Hash Cond: (p2.a = p1.a)
2817 -> Seq Scan on permtest_grandchild p2
2818 -> Hash
2819 -> Seq Scan on permtest_grandchild p1
2820 Filter: ("left"(c, 3) ~ 'a1$'::text)
2821 (6 rows)
2823 reset session authorization;
2824 revoke all on permtest_parent from regress_no_child_access;
2825 drop role regress_no_child_access;
2826 drop table permtest_parent;
2827 -- Verify that constraint errors across partition root / child are
2828 -- handled correctly (Bug #16293)
2829 CREATE TABLE errtst_parent (
2830 partid int not null,
2831 shdata int not null,
2832 data int NOT NULL DEFAULT 0,
2833 CONSTRAINT shdata_small CHECK(shdata < 3)
2834 ) PARTITION BY RANGE (partid);
2835 -- fast defaults lead to attribute mapping being used in one
2836 -- direction, but not the other
2837 CREATE TABLE errtst_child_fastdef (
2838 partid int not null,
2839 shdata int not null,
2840 CONSTRAINT shdata_small CHECK(shdata < 3)
2841 );
2842 -- no remapping in either direction necessary
2843 CREATE TABLE errtst_child_plaindef (
2844 partid int not null,
2845 shdata int not null,
2846 data int NOT NULL DEFAULT 0,
2847 CONSTRAINT shdata_small CHECK(shdata < 3),
2848 CHECK(data < 10)
2849 );
2850 -- remapping in both direction
2851 CREATE TABLE errtst_child_reorder (
2852 data int NOT NULL DEFAULT 0,
2853 shdata int not null,
2854 partid int not null,
2855 CONSTRAINT shdata_small CHECK(shdata < 3),
2856 CHECK(data < 10)
2857 );
2858 ALTER TABLE errtst_child_fastdef ADD COLUMN data int NOT NULL DEFAULT 0;
2859 ALTER TABLE errtst_child_fastdef ADD CONSTRAINT errtest_child_fastdef_data_check CHECK (data < 10);
2860 ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_fastdef FOR VALUES FROM (0) TO (10);
2861 ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_plaindef FOR VALUES FROM (10) TO (20);
2862 ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_reorder FOR VALUES FROM (20) TO (30);
2863 -- insert without child check constraint error
2864 INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '5');
2865 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '5');
2866 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '5');
2867 -- insert with child check constraint error
2868 INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '10');
2869 ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
2870 DETAIL: Failing row contains (0, 1, 10).
2871 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '10');
2872 ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
2873 DETAIL: Failing row contains (10, 1, 10).
2874 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '10');
2875 ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
2876 DETAIL: Failing row contains (20, 1, 10).
2877 -- insert with child not null constraint error
2878 INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', NULL);
2879 ERROR: null value in column "data" of relation "errtst_child_fastdef" violates not-null constraint
2880 DETAIL: Failing row contains (0, 1, null).
2881 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', NULL);
2882 ERROR: null value in column "data" of relation "errtst_child_plaindef" violates not-null constraint
2883 DETAIL: Failing row contains (10, 1, null).
2884 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', NULL);
2885 ERROR: null value in column "data" of relation "errtst_child_reorder" violates not-null constraint
2886 DETAIL: Failing row contains (20, 1, null).
2887 -- insert with shared check constraint error
2888 INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '5', '5');
2889 ERROR: new row for relation "errtst_child_fastdef" violates check constraint "shdata_small"
2890 DETAIL: Failing row contains (0, 5, 5).
2891 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '5', '5');
2892 ERROR: new row for relation "errtst_child_plaindef" violates check constraint "shdata_small"
2893 DETAIL: Failing row contains (10, 5, 5).
2894 INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '5', '5');
2895 ERROR: new row for relation "errtst_child_reorder" violates check constraint "shdata_small"
2896 DETAIL: Failing row contains (20, 5, 5).
2897 -- within partition update without child check constraint violation
2898 BEGIN;
2899 UPDATE errtst_parent SET data = data + 1 WHERE partid = 0;
2900 UPDATE errtst_parent SET data = data + 1 WHERE partid = 10;
2901 UPDATE errtst_parent SET data = data + 1 WHERE partid = 20;
2902 ROLLBACK;
2903 -- within partition update with child check constraint violation
2904 UPDATE errtst_parent SET data = data + 10 WHERE partid = 0;
2905 ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
2906 DETAIL: Failing row contains (0, 1, 15).
2907 UPDATE errtst_parent SET data = data + 10 WHERE partid = 10;
2908 ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
2909 DETAIL: Failing row contains (10, 1, 15).
2910 UPDATE errtst_parent SET data = data + 10 WHERE partid = 20;
2911 ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
2912 DETAIL: Failing row contains (20, 1, 15).
2913 -- direct leaf partition update, without partition id violation
2914 BEGIN;
2915 UPDATE errtst_child_fastdef SET partid = 1 WHERE partid = 0;
2916 UPDATE errtst_child_plaindef SET partid = 11 WHERE partid = 10;
2917 UPDATE errtst_child_reorder SET partid = 21 WHERE partid = 20;
2918 ROLLBACK;
2919 -- direct leaf partition update, with partition id violation
2920 UPDATE errtst_child_fastdef SET partid = partid + 10 WHERE partid = 0;
2921 ERROR: new row for relation "errtst_child_fastdef" violates partition constraint
2922 DETAIL: Failing row contains (10, 1, 5).
2923 UPDATE errtst_child_plaindef SET partid = partid + 10 WHERE partid = 10;
2924 ERROR: new row for relation "errtst_child_plaindef" violates partition constraint
2925 DETAIL: Failing row contains (20, 1, 5).
2926 UPDATE errtst_child_reorder SET partid = partid + 10 WHERE partid = 20;
2927 ERROR: new row for relation "errtst_child_reorder" violates partition constraint
2928 DETAIL: Failing row contains (5, 1, 30).
2929 -- partition move, without child check constraint violation
2930 BEGIN;
2931 UPDATE errtst_parent SET partid = 10, data = data + 1 WHERE partid = 0;
2932 UPDATE errtst_parent SET partid = 20, data = data + 1 WHERE partid = 10;
2933 UPDATE errtst_parent SET partid = 0, data = data + 1 WHERE partid = 20;
2934 ROLLBACK;
2935 -- partition move, with child check constraint violation
2936 UPDATE errtst_parent SET partid = 10, data = data + 10 WHERE partid = 0;
2937 ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check"
2938 DETAIL: Failing row contains (10, 1, 15).
2939 UPDATE errtst_parent SET partid = 20, data = data + 10 WHERE partid = 10;
2940 ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check"
2941 DETAIL: Failing row contains (20, 1, 15).
2942 UPDATE errtst_parent SET partid = 0, data = data + 10 WHERE partid = 20;
2943 ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check"
2944 DETAIL: Failing row contains (0, 1, 15).
2945 -- partition move, without target partition
2946 UPDATE errtst_parent SET partid = 30, data = data + 10 WHERE partid = 20;
2947 ERROR: no partition of relation "errtst_parent" found for row
2948 DETAIL: Partition key of the failing row contains (partid) = (30).
2949 DROP TABLE errtst_parent;