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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster