1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Views and the Rule System</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.9 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="The Rule System"
HREF="rules.html"><LINK
REL="PREVIOUS"
TITLE="The Query Tree"
HREF="querytree.html"><LINK
REL="NEXT"
TITLE="Rules on INSERT, UPDATE, and DELETE"
HREF="rules-update.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2013-04-01T18:35:08"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.9 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="The Query Tree"
HREF="querytree.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="rules.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 37. The Rule System</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Rules on INSERT, UPDATE, and DELETE"
HREF="rules-update.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="RULES-VIEWS"
>37.2. Views and the Rule System</A
></H1
><P
> Views in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> are implemented
using the rule system. In fact, there is essentially no difference
between:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE VIEW myview AS SELECT * FROM mytab;</PRE
><P>
compared against the two commands:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE myview (<TT
CLASS="REPLACEABLE"
><I
>same column list as mytab</I
></TT
>);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
SELECT * FROM mytab;</PRE
><P>
because this is exactly what the <TT
CLASS="COMMAND"
>CREATE VIEW</TT
>
command does internally. This has some side effects. One of them
is that the information about a view in the
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> system catalogs is exactly
the same as it is for a table. So for the parser, there is
absolutely no difference between a table and a view. They are the
same thing: relations.</P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RULES-SELECT"
>37.2.1. How <TT
CLASS="COMMAND"
>SELECT</TT
> Rules Work</A
></H2
><P
> Rules <TT
CLASS="LITERAL"
>ON SELECT</TT
> are applied to all queries as the last step, even
if the command given is an <TT
CLASS="COMMAND"
>INSERT</TT
>,
<TT
CLASS="COMMAND"
>UPDATE</TT
> or <TT
CLASS="COMMAND"
>DELETE</TT
>. And they
have different semantics from rules on the other command types in that they modify the
query tree in place instead of creating a new one. So
<TT
CLASS="COMMAND"
>SELECT</TT
> rules are described first.</P
><P
> Currently, there can be only one action in an <TT
CLASS="LITERAL"
>ON SELECT</TT
> rule, and it must
be an unconditional <TT
CLASS="COMMAND"
>SELECT</TT
> action that is <TT
CLASS="LITERAL"
>INSTEAD</TT
>. This restriction was
required to make rules safe enough to open them for ordinary users, and
it restricts <TT
CLASS="LITERAL"
>ON SELECT</TT
> rules to act like views.</P
><P
> The examples for this chapter are two join views that do some
calculations and some more views using them in turn. One of the
two first views is customized later by adding rules for
<TT
CLASS="COMMAND"
>INSERT</TT
>, <TT
CLASS="COMMAND"
>UPDATE</TT
>, and
<TT
CLASS="COMMAND"
>DELETE</TT
> operations so that the final result will
be a view that behaves like a real table with some magic
functionality. This is not such a simple example to start from and
this makes things harder to get into. But it's better to have one
example that covers all the points discussed step by step rather
than having many different ones that might mix up in mind.</P
><P
>For the example, we need a little <TT
CLASS="LITERAL"
>min</TT
> function that
returns the lower of 2 integer values. We create that as:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE FUNCTION min(integer, integer) RETURNS integer AS $$
SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END
$$ LANGUAGE SQL STRICT;</PRE
><P></P
><P
> The real tables we need in the first two rule system descriptions
are these:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE shoe_data (
shoename text, -- primary key
sh_avail integer, -- available number of pairs
slcolor text, -- preferred shoelace color
slminlen real, -- minimum shoelace length
slmaxlen real, -- maximum shoelace length
slunit text -- length unit
);
CREATE TABLE shoelace_data (
sl_name text, -- primary key
sl_avail integer, -- available number of pairs
sl_color text, -- shoelace color
sl_len real, -- shoelace length
sl_unit text -- length unit
);
CREATE TABLE unit (
un_name text, -- primary key
un_fact real -- factor to transform to cm
);</PRE
><P>
As you can see, they represent shoe-store data.</P
><P
> The views are created as:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE VIEW shoe AS
SELECT sh.shoename,
sh.sh_avail,
sh.slcolor,
sh.slminlen,
sh.slminlen * un.un_fact AS slminlen_cm,
sh.slmaxlen,
sh.slmaxlen * un.un_fact AS slmaxlen_cm,
sh.slunit
FROM shoe_data sh, unit un
WHERE sh.slunit = un.un_name;
CREATE VIEW shoelace AS
SELECT s.sl_name,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name;
CREATE VIEW shoe_ready AS
SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_name,
rsl.sl_avail,
min(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM shoe rsh, shoelace rsl
WHERE rsl.sl_color = rsh.slcolor
AND rsl.sl_len_cm >= rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm;</PRE
><P>
The <TT
CLASS="COMMAND"
>CREATE VIEW</TT
> command for the
<TT
CLASS="LITERAL"
>shoelace</TT
> view (which is the simplest one we
have) will create a relation <TT
CLASS="LITERAL"
>shoelace</TT
> and an entry in
<TT
CLASS="STRUCTNAME"
>pg_rewrite</TT
> that tells that there is a
rewrite rule that must be applied whenever the relation <TT
CLASS="LITERAL"
>shoelace</TT
>
is referenced in a query's range table. The rule has no rule
qualification (discussed later, with the non-<TT
CLASS="COMMAND"
>SELECT</TT
> rules, since
<TT
CLASS="COMMAND"
>SELECT</TT
> rules currently cannot have them) and it is <TT
CLASS="LITERAL"
>INSTEAD</TT
>. Note
that rule qualifications are not the same as query qualifications.
The action of our rule has a query qualification.
The action of the rule is one query tree that is a copy of the
<TT
CLASS="COMMAND"
>SELECT</TT
> statement in the view creation command.</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> The two extra range
table entries for <TT
CLASS="LITERAL"
>NEW</TT
> and <TT
CLASS="LITERAL"
>OLD</TT
> that you can see in
the <TT
CLASS="STRUCTNAME"
>pg_rewrite</TT
> entry aren't of interest
for <TT
CLASS="COMMAND"
>SELECT</TT
> rules.
</P
></BLOCKQUOTE
></DIV
><P
> Now we populate <TT
CLASS="LITERAL"
>unit</TT
>, <TT
CLASS="LITERAL"
>shoe_data</TT
>
and <TT
CLASS="LITERAL"
>shoelace_data</TT
> and run a simple query on a view:
</P><PRE
CLASS="PROGRAMLISTING"
>INSERT INTO unit VALUES ('cm', 1.0);
INSERT INTO unit VALUES ('m', 100.0);
INSERT INTO unit VALUES ('inch', 2.54);
INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm');
INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch');
INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch');
INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm');
INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm');
INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm');
INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch');
SELECT * FROM shoelace;
sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
-----------+----------+----------+--------+---------+-----------
sl1 | 5 | black | 80 | cm | 80
sl2 | 6 | black | 100 | cm | 100
sl7 | 7 | brown | 60 | cm | 60
sl3 | 0 | black | 35 | inch | 88.9
sl4 | 8 | black | 40 | inch | 101.6
sl8 | 1 | brown | 40 | inch | 101.6
sl5 | 4 | brown | 1 | m | 100
sl6 | 0 | brown | 0.9 | m | 90
(8 rows)</PRE
><P>
</P
><P
> This is the simplest <TT
CLASS="COMMAND"
>SELECT</TT
> you can do on our
views, so we take this opportunity to explain the basics of view
rules. The <TT
CLASS="LITERAL"
>SELECT * FROM shoelace</TT
> was
interpreted by the parser and produced the query tree:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len,
shoelace.sl_unit, shoelace.sl_len_cm
FROM shoelace shoelace;</PRE
><P>
and this is given to the rule system. The rule system walks through the
range table and checks if there are rules
for any relation. When processing the range table entry for
<TT
CLASS="LITERAL"
>shoelace</TT
> (the only one up to now) it finds the
<TT
CLASS="LITERAL"
>_RETURN</TT
> rule with the query tree:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT s.sl_name, s.sl_avail,
s.sl_color, s.sl_len, s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace old, shoelace new,
shoelace_data s, unit u
WHERE s.sl_unit = u.un_name;</PRE
><P></P
><P
> To expand the view, the rewriter simply creates a subquery range-table
entry containing the rule's action query tree, and substitutes this
range table entry for the original one that referenced the view. The
resulting rewritten query tree is almost the same as if you had typed:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len,
shoelace.sl_unit, shoelace.sl_len_cm
FROM (SELECT s.sl_name,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name) shoelace;</PRE
><P>
There is one difference however: the subquery's range table has two
extra entries <TT
CLASS="LITERAL"
>shoelace old</TT
> and <TT
CLASS="LITERAL"
>shoelace new</TT
>. These entries don't
participate directly in the query, since they aren't referenced by
the subquery's join tree or target list. The rewriter uses them
to store the access privilege check information that was originally present
in the range-table entry that referenced the view. In this way, the
executor will still check that the user has proper privileges to access
the view, even though there's no direct use of the view in the rewritten
query.</P
><P
> That was the first rule applied. The rule system will continue checking
the remaining range-table entries in the top query (in this example there
are no more), and it will recursively check the range-table entries in
the added subquery to see if any of them reference views. (But it
won't expand <TT
CLASS="LITERAL"
>old</TT
> or <TT
CLASS="LITERAL"
>new</TT
> — otherwise we'd have infinite recursion!)
In this example, there are no rewrite rules for <TT
CLASS="LITERAL"
>shoelace_data</TT
> or <TT
CLASS="LITERAL"
>unit</TT
>,
so rewriting is complete and the above is the final result given to
the planner.</P
><P
> Now we want to write a query that finds out for which shoes currently in the store
we have the matching shoelaces (color and length) and where the
total number of exactly matching pairs is greater or equal to two.
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT * FROM shoe_ready WHERE total_avail >= 2;
shoename | sh_avail | sl_name | sl_avail | total_avail
----------+----------+---------+----------+-------------
sh1 | 2 | sl1 | 5 | 2
sh3 | 4 | sl7 | 7 | 4
(2 rows)</PRE
><P></P
><P
> The output of the parser this time is the query tree:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT shoe_ready.shoename, shoe_ready.sh_avail,
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM shoe_ready shoe_ready
WHERE shoe_ready.total_avail >= 2;</PRE
><P>
The first rule applied will be the one for the
<TT
CLASS="LITERAL"
>shoe_ready</TT
> view and it results in the
query tree:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT shoe_ready.shoename, shoe_ready.sh_avail,
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM (SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_name,
rsl.sl_avail,
min(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM shoe rsh, shoelace rsl
WHERE rsl.sl_color = rsh.slcolor
AND rsl.sl_len_cm >= rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
WHERE shoe_ready.total_avail >= 2;</PRE
><P>
Similarly, the rules for <TT
CLASS="LITERAL"
>shoe</TT
> and
<TT
CLASS="LITERAL"
>shoelace</TT
> are substituted into the range table of
the subquery, leading to a three-level final query tree:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT shoe_ready.shoename, shoe_ready.sh_avail,
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM (SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_name,
rsl.sl_avail,
min(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM (SELECT sh.shoename,
sh.sh_avail,
sh.slcolor,
sh.slminlen,
sh.slminlen * un.un_fact AS slminlen_cm,
sh.slmaxlen,
sh.slmaxlen * un.un_fact AS slmaxlen_cm,
sh.slunit
FROM shoe_data sh, unit un
WHERE sh.slunit = un.un_name) rsh,
(SELECT s.sl_name,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name) rsl
WHERE rsl.sl_color = rsh.slcolor
AND rsl.sl_len_cm >= rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
WHERE shoe_ready.total_avail > 2;</PRE
><P>
</P
><P
> It turns out that the planner will collapse this tree into a
two-level query tree: the bottommost <TT
CLASS="COMMAND"
>SELECT</TT
>
commands will be <SPAN
CLASS="QUOTE"
>"pulled up"</SPAN
> into the middle
<TT
CLASS="COMMAND"
>SELECT</TT
> since there's no need to process them
separately. But the middle <TT
CLASS="COMMAND"
>SELECT</TT
> will remain
separate from the top, because it contains aggregate functions.
If we pulled those up it would change the behavior of the topmost
<TT
CLASS="COMMAND"
>SELECT</TT
>, which we don't want. However,
collapsing the query tree is an optimization that the rewrite
system doesn't have to concern itself with.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN52852"
>37.2.2. View Rules in Non-<TT
CLASS="COMMAND"
>SELECT</TT
> Statements</A
></H2
><P
> Two details of the query tree aren't touched in the description of
view rules above. These are the command type and the result relation.
In fact, the command type is not needed by view rules, but the result
relation may affect the way in which the query rewriter works, because
special care needs to be taken if the result relation is a view.</P
><P
> There are only a few differences between a query tree for a
<TT
CLASS="COMMAND"
>SELECT</TT
> and one for any other
command. Obviously, they have a different command type and for a
command other than a <TT
CLASS="COMMAND"
>SELECT</TT
>, the result
relation points to the range-table entry where the result should
go. Everything else is absolutely the same. So having two tables
<TT
CLASS="LITERAL"
>t1</TT
> and <TT
CLASS="LITERAL"
>t2</TT
> with columns <TT
CLASS="LITERAL"
>a</TT
> and
<TT
CLASS="LITERAL"
>b</TT
>, the query trees for the two statements:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a;
UPDATE t1 SET b = t2.b FROM t2 WHERE t1.a = t2.a;</PRE
><P>
are nearly identical. In particular:
<P
></P
></P><UL
><LI
><P
> The range tables contain entries for the tables <TT
CLASS="LITERAL"
>t1</TT
> and <TT
CLASS="LITERAL"
>t2</TT
>.
</P
></LI
><LI
><P
> The target lists contain one variable that points to column
<TT
CLASS="LITERAL"
>b</TT
> of the range table entry for table <TT
CLASS="LITERAL"
>t2</TT
>.
</P
></LI
><LI
><P
> The qualification expressions compare the columns <TT
CLASS="LITERAL"
>a</TT
> of both
range-table entries for equality.
</P
></LI
><LI
><P
> The join trees show a simple join between <TT
CLASS="LITERAL"
>t1</TT
> and <TT
CLASS="LITERAL"
>t2</TT
>.
</P
></LI
></UL
><P>
</P
><P
> The consequence is, that both query trees result in similar
execution plans: They are both joins over the two tables. For the
<TT
CLASS="COMMAND"
>UPDATE</TT
> the missing columns from <TT
CLASS="LITERAL"
>t1</TT
> are added to
the target list by the planner and the final query tree will read
as:
</P><PRE
CLASS="PROGRAMLISTING"
>UPDATE t1 SET a = t1.a, b = t2.b FROM t2 WHERE t1.a = t2.a;</PRE
><P>
and thus the executor run over the join will produce exactly the
same result set as:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;</PRE
><P>
But there is a little problem in
<TT
CLASS="COMMAND"
>UPDATE</TT
>: the part of the executor plan that does
the join does not care what the results from the join are
meant for. It just produces a result set of rows. The fact that
one is a <TT
CLASS="COMMAND"
>SELECT</TT
> command and the other is an
<TT
CLASS="COMMAND"
>UPDATE</TT
> is handled higher up in the executor, where
it knows that this is an <TT
CLASS="COMMAND"
>UPDATE</TT
>, and it knows that
this result should go into table <TT
CLASS="LITERAL"
>t1</TT
>. But which of the rows
that are there has to be replaced by the new row?</P
><P
> To resolve this problem, another entry is added to the target list
in <TT
CLASS="COMMAND"
>UPDATE</TT
> (and also in
<TT
CLASS="COMMAND"
>DELETE</TT
>) statements: the current tuple ID
(<ACRONYM
CLASS="ACRONYM"
>CTID</ACRONYM
>).
This is a system column containing the
file block number and position in the block for the row. Knowing
the table, the <ACRONYM
CLASS="ACRONYM"
>CTID</ACRONYM
> can be used to retrieve the
original row of <TT
CLASS="LITERAL"
>t1</TT
> to be updated. After adding the
<ACRONYM
CLASS="ACRONYM"
>CTID</ACRONYM
> to the target list, the query actually looks like:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;</PRE
><P>
Now another detail of <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> enters
the stage. Old table rows aren't overwritten, and this
is why <TT
CLASS="COMMAND"
>ROLLBACK</TT
> is fast. In an <TT
CLASS="COMMAND"
>UPDATE</TT
>,
the new result row is inserted into the table (after stripping the
<ACRONYM
CLASS="ACRONYM"
>CTID</ACRONYM
>) and in the row header of the old row, which the
<ACRONYM
CLASS="ACRONYM"
>CTID</ACRONYM
> pointed to, the <TT
CLASS="LITERAL"
>cmax</TT
> and
<TT
CLASS="LITERAL"
>xmax</TT
> entries are set to the current command counter
and current transaction ID. Thus the old row is hidden, and after
the transaction commits the vacuum cleaner can eventually remove
the dead row.</P
><P
> Knowing all that, we can simply apply view rules in absolutely
the same way to any command. There is no difference.</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN52908"
>37.2.3. The Power of Views in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
></A
></H2
><P
> The above demonstrates how the rule system incorporates view
definitions into the original query tree. In the second example, a
simple <TT
CLASS="COMMAND"
>SELECT</TT
> from one view created a final
query tree that is a join of 4 tables (<TT
CLASS="LITERAL"
>unit</TT
> was used twice with
different names).</P
><P
> The benefit of implementing views with the rule system is,
that the planner has all
the information about which tables have to be scanned plus the
relationships between these tables plus the restrictive
qualifications from the views plus the qualifications from
the original query
in one single query tree. And this is still the situation
when the original query is already a join over views.
The planner has to decide which is
the best path to execute the query, and the more information
the planner has, the better this decision can be. And
the rule system as implemented in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
ensures, that this is all information available about the query
up to that point.</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RULES-VIEWS-UPDATE"
>37.2.4. Updating a View</A
></H2
><P
> What happens if a view is named as the target relation for an
<TT
CLASS="COMMAND"
>INSERT</TT
>, <TT
CLASS="COMMAND"
>UPDATE</TT
>, or
<TT
CLASS="COMMAND"
>DELETE</TT
>? Simply doing the substitutions
described above would give a query tree in which the result
relation points at a subquery range-table entry, which will not
work. Instead, the rewriter assumes that the operation will be
handled by an <TT
CLASS="LITERAL"
>INSTEAD OF</TT
> trigger on the view.
(If there is no such trigger, the executor will throw an error
when execution starts.) Rewriting works slightly differently
in this case. For <TT
CLASS="COMMAND"
>INSERT</TT
>, the rewriter does
nothing at all with the view, leaving it as the result relation
for the query. For <TT
CLASS="COMMAND"
>UPDATE</TT
> and
<TT
CLASS="COMMAND"
>DELETE</TT
>, it's still necessary to expand the
view query to produce the <SPAN
CLASS="QUOTE"
>"old"</SPAN
> rows that the command will
attempt to update or delete. So the view is expanded as normal,
but another unexpanded range-table entry is added to the query
to represent the view in its capacity as the result relation.</P
><P
> The problem that now arises is how to identify the rows to be
updated in the view. Recall that when the result relation
is a table, a special <ACRONYM
CLASS="ACRONYM"
>CTID</ACRONYM
> entry is added to the target
list to identify the physical locations of the rows to be updated.
This does not work if the result relation is a view, because a view
does not have any <ACRONYM
CLASS="ACRONYM"
>CTID</ACRONYM
>, since its rows do not have
actual physical locations. Instead, for an <TT
CLASS="COMMAND"
>UPDATE</TT
>
or <TT
CLASS="COMMAND"
>DELETE</TT
> operation, a special <TT
CLASS="LITERAL"
>wholerow</TT
>
entry is added to the target list, which expands to include all
columns from the view. The executor uses this value to supply the
<SPAN
CLASS="QUOTE"
>"old"</SPAN
> row to the <TT
CLASS="LITERAL"
>INSTEAD OF</TT
> trigger. It is
up to the trigger to work out what to update based on the old and
new row values.</P
><P
> If there are no <TT
CLASS="LITERAL"
>INSTEAD OF</TT
> triggers to update the view,
the executor will throw an error, because it cannot automatically
update a view by itself. To change this, we can define rules that
modify the behavior of <TT
CLASS="COMMAND"
>INSERT</TT
>,
<TT
CLASS="COMMAND"
>UPDATE</TT
>, and <TT
CLASS="COMMAND"
>DELETE</TT
> commands on
a view. These rules will rewrite the command, typically into a command
that updates one or more tables, rather than views. That is the topic
of the next section.</P
><P
> Note that rules are evaluated first, rewriting the original query
before it is planned and executed. Therefore, if a view has
<TT
CLASS="LITERAL"
>INSTEAD OF</TT
> triggers as well as rules on <TT
CLASS="COMMAND"
>INSERT</TT
>,
<TT
CLASS="COMMAND"
>UPDATE</TT
>, or <TT
CLASS="COMMAND"
>DELETE</TT
>, then the rules will be
evaluated first, and depending on the result, the triggers may not be
used at all.</P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="querytree.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="rules-update.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>The Query Tree</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="rules.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Rules on <TT
CLASS="COMMAND"
>INSERT</TT
>, <TT
CLASS="COMMAND"
>UPDATE</TT
>, and <TT
CLASS="COMMAND"
>DELETE</TT
></TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
|