Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitdd7e54a

Browse files
committed
Automatic view update rules
Bernd Helmle
1 parent5841aa8 commitdd7e54a

30 files changed

+2289
-41
lines changed

‎doc/src/sgml/catalogs.sgml‎

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.189 2009/01/16 13:27:23 heikki Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.190 2009/01/22 17:27:54 petere Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -4144,6 +4144,13 @@
41444144
<entry>True if the rule is an <literal>INSTEAD</literal> rule</entry>
41454145
</row>
41464146

4147+
<row>
4148+
<entry><structfield>is_auto</structfield></entry>
4149+
<entry><type>bool</type></entry>
4150+
<entry></entry>
4151+
<entry>True if the rule was automatically generated</entry>
4152+
</row>
4153+
41474154
<row>
41484155
<entry><structfield>ev_qual</structfield></entry>
41494156
<entry><type>text</type></entry>

‎doc/src/sgml/intro.sgml‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/intro.sgml,v 1.32 2007/01/31 20:56:17 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/intro.sgml,v 1.33 2009/01/22 17:27:54 petere Exp $ -->
22

33
<preface id="preface">
44
<title>Preface</title>
@@ -110,7 +110,7 @@
110110
<simpara>triggers</simpara>
111111
</listitem>
112112
<listitem>
113-
<simpara>views</simpara>
113+
<simpara>updatableviews</simpara>
114114
</listitem>
115115
<listitem>
116116
<simpara>transactional integrity</simpara>

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

Lines changed: 94 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_view.sgml,v 1.39 2008/12/15 21:35:31 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_view.sgml,v 1.40 2009/01/22 17:27:54 petere Exp $
33
PostgreSQL documentation
44
-->
55

@@ -115,11 +115,99 @@ CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW <replaceable class="PARAMETER">n
115115
<title>Notes</title>
116116

117117
<para>
118-
Currently, views are read only: the system will not allow an insert,
119-
update, or delete on a view. You can get the effect of an updatable
120-
view by creating rules that rewrite inserts, etc. on the view into
121-
appropriate actions on other tables. For more information see
122-
<xref linkend="sql-createrule" endterm="sql-createrule-title">.
118+
Some views are updatable, which means that the
119+
commands <command>INSERT</command>, <command>UPDATE</command>,
120+
and <command>DELETE</command> can be used on the view as if it
121+
were a regular table. A view is updatable if it
122+
does <emphasis>not</emphasis> contain:
123+
124+
<itemizedlist>
125+
<listitem>
126+
<para>
127+
more than one underlying table (joins) or no underlying table at all
128+
</para>
129+
</listitem>
130+
131+
<listitem>
132+
<para>
133+
underlying tables/views that are themselves not updatable,
134+
including table value constructors and table functions
135+
</para>
136+
</listitem>
137+
138+
<listitem>
139+
<para>
140+
subqueries in the <literal>FROM</literal> list
141+
</para>
142+
</listitem>
143+
144+
<listitem>
145+
<para>
146+
items in the select list that are not direct references to a
147+
column of the underlying table, such as literals or any
148+
nontrivial value expression
149+
</para>
150+
</listitem>
151+
152+
<listitem>
153+
<para>
154+
references to system columns in the select list
155+
</para>
156+
</listitem>
157+
158+
<listitem>
159+
<para>
160+
more than one reference to the same column in the select list
161+
</para>
162+
</listitem>
163+
164+
<listitem>
165+
<para>aggregate function calls</para>
166+
</listitem>
167+
168+
<listitem>
169+
<para>window function calls</para>
170+
</listitem>
171+
172+
<listitem>
173+
<para>
174+
<literal>WITH</literal> or <literal>WITH RECURSIVE</literal> clauses
175+
</para>
176+
</listitem>
177+
178+
<listitem>
179+
<para>
180+
<literal>DISTINCT</literal>, <literal>GROUP BY</literal>, or
181+
<literal>HAVING</literal> clauses
182+
</para>
183+
</listitem>
184+
185+
<listitem>
186+
<para>
187+
<literal>UNION</literal>, <literal>INTERSECT</literal>, or
188+
<literal>EXCEPT</literal> clauses
189+
</para>
190+
</listitem>
191+
192+
<listitem>
193+
<para>
194+
<literal>LIMIT</literal> or <literal>OFFSET</literal> clauses
195+
(or other equivalent spellings thereof)
196+
</para>
197+
</listitem>
198+
</itemizedlist>
199+
</para>
200+
201+
<para>
202+
The updatable views implementation is based on the rule system.
203+
Because of this, you can also make more complex views updatable or
204+
insertable by creating your own rules that rewrite
205+
the <command>INSERT</command>,
206+
<command>UPDATE</command>, and <command>DELETE</command> actions
207+
on the view into appropriate actions on other tables. You can
208+
also replace the automatically generated rules by your own rules.
209+
For more information on the rule system, refer
210+
to <xref linkend="sql-createrule" endterm="sql-createrule-title">.
123211
</para>
124212

125213
<para>

‎src/backend/commands/view.c‎

Lines changed: 20 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/view.c,v 1.111 2009/01/01 17:23:40 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/view.c,v 1.112 2009/01/22 17:27:54 petere Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -27,7 +27,9 @@
2727
#include"parser/parse_relation.h"
2828
#include"rewrite/rewriteDefine.h"
2929
#include"rewrite/rewriteManip.h"
30+
#include"rewrite/rewriteRemove.h"
3031
#include"rewrite/rewriteSupport.h"
32+
#include"rewrite/viewUpdate.h"
3133
#include"utils/acl.h"
3234
#include"utils/builtins.h"
3335
#include"utils/lsyscache.h"
@@ -308,13 +310,28 @@ DefineViewRules(Oid viewOid, Query *viewParse, bool replace)
308310
viewOid,
309311
NULL,
310312
CMD_SELECT,
311-
true,
313+
true,/* is_instead */
314+
true,/* is_auto */
312315
replace,
313316
list_make1(viewParse));
314317

315318
/*
316-
* Someday: automatic ON INSERT, etc
319+
* Delete all implicit rules on replace. CreateViewUpdateRules()
320+
* below will re-create them if appropriate for the new view
321+
* definition.
317322
*/
323+
if (replace)
324+
{
325+
Relationrel=heap_open(viewOid,AccessExclusiveLock);
326+
RemoveAutomaticRulesOnEvent(rel,CMD_INSERT);
327+
RemoveAutomaticRulesOnEvent(rel,CMD_DELETE);
328+
RemoveAutomaticRulesOnEvent(rel,CMD_UPDATE);
329+
heap_close(rel,NoLock);
330+
}
331+
332+
CommandCounterIncrement();
333+
334+
CreateViewUpdateRules(viewOid,viewParse);
318335
}
319336

320337
/*---------------------------------------------------------------

‎src/backend/rewrite/Makefile‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
# Makefile for rewrite
55
#
66
# IDENTIFICATION
7-
# $PostgreSQL: pgsql/src/backend/rewrite/Makefile,v 1.17 2008/02/19 10:30:08 petere Exp $
7+
# $PostgreSQL: pgsql/src/backend/rewrite/Makefile,v 1.18 2009/01/22 17:27:54 petere Exp $
88
#
99
#-------------------------------------------------------------------------
1010

@@ -13,6 +13,7 @@ top_builddir = ../../..
1313
include$(top_builddir)/src/Makefile.global
1414

1515
OBJS = rewriteRemove.o rewriteDefine.o\
16-
rewriteHandler.o rewriteManip.o rewriteSupport.o
16+
rewriteHandler.o rewriteManip.o rewriteSupport.o\
17+
viewUpdate.o
1718

1819
include$(top_srcdir)/src/backend/common.mk

‎src/backend/rewrite/rewriteDefine.c‎

Lines changed: 64 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,13 +8,14 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/rewrite/rewriteDefine.c,v 1.134 2009/01/01 17:23:47 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/rewrite/rewriteDefine.c,v 1.135 2009/01/22 17:27:54 petere Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
1515
#include"postgres.h"
1616

1717
#include"access/heapam.h"
18+
#include"access/xact.h"
1819
#include"catalog/dependency.h"
1920
#include"catalog/indexing.h"
2021
#include"catalog/namespace.h"
@@ -25,6 +26,7 @@
2526
#include"parser/parse_utilcmd.h"
2627
#include"rewrite/rewriteDefine.h"
2728
#include"rewrite/rewriteManip.h"
29+
#include"rewrite/rewriteRemove.h"
2830
#include"rewrite/rewriteSupport.h"
2931
#include"utils/acl.h"
3032
#include"utils/builtins.h"
@@ -39,6 +41,7 @@ static void checkRuleResultList(List *targetList, TupleDesc resultDesc,
3941
boolisSelect);
4042
staticboolsetRuleCheckAsUser_walker(Node*node,Oid*context);
4143
staticvoidsetRuleCheckAsUser_Query(Query*qry,Oiduserid);
44+
staticconstchar*rule_event_string(CmdTypeevtype);
4245

4346

4447
/*
@@ -52,6 +55,7 @@ InsertRule(char *rulname,
5255
Oideventrel_oid,
5356
AttrNumberevslot_index,
5457
boolevinstead,
58+
boolis_auto,
5559
Node*event_qual,
5660
List*action,
5761
boolreplace)
@@ -84,6 +88,7 @@ InsertRule(char *rulname,
8488
values[i++]=CharGetDatum(evtype+'0');/* ev_type */
8589
values[i++]=CharGetDatum(RULE_FIRES_ON_ORIGIN);/* ev_enabled */
8690
values[i++]=BoolGetDatum(evinstead);/* is_instead */
91+
values[i++]=BoolGetDatum(is_auto);/* is_auto */
8792
values[i++]=CStringGetTextDatum(evqual);/* ev_qual */
8893
values[i++]=CStringGetTextDatum(actiontree);/* ev_action */
8994

@@ -102,7 +107,11 @@ InsertRule(char *rulname,
102107

103108
if (HeapTupleIsValid(oldtup))
104109
{
105-
if (!replace)
110+
/*
111+
* If REPLACE was not used we still check if the old rule is
112+
* automatic: Then we replace it anyway.
113+
*/
114+
if (!replace&& !((Form_pg_rewrite)GETSTRUCT(oldtup))->is_auto)
106115
ereport(ERROR,
107116
(errcode(ERRCODE_DUPLICATE_OBJECT),
108117
errmsg("rule \"%s\" for relation \"%s\" already exists",
@@ -115,6 +124,7 @@ InsertRule(char *rulname,
115124
replaces[Anum_pg_rewrite_ev_attr-1]= true;
116125
replaces[Anum_pg_rewrite_ev_type-1]= true;
117126
replaces[Anum_pg_rewrite_is_instead-1]= true;
127+
replaces[Anum_pg_rewrite_is_auto-1]= true;
118128
replaces[Anum_pg_rewrite_ev_qual-1]= true;
119129
replaces[Anum_pg_rewrite_ev_action-1]= true;
120130

@@ -205,6 +215,7 @@ DefineRule(RuleStmt *stmt, const char *queryString)
205215
whereClause,
206216
stmt->event,
207217
stmt->instead,
218+
false,/* not is_auto */
208219
stmt->replace,
209220
actions);
210221
}
@@ -223,6 +234,7 @@ DefineQueryRewrite(char *rulename,
223234
Node*event_qual,
224235
CmdTypeevent_type,
225236
boolis_instead,
237+
boolis_auto,
226238
boolreplace,
227239
List*action)
228240
{
@@ -446,6 +458,42 @@ DefineQueryRewrite(char *rulename,
446458
RelationGetDescr(event_relation),
447459
false);
448460
}
461+
462+
/*
463+
* If defining a non-automatic DO INSTEAD rule, drop all
464+
* automatic rules on the same event.
465+
*/
466+
if (!is_auto&&is_instead)
467+
{
468+
RemoveAutomaticRulesOnEvent(event_relation,event_type);
469+
CommandCounterIncrement();
470+
}
471+
472+
/*
473+
* If defining an automatic rule and there is a manual rule on
474+
* the same event, warn and don't do it.
475+
*/
476+
if (is_auto&&event_relation->rd_rules!=NULL)
477+
{
478+
inti;
479+
480+
for (i=0;i<event_relation->rd_rules->numLocks;i++)
481+
{
482+
RewriteRule*rule=event_relation->rd_rules->rules[i];
483+
484+
if (rule->event==event_type&& !rule->is_auto&&rule->isInstead==is_instead)
485+
{
486+
ereport(WARNING,
487+
(errmsg("automatic %s rule not created because manually created %s rule exists",
488+
rule_event_string(event_type),rule_event_string(event_type)),
489+
errhint("If you prefer to have the automatic rule, drop the manually created rule and run CREATE OR REPLACE VIEW again.")));
490+
491+
heap_close(event_relation,NoLock);
492+
return;
493+
}
494+
}
495+
}
496+
449497
}
450498

451499
/*
@@ -461,6 +509,7 @@ DefineQueryRewrite(char *rulename,
461509
event_relid,
462510
event_attno,
463511
is_instead,
512+
is_auto,
464513
event_qual,
465514
action,
466515
replace);
@@ -754,3 +803,16 @@ RenameRewriteRule(Oid owningRel, const char *oldName,
754803
}
755804

756805
#endif
806+
807+
808+
staticconstchar*
809+
rule_event_string(CmdTypetype)
810+
{
811+
if (type==CMD_INSERT)
812+
return"INSERT";
813+
if (type==CMD_UPDATE)
814+
return"UPDATE";
815+
if (type==CMD_DELETE)
816+
return"DELETE";
817+
return"???";
818+
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp