Thread: rules and default values
Hi all, In IRC, "StuckMojo" commented that the following behavior doesn't seem to be ideal: nconway=> create table my_table (col1 int default 5, col2 int default 10); CREATE nconway=> create view my_view (col1, col2) as select * from my_table; CREATE nconway=> create rule insert_rule as on insert to my_view do instead insert into my_table values (new.*); CREATE nconway=> insert into my_table default values; INSERT 112714 1 nconway=> insert into my_view default values; INSERT 0 0 nconway=> select * from my_table;col1 | col2 ------+------ 5 | 10 | (2 rows) In other words, when the insert statement on the view is transformed by the rule, the "default value" columns are replaced by explicit NULL values (which is the default value for the columns of the pseudo-table created by CREATE VIEW). Is this the correct behavior? Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
nconway@klamath.dyndns.org (Neil Conway) writes:
> In other words, when the insert statement on the view is transformed by
> the rule, the "default value" columns are replaced by explicit NULL
> values (which is the default value for the columns of the pseudo-table
> created by CREATE VIEW). Is this the correct behavior?
It's correct, from the point of view of the rule rewriter, but that
doesn't make the behavior useful.
What'd make sense to me is to allow defaults to be attached to the
view columns, say by doing ALTER TABLE ADD DEFAULT on the view.
Unfortunately that won't do much in the current implementation,
because such defaults will never get applied (the planner certainly
won't see them as applicable).
Maybe inserting defaults should be the first phase of rewriting, just
before rule substitution, rather than being left to the planner as it
is now. We took it out of the parser for good reasons, but perhaps
we moved it too far downstream.
regards, tom lane
Awhile back I said:
> nconway@klamath.dyndns.org (Neil Conway) writes:
>> In other words, when the insert statement on the view is transformed by
>> the rule, the "default value" columns are replaced by explicit NULL
>> values (which is the default value for the columns of the pseudo-table
>> created by CREATE VIEW). Is this the correct behavior?
> It's correct, from the point of view of the rule rewriter, but that
> doesn't make the behavior useful.
> What'd make sense to me is to allow defaults to be attached to the
> view columns, say by doing ALTER TABLE ADD DEFAULT on the view.
> Unfortunately that won't do much in the current implementation,
> because such defaults will never get applied (the planner certainly
> won't see them as applicable).
> Maybe inserting defaults should be the first phase of rewriting, just
> before rule substitution, rather than being left to the planner as it
> is now. We took it out of the parser for good reasons, but perhaps
> we moved it too far downstream.
I recently moved the default-insertion phase to fix a different bug,
so this is now possible. Given the attached patch, it actually works.
However I have not applied the patch because it needs (a) pg_dump
support and (b) documentation, neither of which I have time for at the
moment. Anyone want to pick up the ball?
regards, tom lane
Demonstration of defaults for views (with patch):
regression=# create table foo (f1 int);
CREATE
regression=# create view vv as select * from foo;
CREATE
regression=# create rule vvi as on insert to vv do instead
regression-# insert into foo select new.*;
CREATE
regression=# insert into vv default values;
INSERT 0 0
regression=# select * from vv;f1
----
(1 row)
regression=# alter table vv alter column f1 set default 42;
ALTER
regression=# insert into vv default values;
INSERT 0 0
regression=# select * from vv;f1
----
42
(2 rows)
*** src/backend/commands/tablecmds.c~ Mon Apr 15 01:22:03 2002
--- src/backend/commands/tablecmds.c Mon Apr 15 14:16:58 2002
***************
*** 622,629 **** rel = heap_open(myrelid, AccessExclusiveLock);
! if (rel->rd_rel->relkind != RELKIND_RELATION)
! elog(ERROR, "ALTER TABLE: relation \"%s\" is not a table", RelationGetRelationName(rel));
if(!allowSystemTableMods
--- 622,635 ---- rel = heap_open(myrelid, AccessExclusiveLock);
! /*
! * We allow defaults on views so that INSERT into a view can have
! * default-ish behavior. This works because the rewriter substitutes
! * default values into INSERTs before it expands rules.
! */
! if (rel->rd_rel->relkind != RELKIND_RELATION &&
! rel->rd_rel->relkind != RELKIND_VIEW)
! elog(ERROR, "ALTER TABLE: relation \"%s\" is not a table or view",
RelationGetRelationName(rel)); if (!allowSystemTableMods
On Mon, 15 Apr 2002 14:25:28 -0400 "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > Awhile back I said: > > nconway@klamath.dyndns.org (Neil Conway) writes: > >> In other words, when the insert statement on the view is transformed by > >> the rule, the "default value" columns are replaced by explicit NULL > >> values (which is the default value for the columns of the pseudo-table > >> created by CREATE VIEW). Is this the correct behavior? > > > It's correct, from the point of view of the rule rewriter, but that > > doesn't make the behavior useful. > > > What'd make sense to me is to allow defaults to be attached to the > > view columns, say by doing ALTER TABLE ADD DEFAULT on the view. > > Unfortunately that won't do much in the current implementation, > > because such defaults will never get applied (the planner certainly > > won't see them as applicable). > > > Maybe inserting defaults should be the first phase of rewriting, just > > before rule substitution, rather than being left to the planner as it > > is now. We took it out of the parser for good reasons, but perhaps > > we moved it too far downstream. > > I recently moved the default-insertion phase to fix a different bug, > so this is now possible. Given the attached patch, it actually works. Great! > However I have not applied the patch because it needs (a) pg_dump > support and (b) documentation, neither of which I have time for at the > moment. Anyone want to pick up the ball? Sure, I'll do this stuff. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC