Thread: rewriter in updateable views
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
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
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
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
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
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.
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
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
--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
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
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
--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
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)
--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