Thread: rewriter in updateable views

rewriter in updateable views

From
Jaime Casanova
Date:
Hi,

Bernd and myself are working in updateable views, one thing we find is
that when we have something like:

create table foo (    col1   serial,    col2   text   default 'default'
);

create view vfoo as select * from foo;

then we create the appropiate rules for allow INSERT /UPDATE /DELETE on the view

but if we do INSERT INTO vfoo(col2) values ('some_string) the rewriter
cann resolv the value for col1. the reason is that views does not
inherit the defaults of the parent table. That is the reason you add
the ALTER TABLE  ALTER COLUMN ADD/DROP DEFAULT for views.

Ok, this is a problem for us, so we want to improve the rewriter to
see the default in the base table an add it as appropiate.

Can you comment on this? Are there any issues here we have not seen
yet? performance? possible?

regards,
Jaime Casanova


Re: rewriter in updateable views

From
Tom Lane
Date:
Jaime Casanova <systemguards@gmail.com> writes:
> ... but if we do INSERT INTO vfoo(col2) values ('some_string) the rewriter
> cann resolv the value for col1. the reason is that views does not
> inherit the defaults of the parent table. That is the reason you add
> the ALTER TABLE  ALTER COLUMN ADD/DROP DEFAULT for views.

> Ok, this is a problem for us, so we want to improve the rewriter to
> see the default in the base table an add it as appropiate.

Why do you not define the problem as "when we decide a view is
updateable and create the needed rules for it, also create default
values for it by copying up from the base tables"?
        regards, tom lane


Re: rewriter in updateable views

From
Jaime Casanova
Date:
On Fri, 18 Mar 2005 23:31:26 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jaime Casanova <systemguards@gmail.com> writes:
> > ... but if we do INSERT INTO vfoo(col2) values ('some_string) the
> rewriter
> > cann resolv the value for col1. the reason is that views does not
> > inherit the defaults of the parent table. That is the reason you add
> > the ALTER TABLE  ALTER COLUMN ADD/DROP DEFAULT for views.
> 
> > Ok, this is a problem for us, so we want to improve the rewriter to
> > see the default in the base table an add it as appropiate.
> 
> Why do you not define the problem as "when we decide a view is
> updateable and create the needed rules for it, also create default
> values for it by copying up from the base tables"?
> 
>             regards, tom lane
>
Well, that was our first thought. but what if the default value is
changed in the base table? then we have a problem, can we found in
what views we have to alter the default value in order to keep
consistency.

regards,
Jaime Casanova


Re: rewriter in updateable views

From
Simon Riggs
Date:
On Sat, 2005-03-19 at 01:10 -0500, Jaime Casanova wrote:
> On Fri, 18 Mar 2005 23:31:26 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Jaime Casanova <systemguards@gmail.com> writes:
> > > ... but if we do INSERT INTO vfoo(col2) values ('some_string) the
> > rewriter
> > > cann resolv the value for col1. the reason is that views does not
> > > inherit the defaults of the parent table. That is the reason you add
> > > the ALTER TABLE  ALTER COLUMN ADD/DROP DEFAULT for views.
> > 
> > > Ok, this is a problem for us, so we want to improve the rewriter to
> > > see the default in the base table an add it as appropiate.
> > 
> > Why do you not define the problem as "when we decide a view is
> > updateable and create the needed rules for it, also create default
> > values for it by copying up from the base tables"?
> > 
> Well, that was our first thought. but what if the default value is
> changed in the base table? then we have a problem, can we found in
> what views we have to alter the default value in order to keep
> consistency.

I can see that I might want the view to have a different default value
from that of the underlying table. I can see a reason to have multiple
updateable views on the same table, all with different columns, column
defaults and row selection clauses. (Multiple classes all held within
the same physical table, for example).

I'd suggest - if the default value for a column on a view IS NOT set,
then use the default value from the underlying table. If it IS set, then
it should stay set, even if the underlying table changes. That might
need some dependency logic in there...

Best Regards, Simon Riggs



Re: rewriter in updateable views

From
Tom Lane
Date:
Jaime Casanova <systemguards@gmail.com> writes:
> On Fri, 18 Mar 2005 23:31:26 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Why do you not define the problem as "when we decide a view is
>> updateable and create the needed rules for it, also create default
>> values for it by copying up from the base tables"?
>> 
> Well, that was our first thought. but what if the default value is
> changed in the base table?

So?  Being able to have a different default for the view could be
construed as a feature, not a bug.
        regards, tom lane


Re: rewriter in updateable views

From
Stephan Szabo
Date:
On Sat, 19 Mar 2005, Tom Lane wrote:

> Jaime Casanova <systemguards@gmail.com> writes:
> > On Fri, 18 Mar 2005 23:31:26 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Why do you not define the problem as "when we decide a view is
> >> updateable and create the needed rules for it, also create default
> >> values for it by copying up from the base tables"?
> >>
> > Well, that was our first thought. but what if the default value is
> > changed in the base table?
>
> So?  Being able to have a different default for the view could be
> construed as a feature, not a bug.

Except that if the view got its default by copying the base table default,
(or not copying it if there isn't one) it should presumably mirror the
base table's current default.  However, if the view's default was
explicitly set, it should probably ignore base table default changes.


Re: rewriter in updateable views

From
Jaime Casanova
Date:
On Sat, 19 Mar 2005 11:42:18 +0000, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Sat, 2005-03-19 at 01:10 -0500, Jaime Casanova wrote:
> > On Fri, 18 Mar 2005 23:31:26 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > Jaime Casanova <systemguards@gmail.com> writes:
> > > > ... but if we do INSERT INTO vfoo(col2) values ('some_string) the
> > > rewriter
> > > > cann resolv the value for col1. the reason is that views does not
> > > > inherit the defaults of the parent table. That is the reason you add
> > > > the ALTER TABLE  ALTER COLUMN ADD/DROP DEFAULT for views.
> > >
> > > > Ok, this is a problem for us, so we want to improve the rewriter to
> > > > see the default in the base table an add it as appropiate.
> > >
> > > Why do you not define the problem as "when we decide a view is
> > > updateable and create the needed rules for it, also create default
> > > values for it by copying up from the base tables"?
> > >
> > Well, that was our first thought. but what if the default value is
> > changed in the base table? then we have a problem, can we found in
> > what views we have to alter the default value in order to keep
> > consistency.
> 
> I can see that I might want the view to have a different default value
> from that of the underlying table. I can see a reason to have multiple
> updateable views on the same table, all with different columns, column
> defaults and row selection clauses. (Multiple classes all held within
> the same physical table, for example).
> 
> I'd suggest - if the default value for a column on a view IS NOT set,
> then use the default value from the underlying table. If it IS set, then
> it should stay set, even if the underlying table changes. That might
> need some dependency logic in there...
> 
And here is were we thought we have to improve the rewriter, if the
rewriter find a default value for a view it will use it if not it must
look for a default value in the base table.

regards,
Jaime Casanova


Re: rewriter in updateable views

From
Jaime Casanova
Date:
On Sat, 19 Mar 2005 11:05:39 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jaime Casanova <systemguards@gmail.com> writes:
> > On Fri, 18 Mar 2005 23:31:26 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Why do you not define the problem as "when we decide a view is
> >> updateable and create the needed rules for it, also create default
> >> values for it by copying up from the base tables"?
> >>
> > Well, that was our first thought. but what if the default value is
> > changed in the base table?
> 
> So?  Being able to have a different default for the view could be
> construed as a feature, not a bug.
> 
>                         regards, tom lane
> 
We are not against this. As you say this is a feature, but if the view
doesn't have a default value we have to assign something in the
appropiate col in the insert.

ALTER TABLE view_name ALTER COLUMN ADD/DROP DEFAULT is your friend ;)

regards,
Jaime Casanova


Re: rewriter in updateable views

From
Bernd Helmle
Date:
--On Samstag, März 19, 2005 11:05:39 -0500 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

> Jaime Casanova <systemguards@gmail.com> writes:
>> On Fri, 18 Mar 2005 23:31:26 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Why do you not define the problem as "when we decide a view is
>>> updateable and create the needed rules for it, also create default
>>> values for it by copying up from the base tables"?
>>>
>> Well, that was our first thought. but what if the default value is
>> changed in the base table?
>
> So?  Being able to have a different default for the view could be
> construed as a feature, not a bug.
>

As far as i can oversee, we have the following options to handle this:

1.

- Create default values in views inherited by their base tables in the
CREATE VIEW command.

- Extend ALTER TABLE table ... SET DEFAULT ... to track dependencies when
changing default values in base tables. We need to know, when a default
value in a view was overwritten by a user-fired ALTER TABLE view ... SET
DEFAULT, so we need some extra information somewhere. I think the plus of
this implementation is, that we don't touch the rewriter and don't need
extra time on rewriting a query. The negative is that this adds
side-effects to ALTER TABLE ... SET DEFAULT ... when views are involved.

2.

Extend the rewriter (rewriteTargetList()) to derive column default values
from a base table, if the pg_attribute.atthasdef column value is set to
false and the base table has a valid default expression. This adds extra
time when rewriting the target list of a query and we need to reparse the
query tree to find out which base table(s) /columns to look for, if we
don't save extra information somewhere, but we don't have the overhead of
keeping views and base tables in sync....

--
 Bernd


Re: rewriter in updateable views

From
Jaime Casanova
Date:
On Sat, 19 Mar 2005 11:42:18 +0000, Simon Riggs <simon@2ndquadrant.com> wrote:
> I can see that I might want the view to have a different default value
> from that of the underlying table. I can see a reason to have multiple
> updateable views on the same table, all with different columns, column
> defaults and row selection clauses. (Multiple classes all held within
> the same physical table, for example).
> 
> I'd suggest - if the default value for a column on a view IS NOT set,
> then use the default value from the underlying table. If it IS set, then
> it should stay set, even if the underlying table changes. That might
> need some dependency logic in there...
> 
> Best Regards, Simon Riggs
> 
I think i can do this within rewriterHandle.c:build_column_default
immediatly after the first try i can ask if expr == NULL and if is it
a view if  so my code start looking for defaults on base tables maybe
using something like the parse_relation.c:colNameToVar function to
identify the column. there is a better way to do it? also the
ParseState parameter is extensively used in the colNameToVar function
but i can't find what is it.

regards,
Jaime Casanova


Re: rewriter in updateable views

From
Simon Riggs
Date:
On Sun, 2005-03-27 at 23:12 -0500, Jaime Casanova wrote:
> On Sat, 19 Mar 2005 11:42:18 +0000, Simon Riggs <simon@2ndquadrant.com> wrote:
> > I can see that I might want the view to have a different default value
> > from that of the underlying table. I can see a reason to have multiple
> > updateable views on the same table, all with different columns, column
> > defaults and row selection clauses. (Multiple classes all held within
> > the same physical table, for example).
> > 
> > I'd suggest - if the default value for a column on a view IS NOT set,
> > then use the default value from the underlying table. If it IS set, then
> > it should stay set, even if the underlying table changes. That might
> > need some dependency logic in there...
> > 
> I think i can do this within rewriterHandle.c:build_column_default
> immediatly after the first try i can ask if expr == NULL and if is it
> a view if  so my code start looking for defaults on base tables maybe
> using something like the parse_relation.c:colNameToVar function to
> identify the column. there is a better way to do it? also the
> ParseState parameter is extensively used in the colNameToVar function
> but i can't find what is it.

ParseState is defined in src/include/parser/parse_node.h

I'm not sure I can comment further.

If you have everything else working, it might be worth submitting a
patch for review? There may be other things required also.

Best Regards, Simon Riggs



Re: rewriter in updateable views

From
Bernd Helmle
Date:
--On Montag, März 28, 2005 09:51:52 +0100 Simon Riggs
<simon@2ndquadrant.com> wrote:

[...]

>
> If you have everything else working, it might be worth submitting a
> patch for review? There may be other things required also.
>
> Best Regards, Simon Riggs
>

Well, the patch is far away from being ready for -patches, but if you want
to "preview" you can get our latest patch against HEAD at

http://www.oopsware.de/pgsql_viewupdate.html

Critics and Discussion is highly appreciated :)

--
 Bernd


Re: rewriter in updateable views

From
Alvaro Herrera
Date:
On Tue, Mar 29, 2005 at 07:30:13PM +0200, Bernd Helmle wrote:

> Well, the patch is far away from being ready for -patches, but if you want 
> to "preview" you can get our latest patch against HEAD at
> 
> http://www.oopsware.de/pgsql_viewupdate.html

Well, that description there says what the patch doesn't do, but it
would be helpful to say what does it do.

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Un poeta es un mundo encerrado en un hombre" (Victor Hugo)


Re: rewriter in updateable views

From
Bernd Helmle
Date:
--On Mittwoch, März 30, 2005 11:35:05 -0400 Alvaro Herrera
<alvherre@dcc.uchile.cl> wrote:

> On Tue, Mar 29, 2005 at 07:30:13PM +0200, Bernd Helmle wrote:
>
>> Well, the patch is far away from being ready for -patches, but if you
>> want  to "preview" you can get our latest patch against HEAD at
>>
>> http://www.oopsware.de/pgsql_viewupdate.html
>
> Well, that description there says what the patch doesn't do, but it
> would be helpful to say what does it do.

Implemented is:

- Create rules for SQL92-compliant updateable views. This creates implicit
rules for DELETE, UPDATE and INSERT actions named _DELETE, _DELETE_NOTHING, _UPDATE, _UPDATE_NOTHING, _INSERT and
_INSERT_NOTHING

and marks them with a new column in pg_rewrite as an impicit created update
rule.
The patch supports system columns and functions in the view's target list
rudimentary and allows CASCADED updates to base tables, since rules are
created in cascaded manner on the underlying relations (which is changed,
when we cover LOCAL | CASCADED CHECK OPTION). The WITH CHECK OPTION is
implemented and creates all mentioned update rules with a rule condition
added, which calls a new system function that elogs() an error if the
passed view condition is not evaluated to true.
I'm sure not all corner cases are covered for this yet, but it's still a
work in progress....

--
 Bernd