🌐 AI搜索 & 代理 主页
Skip to content

Commit a99c42f

Browse files
tglsfdctglsfdc
committed
Support automatically-updatable views.
This patch makes "simple" views automatically updatable, without the need to create either INSTEAD OF triggers or INSTEAD rules. "Simple" views are those classified as updatable according to SQL-92 rules. The rewriter transforms INSERT/UPDATE/DELETE commands on such views directly into an equivalent command on the underlying table, which will generally have noticeably better performance than is possible with either triggers or user-written rules. A view that has INSTEAD OF triggers or INSTEAD rules continues to operate the same as before. For the moment, security_barrier views are not considered simple. Also, we do not support WITH CHECK OPTION. These features may be added in future. Dean Rasheed, reviewed by Amit Kapila
1 parent d12d9f5 commit a99c42f

File tree

20 files changed

+2491
-93
lines changed

20 files changed

+2491
-93
lines changed

‎doc/src/sgml/intro.sgml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -110,7 +110,7 @@
110110
<simpara>triggers</simpara>
111111
</listitem>
112112
<listitem>
113-
<simpara>views</simpara>
113+
<simpara>updatable views</simpara>
114114
</listitem>
115115
<listitem>
116116
<simpara>transactional integrity</simpara>

‎doc/src/sgml/ref/alter_table.sgml‎

Lines changed: 3 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -147,11 +147,9 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
147147
<listitem>
148148
<para>
149149
These forms set or remove the default value for a column.
150-
The default values only apply to subsequent <command>INSERT</command>
151-
commands; they do not cause rows already in the table to change.
152-
Defaults can also be created for views, in which case they are
153-
inserted into <command>INSERT</> statements on the view before
154-
the view's <literal>ON INSERT</literal> rule is applied.
150+
Default values only apply in subsequent <command>INSERT</command>
151+
or <command>UPDATE</> commands; they do not cause rows already in the
152+
table to change.
155153
</para>
156154
</listitem>
157155
</varlistentry>

‎doc/src/sgml/ref/alter_view.sgml‎

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -80,10 +80,11 @@ ALTER VIEW [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RESET
8080
<listitem>
8181
<para>
8282
These forms set or remove the default value for a column.
83-
A default value associated with a view column is
84-
inserted into <command>INSERT</> statements on the view before
85-
the view's <literal>ON INSERT</literal> rule is applied, if
86-
the <command>INSERT</> does not specify a value for the column.
83+
A view column's default value is substituted into any
84+
<command>INSERT</> or <command>UPDATE</> command whose target is the
85+
view, before applying any rules or triggers for the view. The view's
86+
default will therefore take precedence over any default values from
87+
underlying relations.
8788
</para>
8889
</listitem>
8990
</varlistentry>

‎doc/src/sgml/ref/create_rule.sgml‎

Lines changed: 19 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -45,10 +45,10 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS
4545
additional commands to be executed when a given command on a given
4646
table is executed. Alternatively, an <literal>INSTEAD</literal>
4747
rule can replace a given command by another, or cause a command
48-
not to be executed at all. Rules are used to implement table
48+
not to be executed at all. Rules are used to implement SQL
4949
views as well. It is important to realize that a rule is really
5050
a command transformation mechanism, or command macro. The
51-
transformation happens before the execution of the commands starts.
51+
transformation happens before the execution of the command starts.
5252
If you actually want an operation that fires independently for each
5353
physical row, you probably want to use a trigger, not a rule.
5454
More information about the rules system is in <xref linkend="rules">.
@@ -73,13 +73,11 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS
7373
sufficient for your purposes) to replace update actions on the view
7474
with appropriate updates on other tables. If you want to support
7575
<command>INSERT RETURNING</> and so on, then be sure to put a suitable
76-
<literal>RETURNING</> clause into each of these rules. Alternatively,
77-
an updatable view can be implemented using <literal>INSTEAD OF</>
78-
triggers (see <xref linkend="sql-createtrigger">).
76+
<literal>RETURNING</> clause into each of these rules.
7977
</para>
8078

8179
<para>
82-
There is a catch if you try to use conditional rules for view
80+
There is a catch if you try to use conditional rules for complex view
8381
updates: there <emphasis>must</> be an unconditional
8482
<literal>INSTEAD</literal> rule for each action you wish to allow
8583
on the view. If the rule is conditional, or is not
@@ -95,6 +93,21 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS
9593
<literal>INSTEAD NOTHING</literal> action. (This method does not
9694
currently work to support <literal>RETURNING</> queries, however.)
9795
</para>
96+
97+
<note>
98+
<para>
99+
A view that is simple enough to be automatically updatable (see <xref
100+
linkend="sql-createview">) does not require a user-created rule in
101+
order to be updatable. While you can create an explicit rule anyway,
102+
the automatic update transformation will generally outperform an
103+
explicit rule.
104+
</para>
105+
106+
<para>
107+
Another alternative worth considering is to use <literal>INSTEAD OF</>
108+
triggers (see <xref linkend="sql-createtrigger">) in place of rules.
109+
</para>
110+
</note>
98111
</refsect1>
99112

100113
<refsect1>

‎doc/src/sgml/ref/create_view.sgml‎

Lines changed: 109 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -127,17 +127,6 @@ CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW <replaceable class="PARAMETER">n
127127
<refsect1>
128128
<title>Notes</title>
129129

130-
<para>
131-
Currently, views are read only: the system will not allow an insert,
132-
update, or delete on a view. You can get the effect of an updatable
133-
view by creating <literal>INSTEAD</> triggers on the view, which
134-
must convert attempted inserts, etc. on the view into
135-
appropriate actions on other tables. For more information see
136-
<xref linkend="sql-createtrigger">. Another possibility is to create
137-
rules (see <xref linkend="sql-createrule">), but in practice triggers
138-
are easier to understand and use correctly.
139-
</para>
140-
141130
<para>
142131
Use the <xref linkend="sql-dropview">
143132
statement to drop views.
@@ -175,6 +164,105 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
175164
to replace it (this includes being a member of the owning role).
176165
</para>
177166

167+
<refsect2 id="SQL-CREATEVIEW-updatable-views">
168+
<title id="SQL-CREATEVIEW-updatable-views-title">Updatable Views</title>
169+
170+
<indexterm zone="sql-createview-updatable-views">
171+
<primary>updatable views</primary>
172+
</indexterm>
173+
174+
<para>
175+
Simple views are automatically updatable: the system will allow
176+
<command>INSERT</>, <command>UPDATE</> and <command>DELETE</> statements
177+
to be used on the view in the same way as on a regular table. A view is
178+
automatically updatable if it satisfies all of the following conditions:
179+
180+
<itemizedlist>
181+
<listitem>
182+
<para>
183+
The view must have exactly one entry in its <literal>FROM</> list,
184+
which must be a table or another updatable view.
185+
</para>
186+
</listitem>
187+
188+
<listitem>
189+
<para>
190+
The view definition must not contain <literal>WITH</>,
191+
<literal>DISTINCT</>, <literal>GROUP BY</>, <literal>HAVING</>,
192+
<literal>LIMIT</>, or <literal>OFFSET</> clauses at the top level.
193+
</para>
194+
</listitem>
195+
196+
<listitem>
197+
<para>
198+
The view definition must not contain set operations (<literal>UNION</>,
199+
<literal>INTERSECT</> or <literal>EXCEPT</>) at the top level.
200+
</para>
201+
</listitem>
202+
203+
<listitem>
204+
<para>
205+
All columns in the view's select list must be simple references to
206+
columns of the underlying relation. They cannot be expressions,
207+
literals or functions. System columns cannot be referenced, either.
208+
</para>
209+
</listitem>
210+
211+
<listitem>
212+
<para>
213+
No column of the underlying relation can appear more than once in
214+
the view's select list.
215+
</para>
216+
</listitem>
217+
218+
<listitem>
219+
<para>
220+
The view must not have the <literal>security_barrier</> property.
221+
</para>
222+
</listitem>
223+
</itemizedlist>
224+
</para>
225+
226+
<para>
227+
If the view is automatically updatable the system will convert any
228+
<command>INSERT</>, <command>UPDATE</> or <command>DELETE</> statement
229+
on the view into the corresponding statement on the underlying base
230+
relation.
231+
</para>
232+
233+
<para>
234+
If an automatically updatable view contains a <literal>WHERE</>
235+
condition, the condition restricts which rows of the base relation are
236+
available to be modified by <command>UPDATE</> and <command>DELETE</>
237+
statements on the view. However, an <command>UPDATE</> is allowed to
238+
change a row so that it no longer satisfies the <literal>WHERE</>
239+
condition, and thus is no longer visible through the view. Similarly,
240+
an <command>INSERT</> command can potentially insert base-relation rows
241+
that do not satisfy the <literal>WHERE</> condition and thus are not
242+
visible through the view.
243+
</para>
244+
245+
<para>
246+
A more complex view that does not satisfy all these conditions is
247+
read-only by default: the system will not allow an insert, update, or
248+
delete on the view. You can get the effect of an updatable view by
249+
creating <literal>INSTEAD OF</> triggers on the view, which must
250+
convert attempted inserts, etc. on the view into appropriate actions
251+
on other tables. For more information see <xref
252+
linkend="sql-createtrigger">. Another possibility is to create rules
253+
(see <xref linkend="sql-createrule">), but in practice triggers are
254+
easier to understand and use correctly.
255+
</para>
256+
257+
<para>
258+
Note that the user performing the insert, update or delete on the view
259+
must have the corresponding insert, update or delete privilege on the
260+
view. In addition the view's owner must have the relevant privileges on
261+
the underlying base relations, but the user performing the update does
262+
not need any permissions on the underlying base relations (see
263+
<xref linkend="rules-privileges">).
264+
</para>
265+
</refsect2>
178266
</refsect1>
179267

180268
<refsect1>
@@ -217,11 +305,15 @@ CREATE VIEW <replaceable class="parameter">name</replaceable> [ ( <replaceable c
217305
<term><literal>CHECK OPTION</literal></term>
218306
<listitem>
219307
<para>
220-
This option has to do with updatable views. All
221-
<command>INSERT</> and <command>UPDATE</> commands on the view
222-
will be checked to ensure data satisfy the view-defining
223-
condition (that is, the new data would be visible through the
224-
view). If they do not, the update will be rejected.
308+
This option controls the behavior of automatically updatable views.
309+
When given, <command>INSERT</> and <command>UPDATE</> commands on
310+
the view will be checked to ensure new rows satisfy the
311+
view-defining condition (that is, the new rows would be visible
312+
through the view). If they do not, the update will be rejected.
313+
Without <literal>CHECK OPTION</literal>, <command>INSERT</> and
314+
<command>UPDATE</> commands on the view are allowed to create rows
315+
that are not visible through the view. (The latter behavior is the
316+
only one currently provided by <productname>PostgreSQL</>.)
225317
</para>
226318
</listitem>
227319
</varlistentry>
@@ -252,6 +344,7 @@ CREATE VIEW <replaceable class="parameter">name</replaceable> [ ( <replaceable c
252344
<command>CREATE OR REPLACE VIEW</command> is a
253345
<productname>PostgreSQL</productname> language extension.
254346
So is the concept of a temporary view.
347+
The <literal>WITH</> clause is an extension as well.
255348
</para>
256349
</refsect1>
257350

‎doc/src/sgml/rules.sgml‎

Lines changed: 38 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -808,13 +808,28 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
808808
<para>
809809
What happens if a view is named as the target relation for an
810810
<command>INSERT</command>, <command>UPDATE</command>, or
811-
<command>DELETE</command>? Simply doing the substitutions
811+
<command>DELETE</command>? Doing the substitutions
812812
described above would give a query tree in which the result
813813
relation points at a subquery range-table entry, which will not
814-
work. Instead, the rewriter assumes that the operation will be
815-
handled by an <literal>INSTEAD OF</> trigger on the view.
816-
(If there is no such trigger, the executor will throw an error
817-
when execution starts.) Rewriting works slightly differently
814+
work. There are several ways in which <productname>PostgreSQL</>
815+
can support the appearance of updating a view, however.
816+
</para>
817+
818+
<para>
819+
If the subquery selects from a single base relation and is simple
820+
enough, the rewriter can automatically replace the subquery with the
821+
underlying base relation so that the <command>INSERT</command>,
822+
<command>UPDATE</command>, or <command>DELETE</command> is applied to
823+
the base relation in the appropriate way. Views that are
824+
<quote>simple enough</> for this are called <firstterm>automatically
825+
updatable</>. For detailed information on the kinds of view that can
826+
be automatically updated, see <xref linkend="sql-createview">.
827+
</para>
828+
829+
<para>
830+
Alternatively, the operation may be handled by a user-provided
831+
<literal>INSTEAD OF</> trigger on the view.
832+
Rewriting works slightly differently
818833
in this case. For <command>INSERT</command>, the rewriter does
819834
nothing at all with the view, leaving it as the result relation
820835
for the query. For <command>UPDATE</command> and
@@ -842,10 +857,8 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
842857
</para>
843858

844859
<para>
845-
If there are no <literal>INSTEAD OF</> triggers to update the view,
846-
the executor will throw an error, because it cannot automatically
847-
update a view by itself. To change this, we can define rules that
848-
modify the behavior of <command>INSERT</command>,
860+
Another possibility is for the user to define <literal>INSTEAD</>
861+
rules that specify substitute actions for <command>INSERT</command>,
849862
<command>UPDATE</command>, and <command>DELETE</command> commands on
850863
a view. These rules will rewrite the command, typically into a command
851864
that updates one or more tables, rather than views. That is the topic
@@ -860,6 +873,22 @@ SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
860873
evaluated first, and depending on the result, the triggers may not be
861874
used at all.
862875
</para>
876+
877+
<para>
878+
Automatic rewriting of an <command>INSERT</command>,
879+
<command>UPDATE</command>, or <command>DELETE</command> query on a
880+
simple view is always tried last. Therefore, if a view has rules or
881+
triggers, they will override the default behavior of automatically
882+
updatable views.
883+
</para>
884+
885+
<para>
886+
If there are no <literal>INSTEAD</> rules or <literal>INSTEAD OF</>
887+
triggers for the view, and the rewriter cannot automatically rewrite
888+
the query as an update on the underlying base relation, an error will
889+
be thrown because the executor cannot update a view as such.
890+
</para>
891+
863892
</sect2>
864893

865894
</sect1>

‎src/backend/catalog/information_schema.sql‎

Lines changed: 6 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -730,10 +730,8 @@ CREATE VIEW columns AS
730730
CAST('NEVER' AS character_data) AS is_generated,
731731
CAST(null AS character_data) AS generation_expression,
732732

733-
CAST(CASE WHEN c.relkind = 'r'
734-
OR (c.relkind = 'v'
735-
AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
736-
AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead))
733+
CAST(CASE WHEN c.relkind = 'r' OR
734+
(c.relkind = 'v' AND pg_view_is_updatable(c.oid))
737735
THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable
738736

739737
FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)
@@ -1896,9 +1894,8 @@ CREATE VIEW tables AS
18961894
CAST(nt.nspname AS sql_identifier) AS user_defined_type_schema,
18971895
CAST(t.typname AS sql_identifier) AS user_defined_type_name,
18981896

1899-
CAST(CASE WHEN c.relkind = 'r'
1900-
OR (c.relkind = 'v'
1901-
AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead))
1897+
CAST(CASE WHEN c.relkind = 'r' OR
1898+
(c.relkind = 'v' AND pg_view_is_insertable(c.oid))
19021899
THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into,
19031900

19041901
CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed,
@@ -2497,14 +2494,11 @@ CREATE VIEW views AS
24972494
CAST('NONE' AS character_data) AS check_option,
24982495

24992496
CAST(
2500-
CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
2501-
AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead)
2502-
THEN 'YES' ELSE 'NO' END
2497+
CASE WHEN pg_view_is_updatable(c.oid) THEN 'YES' ELSE 'NO' END
25032498
AS yes_or_no) AS is_updatable,
25042499

25052500
CAST(
2506-
CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead)
2507-
THEN 'YES' ELSE 'NO' END
2501+
CASE WHEN pg_view_is_insertable(c.oid) THEN 'YES' ELSE 'NO' END
25082502
AS yes_or_no) AS is_insertable_into,
25092503

25102504
CAST(

0 commit comments

Comments
 (0)