Extending View Updatability by a Novel Theory
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Subtitle |
| |
| Title of Series | ||
| Number of Parts | 19 | |
| Author | ||
| License | CC Attribution 3.0 Unported: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor. | |
| Identifiers | 10.5446/48960 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
PGCon 201719 / 19
1
7
8
10
11
12
15
00:00
Computer animation
01:35
Computer animation
03:42
Computer animation
04:59
Computer animation
06:40
Computer animation
10:31
Computer animation
14:32
Computer animation
16:04
Computer animation
18:31
Computer animation
19:34
Computer animation
20:43
Computer animation
25:36
Computer animation
27:04
Computer animation
30:44
Computer animation
31:47
Computer animation
32:52
Computer animation
34:01
Computer animation
37:39
Computer animation
39:08
Computer animation
40:11
Computer animation
40:55
Computer animation
43:54
Computer animation
45:07
Computer animation
Transcript: English(auto-generated)
00:15
Yuu Nagata from Celery Oasis in Japan.
00:21
This presentation title is Extending View Updatability by a novel theory prototype implementation on PostgresQL. As this shows, I would like to introduce a novel theory to extend view updatability of RDBMS and
00:42
talk about the prototype implementation of this theory. This is a joint work with Dosto Masunaga. He is author of the paper of this theory.
01:04
This work is based on his idea, which I call a novel theory here. I will talk about the current status of his implementation on PostgresQL.
01:21
I would appreciate if you hold your questions till the end of presentation, please. Okay, this is an outline of this talk.
01:40
Firstly, I'll introduce a problem that we are trying to resolve. View Updatability problem. In this part, I'll explain what View Updatability problem is and how it is handled in PostgresQL and SQL standard.
02:02
Then I'll talk about theory to resolve this problem. In this part, I'll summarize the problem and introduce our new theory using the intention-based approach we call this.
02:20
I'll explain the basic idea to resolve the problem and algorithm called pro forma guessing. Next, I'll talk about prototype implementation of the algorithm, which we are developing on PostgresQL.
02:41
I'll also talk about a new feature of Postgres 10 used in this implementation. At the end of this part, I'll show you some examples of demonstration about this implementation.
03:00
Finally, I'll summarize the talk and finish. Before I introduce View Updatability problem, I'd like to start with what is the view? Originally, views were first introduced by Dr. Cote, the investor of the relation data model, in 1974.
03:29
And now, as you know, almost RDB-MS, including PostgresQL, support views. A view can be regarded as a virtual relation.
03:43
Sorry, a virtual relation based on the result set of select queries. Any data overview is not contained in the database, and only the view definition is contained.
04:09
There are a few purposes of the view support. The first is to simplify complex queries that are used repeatedly in applications.
04:23
Thanks to this view support, users don't need to write long SQL queries many times. It also provides logical data independence. So if the relation schema in the database is changed,
04:42
applications that access the relation via a view may not have to rewrite the SQL code. View is also used for security purposes by hiding some information from some users.
05:02
This is a simple example of view. Well, EMP is a relation representing employees, the field is employee number, employee name, departmental number, and salary.
05:22
And K41 EMP and FUWA EMP, these are views. This is a view of employee, the departmental number is K41, and this view is employee, the food salary is lower than 30.
05:55
This relation EMP is the base relation of these views.
06:04
This table data is contained in the database. So it can be said, it is a real relation, real relation, yes. And these views, this views data itself is not contained database,
06:25
and only view definition is in the database. So these are called to be virtual relations.
06:42
So what is view update program? A view is regarded as a relation virtually, virtually, but it's regarded as a relation.
07:03
So users maybe have a motivation to handle views as a regular relation, as same as possible. That is, it would be good if user can access the views at the same manner of relations.
07:21
Actually, read queries, that is, select statements, are always acceptable to view. If user issues select query to views, it is modified, it is related to the query, to the base relation, it is always okay.
07:46
However, as you know, update queries, that is, delete, insert, update statement, these are not always acceptable. So views are not always updatable.
08:04
So one question is, what kind of views are updatable? And if one view is updatable, request, appropriate request to the views have to be translated to
08:26
request, appropriate to these base relations. So second question is, how to translate appropriate request on views into appropriate request on base relations?
08:40
We call this problem view appropriate problem. Here is an example of not updatable view. R and S is the base relations. Each have two columns.
09:01
And this view, B, is a natural drawing view of R and S, from R and S. Request to delete A, B dash, C from this view is issued.
09:24
There are three translation alternatives. T1 is delete A, B dash from R. T2 is delete B dash C from S. And T3 is executing both T1 and T2.
09:45
However, we can't determine which translation is the answer.
10:00
Because each of them is realize this request. So we can determine which is the answer. So this is called, we call this translation ambiguity.
10:22
And in this view and in this appropriate request, translation ambiguity cannot be resolved. So we can say this view is not updatable.
10:42
By the way, we can say that the view update program is already resolved partially because, as you know, PostgreSQL support automatically updatable view since 9.3.
11:02
Thanks to this feature, affiliate query to simple views are automatically updatable. The conditions for automatically updatable view here, as described in PostgreSQL official document.
11:21
First, the view must have exactly one entry in its from list, which must be a table or another updatable view. And second, the view definition must not contain with distinct group by having limit or first clause at the top level.
11:41
Third, the view definition must not contain set operations. Set operations, that is, you know, intersect or accept. Finally, the view select list must not contain any aggregation window function or set returning functions. And as shown here, this is very restrictive, so limited.
12:07
So, for example, any view contain join cannot be updatable. With regard to the SQL standard,
12:21
the condition of PostgreSQL automatically updatable view is basically according to SQL 92. Also, a few condition are relaxed. After the SQL 92, view update ability is largely extended at SQL 1999.
12:53
And in this specification, views including join and immune-all view are updatable under some conditions.
13:06
And some condition and Oracle DB supports it partially. However, it is still restrictive. Here, someone may say, it's no problem.
13:23
You can do anything using instead of a polygon. And it will resolve the problem perfectly. Yes. It is true that users can create instead of polygon on complex views
13:40
and do any actions you like in the polygon functions. If this polygon function comparts the attempt query and it will create actions on other tables, this will work well.
14:00
However, in this matter, basically you have to define specific triggers for each view. And although you can define any action using instead of trigger, the best way to convert the attempt query appropriately is not clear.
14:23
Instead, we would like to propose more general manners to extend view update ability. So, now I'll talk about the theory to resolve the view update problem.
14:44
This is a novel theory proposed last year by Dr. Masunaga. And this is called an intention-based approach.
15:01
In this figure, first let me summarize the problem using the figure and the equation. It's not difficult. In this figure, v is, notation v is a view definition. And s-tau is state of base relation at time tau.
15:25
So when s-tau is base relation and using view definition, we can get the state of the relation, data in virtual relation at time tau.
15:42
And now, if a plate request for view, u, is provided, we can get u, v, s-tau, u, v, s-tau. This is desired result of this update,
16:06
this desired state. Next, we can consider some t. This is a translation of a plate request. Using this translation,
16:21
a plate request for view is translated to a plate request on base relations. So, current base relations and translated a plate request. Using them, we can get updated base relation,
16:47
state of updated base relation. And again, we use view definition. We can get v, t, u, s-tau, right time of this equation.
17:05
This is the result of the view station after applying this translated request to base relations.
17:23
And then, if this equation is obtained, left terms equals right terms, this view is, we can say this view is updatable
17:40
using this translation t. This equal means there is no side effect that is desired result, and actual result is the same.
18:01
And we have to find this translation uniquely, so only one translation should be there. If there are similar translation obtaining this equation,
18:21
we cannot determine which is the answer. This is ambiguity. To resolve this problem, traditionally, there are three approaches.
18:42
Syntax-based or functional approach, semantics-based approach, and interaction-based approach. Then, syntax-based approach and semantics-based approach tried to resolve the problem using view definition and uploaded request logically.
19:04
And on the other hand, interaction-based approach tried to solve the problem using interaction with user and systems. So, this theory has been studied,
19:24
but view approach problem is not yet fully resolved. So, this is an old problem, but not resolved. Then we can say this problem is an old and new problem.
19:48
Now, we have proposed a new novel theory. This is an intentional-based theory.
20:03
In this theory, the problem is resolved by testing the user's intention of the update using an algorithm called view applicability based on pro-forma guessing of the update's intention.
20:25
The basic idea is that completing each translation candidate temporarily so that we can guess user's update's intention uniquely.
20:41
So, traditional approach uses only view definition and update request information, but it uses also the data in database, the current data in database.
21:00
So, in this approach, view applicability depends on data in the current database relation. This figure shows overview of the algorithm.
21:21
This algorithm is called view applicability based on pro-forma guessing of update's intention. We create a temporarily materialized view from the base relation and view definition. Using this information, we can temporarily materialize the view.
21:54
And using this temporarily materialized view and update request to this view,
22:02
we can get desired result of the update request. And then, in addition, we can generate translation alternatives of update request
22:20
to base relation from a plate request to the view. There are several alternatives. Using one of the alternatives, we can calculate a result of a translation alternative
22:42
using base relation and view definition and one of alternatives. Finally, we can compare desired result and result of a translation alternative and comparing this, we can guessing which one is answer.
23:04
So, we can only want translation to realize the result to obtain desired result.
23:23
We can say that translation is answer and this view is updatable. Here is an example of pro-forma guessing of update's intention.
23:42
There is a national join view of from relation R and S. And if delete ABC and ABC dash from this view is requested, these are three translation alternatives.
24:02
Delete AB from R, delete BC and BC dash from S, and executing both D and two. From these alternatives, only T1 realize a desired result without side effect. So, T1 is a user's update intention
24:23
and this view is updatable. Next example is that using same views and relation, when delete AB dash C from V is requested,
24:44
there are also three translation alternatives. Delete AB dash from R, delete B dash C from S, and executing both. However, all of T1, T2, and T3 realize a desired result,
25:04
this request. So, we cannot determine which is answer. We cannot realize, we cannot resolve translation ambiguity. We cannot guess the appropriate intention.
25:22
This view is not updatable in this data, and with this data, and with this request.
25:40
So far, we have explained only join view, but our theory can handle updatability of a generally defined view. In this algorithm, a view is defined recursively using best relation and predefined views.
26:05
And thus, this recursive structure is expressed as view definition tree. For example, consider this view. This view is join of EMP and DPT,
26:28
and selection with here, and projection with select. So, we can just view like this.
26:44
Okay, we can check updativity using this scanning history recursively. So, we can check updatability of views generally defined with, generally defined view.
27:05
So, next, we talk about implementation of prototype. So, there we consider two prototype implement, this prototype is implemented as a proof of concept,
27:26
and the purpose is to test feasibility of this theory. In the current version, this prototype is developed as an extension of PostgresQL. So, I don't touch PostgresQL code,
27:42
but maybe in future, we should touch core code. We consider two approaches, rule-based approach and trigger-based approach. Rule-based approach, in rule-based approach,
28:02
we can implement this using rule system in PostgresQL. As you know, view support of PostgresQL is realized by rule systems. And this is rewriting query to another query.
28:27
So, maybe it might be natural to implement our algorithm in rule systems. In rule systems, but applet request
28:42
is provided as a query tree, that internal structure of PostgresQL. This is very informative, but complex, so it is difficult to handle this in our algorithm in the current version.
29:01
In addition, if we implement the algorithm on rule systems, we need to modify PostgresQL's core code largely. So, we decided to use the other approach.
29:25
Other approach is trigger-based approach. In this approach, we can implement the algorithm in trigger functions. And applet request is provided as a list of tuples,
29:40
tuples stores. Maybe in future, we have to use this approach, but now we decide to start with the trigger-based approach. But there was one problem.
30:01
Instead of the triggers. In PostgresQL, statement-level instead of trigger is not supported, and only low-level instead of trigger is supported. But we needed to process multiple tuples at the same time to translate request.
30:23
So we cannot use low-level instead of trigger. So, we decided to use statement-level after trigger instead of triggers. This gets possible using Postgres 10's new feature.
30:46
This is transition table. Transition table, this is a new feature of PostQL 10. This is a copy of the list node of that part, and as it shows, transition table is a table
31:07
to record change nodes available after trigger. The transition table is such a table that contains access from the server-side language,
31:25
so we can use this from trigger functions. The contents is a before or after image for a role affected by the statement which, via the trigger, can be accessed
31:48
as a tuples towards in after trigger functions. We use tuples in these tuples towards as a request for views.
32:01
So, this is the overview of the implementation. This is three triggers, before trigger, instead of trigger, after trigger. In before trigger, the view definition tree is built,
32:22
and instead of trigger, it's doing nothing, basically, but it is necessary to suppress autoappositable views. The main function is after trigger. In this function, extracting the request
32:41
for the view from transition table and check the view updateability and update the base relation, if possible. In before triggers, we build a view definition tree. View definition tree is converted from query tree.
33:05
This query tree is available by getViewQuery function. This is an API of root system of PostgreSQL. As a requirement of this view definition tree,
33:22
we need to convert any sub-trees of this tree to a scaled query of view definition. This is because to check the view updatedly recursively, any sub-tree needs to be temporarily materialized.
33:45
Here, I said materialized. In fact, simply just creating a temporary table because it has to be updated.
34:03
In after triggers, update queries or update requests for views are extracted from transition tables. This is an example of an update request
34:26
for this relation and this views, natural join views. Delete from view first as c equals c dash is expressed as delete a, b, c dash, a dash, b, c dash,
34:42
that's a list of tuples format. Also, update b set c equal d first c equal c dash is writing this tuple to this tuples. In the case of insert settlement,
35:02
it's simple, just inserting a list of tuples. List of tuples are extracted from the tuples to us. This is tg old table and tg new table.
35:24
This is a member of trigger structure. This is available in after trigger function. To check view of the updateability,
35:40
we work down the view definition tree from the top recursively to down. And this, during this work, if we find a join node, join view node, we use pro forma guessing algorithm to check view updateability.
36:02
In this process, the sovereignty of the join node, this is arranged box area, and the children nodes that are green box areas are temporarily materialized, just create as temporary tables.
36:28
And in pro forma guessing algorithm, firstly, the applet request are divided for base relations.
36:43
For example, insert a b dash d a b dash e into b is invited into a b dash into r and insert b dash d b dash e into s. And using this request, alternatives of translation is generated.
37:03
These alternatives are determined logically, it can be computed logically. In this example, we can generate ten alternatives and check if only one of these alternatives
37:22
can realize the desired result. We can say the join view is updatable and the translation is the answer. This is an example of how it works.
37:45
For example, these are two relations, base one and base two, and this is a natural join view named b. Then, issuing insert query into b
38:03
and in default, it occurs echeverly, of course. In our implementations, in extensions,
38:20
we create a function, add view ext this, add trigger function to the view. After executing this function, we can insert query to view b. For example, instead of value one, two, three, one, two, four,
38:43
we can insert two tuples to view, these blue tuples. Actually, two tuples are inserted to base two. However, when trying to insert two, two, two, this is not applicable based on pro forma guessing.
39:09
Another example. Then, issuing delete from one, three, we can delete two tuples from view.
39:21
These blue tuples are deleted from base two. But when removing condition is c equal four, this is not applicable.
39:40
In update example, we can issue this update query. We can put it to tuples of green area, but some update query is not issued
40:04
because based on pro forma guessing. We show another example, another demonstration.
40:39
This is EMP relation and DPT relation.
40:47
This is join of these relations. This is a view definition.
41:16
Just a simple join.
41:21
And then we try delete from this view here, dno equal k four one. We can then issue delete to views.
41:45
We get error. It's a default error. We add view extension to this view
42:02
and issuing same delete query. This is a debug message. We can delete two tuples.
42:23
We can delete two tuples from view. And we can delete two tuples from adjust relations, employee relations from here.
42:49
Reset. This is the initial state. Next, we try to delete k eight one.
43:05
But we get error. We cannot do it because deleting k eight one load,
43:26
this tuple, we cannot determine we should delete this tuple. We should delete this tuple. We cannot determine which should be deleted.
43:44
So we get errors.
44:05
In conclusion, I have introduced view applet program and we introduce a novel theory
44:23
called the intention-based approach and algorithms called pro forma guessing of applet intention. Using this algorithm, we can, using this algorithm, join views applet in certain cases,
44:42
although they are not applet in the traditional approach. And we show current implementation, prototype implementation with the algorithm. Using trigger-based approach and using transition table.
45:03
This is a new feature of Postgres 10. As a future France, we should handle the limitation of performance issue of this implementation and theories. We have many limitations and we have serious performance issues.
45:29
And the purpose of the prototype information is test feasibility of the theory. So we should test using this prototype in many cases,
45:44
in many relations, in many join cases, in many views cases, and give feedback to the theory to elaborate on it and again, elaborate the implementation again and again.
46:00
So in this process, maybe we might need to investigate the rule-based approach instead of the trigger-based approach. That's all. Thank you. Any questions?
46:38
A link to the slide?
47:09
Currently, there is no public input. Only papers.