Обсуждение: Request for new function in view update
Hello all,
I am a researcher in databases who would like to suggest a new function. I am writing to you because you have an active developer community. Your website said that suggestions for new functions should go to this mailing list. If there is another mailing list you prefer, please let me know.
My research is in updating views -- the problem of translating an update in a view to an update to a set of underlying base tables. This problem has been partially solved for many years, including in PostgreSQL, but a complete solution hasn't been found.
Views are useful for data independence; if users only access data through views, then underlying databases can change without user programs. Data independence requires an automatic solution to the view update problem.
In my research, I went back to the initial papers about the problem. The most promising approach was the "constant complement" approach. It starts from the idea that a view shows only part of the information in a database, and that view updates should never change the part of the database that isn't exposed in the view. (The "complement" is the unexposed part, and "constant" means that a view update shouldn't change the complement.) The "constant complement" constraint is intuitive, that a view update shouldn't have side effects on information not available through the view.
A seminal paper showed that defining a complement is enough, because each complement of a view creates a unique view update. Unfortunately, there are limitations. Views have multiple complements, and no unique minimal complement exists. Because of this limitation and other practical difficulties, the constant complement approach was abandoned.
I used a theorem in this initial paper that other researchers didn't use, that shows the inverse. An update method defines a unique complement. I used the two theorems as a saw's upstroke and downstroke to devise view update methods for several relational operators. Unlike other approaches, these methods have a solid mathematical foundation.
Some relational operators are easy (selection), others are hard (projection); some have several valid update methods that can be used interchangeably (union) and some can have several valid update methods that reflect different semantics (joins). For joins, I found clues in the database that can determine which update method to use. I address the other relational operators, but not in the attached paper
.
I also discuss the problem of when views can't have updates, and possible reasons why.
I have attached my arXiv paper. I would appreciate anyone's interest in this topic.
Yours
Terry Brennan
Вложения
On 01/06/2023 13:18, Terry Brennan wrote: > Hello all, > > I am a researcher in databases who would like to suggest a > new function. I am writing to you because you have an active developer > community. Your website said that suggestions for new functions should > go to this mailing list. If there is another mailing list you prefer, > please let me know. You're in the right place. > My research is in updating views -- the problem of translating an update > in a view to an update to a set of underlying base tables. This problem > has been partially solved for many years, including in PostgreSQL, but a > complete solution hasn't been found. Yeah, PostgreSQL only supports updating views in some simple cases [1]. Patches to handle more cases welcome. [1] https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS -- Heikki Linnakangas Neon (https://neon.tech)
Hi Heikki
PostgreSQL supports only one-table views, which means that the relational operators are limited to "selection" and "projection." I have provided update methods for these two, plus for two kinds of joins and unions.
I discuss a hierarchical join, when two tables together define an entity. The classic example is an invoice. One table, the Invoice Master table, has a row for each invoice. The daughter table, the Invoice Detail table, has a row for each item on each invoice. The two tables are linked by having the same key -- the invoice number. When updating, a detail row should never be left without a corresponding master row, though master rows without detail rows can exist.
The second type is a foreign key. For example, an invoice detail line will have an item number column, containing a key for the Item table. The key for the Invoice Detail table is unrelated to the key for the Item table. Deleting an Invoice Detail row should never delete an Item row, and adding an Invoice Detail row should never add an Item row.
These two examples have the same relational operator -- join -- that have different semantics -- hierarchical and foreign key -- leading to different update methods. PostgreSQL can determine which type of join is present by examining the primary keys of the two tables, and by examining other clues, such as referential integrity checking.
Adding join and union would allow many more views to be updateable.
Yours,
Terry Brennan
On Thu, Jun 1, 2023 at 8:43 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
On 01/06/2023 13:18, Terry Brennan wrote:
> Hello all,
>
> I am a researcher in databases who would like to suggest a
> new function. I am writing to you because you have an active developer
> community. Your website said that suggestions for new functions should
> go to this mailing list. If there is another mailing list you prefer,
> please let me know.
You're in the right place.
> My research is in updating views -- the problem of translating an update
> in a view to an update to a set of underlying base tables. This problem
> has been partially solved for many years, including in PostgreSQL, but a
> complete solution hasn't been found.
Yeah, PostgreSQL only supports updating views in some simple cases [1].
Patches to handle more cases welcome.
[1]
https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS
--
Heikki Linnakangas
Neon (https://neon.tech)
On Thu, 1 Jun 2023 12:18:47 -0500 Terry Brennan <terryjbrennan@gmail.com> wrote: > Hello all, > > I am a researcher in databases who would like to suggest a new function. I > am writing to you because you have an active developer community. Your > website said that suggestions for new functions should go to this mailing > list. If there is another mailing list you prefer, please let me know. > > My research is in updating views -- the problem of translating an update in > a view to an update to a set of underlying base tables. This problem has > been partially solved for many years, including in PostgreSQL, but a > complete solution hasn't been found. > > Views are useful for data independence; if users only access data through > views, then underlying databases can change without user programs. Data > independence requires an automatic solution to the view update problem. > > In my research, I went back to the initial papers about the problem. The > most promising approach was the "constant complement" approach. It starts > from the idea that a view shows only part of the information in a database, > and that view updates should never change the part of the database that > isn't exposed in the view. (The "complement" is the unexposed part, and > "constant" means that a view update shouldn't change the complement.) The > "constant complement" constraint is intuitive, that a view update shouldn't > have side effects on information not available through the view. > > A seminal paper showed that defining a complement is enough, because each > complement of a view creates a unique view update. Unfortunately, there > are limitations. Views have multiple complements, and no unique minimal > complement exists. Because of this limitation and other practical > difficulties, the constant complement approach was abandoned. > > I used a theorem in this initial paper that other researchers didn't use, > that shows the inverse. An update method defines a unique complement. I > used the two theorems as a saw's upstroke and downstroke to devise view > update methods for several relational operators. Unlike other approaches, > these methods have a solid mathematical foundation. > > Some relational operators are easy (selection), others are hard > (projection); some have several valid update methods that can be used > interchangeably (union) and some can have several valid update methods that > reflect different semantics (joins). For joins, I found clues in the > database that can determine which update method to use. I address the > other relational operators, but not in the attached paper > . > I also discuss the problem of when views can't have updates, and possible > reasons why. > > I have attached my arXiv paper. I would appreciate anyone's interest in > this topic. I'm interested in the view update problem because we have some works on this topic [1][2]. I read your paper. Although I don't understand the theoretical part enough, I found your proposal methods to update views as for several relational operators. The method for updating selection views seems same as the way of automatically updatable views in the current PostgreSQL, that is, deleting/updating rows in a view results in deletes/updates for corresponding rows in the base table. Inserting rows that is not compliant to the view is checked and prevented if the view is defined with WITH CHECK OPTION. However, the proposed method for projection is that a column not contained in the view is updated to NULL when a row is deleted. I think it is not desirable to use NULL in a such special purpose, and above all, this is different the current PostgreSQL behavior, so I wonder it would not accepted to change it. I think it would be same for the method for JOIN that uses NULL for the special use. I think it would be nice to extend view updatability of PostgreSQL because the SQL standard allows more than the current limited support. In this case, I wonder we should follow SQL:1999 or later, and maybe this would be somehow compatible to the spec in Oracle. [1] https://dl.acm.org/doi/10.1145/3164541.3164584 [2] https://www.pgcon.org/2017/schedule/events/1074.en.html Regards, Yugo Nagata > Yours > Terry Brennan -- Yugo NAGATA <nagata@sraoss.co.jp>
Hi Yugo
Thank you for taking a look at the paper.
The key difference from PostgreSQL is that it only allows updates on single table views. My paper discusses two kinds of joins of two tables. It discusses how to update them and how to determine when they occur.
The paper also discusses unioning two tables. I've done work on table intersection and table difference too, but didn't include it in the paper.
Terry Brennan
On Wed, Jun 28, 2023 at 2:49 AM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
On Thu, 1 Jun 2023 12:18:47 -0500
Terry Brennan <terryjbrennan@gmail.com> wrote:
> Hello all,
>
> I am a researcher in databases who would like to suggest a new function. I
> am writing to you because you have an active developer community. Your
> website said that suggestions for new functions should go to this mailing
> list. If there is another mailing list you prefer, please let me know.
>
> My research is in updating views -- the problem of translating an update in
> a view to an update to a set of underlying base tables. This problem has
> been partially solved for many years, including in PostgreSQL, but a
> complete solution hasn't been found.
>
> Views are useful for data independence; if users only access data through
> views, then underlying databases can change without user programs. Data
> independence requires an automatic solution to the view update problem.
>
> In my research, I went back to the initial papers about the problem. The
> most promising approach was the "constant complement" approach. It starts
> from the idea that a view shows only part of the information in a database,
> and that view updates should never change the part of the database that
> isn't exposed in the view. (The "complement" is the unexposed part, and
> "constant" means that a view update shouldn't change the complement.) The
> "constant complement" constraint is intuitive, that a view update shouldn't
> have side effects on information not available through the view.
>
> A seminal paper showed that defining a complement is enough, because each
> complement of a view creates a unique view update. Unfortunately, there
> are limitations. Views have multiple complements, and no unique minimal
> complement exists. Because of this limitation and other practical
> difficulties, the constant complement approach was abandoned.
>
> I used a theorem in this initial paper that other researchers didn't use,
> that shows the inverse. An update method defines a unique complement. I
> used the two theorems as a saw's upstroke and downstroke to devise view
> update methods for several relational operators. Unlike other approaches,
> these methods have a solid mathematical foundation.
>
> Some relational operators are easy (selection), others are hard
> (projection); some have several valid update methods that can be used
> interchangeably (union) and some can have several valid update methods that
> reflect different semantics (joins). For joins, I found clues in the
> database that can determine which update method to use. I address the
> other relational operators, but not in the attached paper
> .
> I also discuss the problem of when views can't have updates, and possible
> reasons why.
>
> I have attached my arXiv paper. I would appreciate anyone's interest in
> this topic.
I'm interested in the view update problem because we have some works on
this topic [1][2].
I read your paper. Although I don't understand the theoretical part enough,
I found your proposal methods to update views as for several relational operators.
The method for updating selection views seems same as the way of automatically
updatable views in the current PostgreSQL, that is, deleting/updating rows in
a view results in deletes/updates for corresponding rows in the base table.
Inserting rows that is not compliant to the view is checked and prevented if
the view is defined with WITH CHECK OPTION.
However, the proposed method for projection is that a column not contained
in the view is updated to NULL when a row is deleted. I think it is not
desirable to use NULL in a such special purpose, and above all, this is
different the current PostgreSQL behavior, so I wonder it would not accepted
to change it. I think it would be same for the method for JOIN that uses NULL
for the special use.
I think it would be nice to extend view updatability of PostgreSQL because
the SQL standard allows more than the current limited support. In this case,
I wonder we should follow SQL:1999 or later, and maybe this would be somehow
compatible to the spec in Oracle.
[1] https://dl.acm.org/doi/10.1145/3164541.3164584
[2] https://www.pgcon.org/2017/schedule/events/1074.en.html
Regards,
Yugo Nagata
> Yours
> Terry Brennan
--
Yugo NAGATA <nagata@sraoss.co.jp>