thw rewriter and default values, again
Hi, here we go again...
As you know there is a problem executing something like:
create table foo (
col1 serial,
col2 int
);
create view v_foo as select * from foo;
create rule ins_rule as on insert to v_foo do instead
insert into foo(col1, col2) values (new.col1, new.col2);
insert into v_foo(col2) values (1);
this give an error like:
psql:f:/views.sql:13: ERROR: null value in column "col1" violates
not-null constraint
----
There is a workaround about this creating default values to the view.
Now, for updateable views we need this happen automatically, attached
there is a solution to this.
The only problem i have found until now is that
update v_foo set col1 = DEFAULT; execute nextval twice per every record.
so there will be a gasp between numbers, but AFAIK nextval has no guarantee
of returning sequential numbers.
Any comments on this?
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
Attachments:
rewriteHandler.patchtext/plain; name=rewriteHandler.patchDownload
*** ../pgsql_head/src/backend/rewrite/rewriteHandler.c 2005-05-27 02:38:08.000000000 -0500
--- src/backend/rewrite/rewriteHandler.c 2005-05-27 14:36:58.000000000 -0500
***************
*** 376,381 ****
--- 376,389 ----
new_expr = build_column_default(target_relation, attrno);
/*
+ * I will do this only in case of relkind == RELKIND_VIEW.
+ * This is the last attempt to get a value for new_expr before we
+ * consider that new_expr must be NULL.
+ */
+ if (!new_expr && target_relation->rd_rel->relkind == RELKIND_VIEW)
+ new_expr = (Expr *) makeNode(SetToDefault);
+
+ /*
* If there is no default (ie, default is effectively NULL),
* we can omit the tlist entry in the INSERT case, since the
* planner can insert a NULL for itself, and there's no point
Jaime Casanova <systemguards@gmail.com> writes:
The only problem i have found until now is that
update v_foo set col1 = DEFAULT; execute nextval twice per every record.
so there will be a gasp between numbers, but AFAIK nextval has no guarantee
of returning sequential numbers.
While there's no guarantee that strong there's still an expected behaviour.
sequences generate sequential numbers and only skip in specific cases.
I think this would still surprise and bother most users.
--
greg
Jaime Casanova <systemguards@gmail.com> writes:
create rule ins_rule as on insert to v_foo do instead
insert into foo(col1, col2) values (new.col1, new.col2);
insert into v_foo(col2) values (1);
this give an error like:
psql:f:/views.sql:13: ERROR: null value in column "col1" violates
not-null constraint
That's not a bug, and "fixing" it isn't acceptable.
The correct solution to the problem you are looking at is
to attach default expressions to the view itself. Adding
alter table v_foo alter col1 set default nextval('public.foo_col1_seq');
to your example makes it work as you wish.
regards, tom lane
On 5/28/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jaime Casanova <systemguards@gmail.com> writes:
create rule ins_rule as on insert to v_foo do instead
insert into foo(col1, col2) values (new.col1, new.col2);insert into v_foo(col2) values (1);
this give an error like:
psql:f:/views.sql:13: ERROR: null value in column "col1" violates
not-null constraintThat's not a bug, and "fixing" it isn't acceptable.
The correct solution to the problem you are looking at is
to attach default expressions to the view itself. Addingalter table v_foo alter col1 set default nextval('public.foo_col1_seq');
to your example makes it work as you wish.
I know you're right, but -when dealing with updateable views- doing
that implies to add a lot of time altering views when base table
change, and of course we maybe don't want all views get that values.
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
On 28 May 2005 10:25:48 -0400, Greg Stark <gsstark@mit.edu> wrote:
Jaime Casanova <systemguards@gmail.com> writes:
The only problem i have found until now is that
update v_foo set col1 = DEFAULT; execute nextval twice per every record.
so there will be a gasp between numbers, but AFAIK nextval has noguarantee
of returning sequential numbers.
While there's no guarantee that strong there's still an expected behaviour.
sequences generate sequential numbers and only skip in specific cases.I think this would still surprise and bother most users.
Certainly, i will look deeper in it.
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
Jaime Casanova wrote:
I know you're right, but -when dealing with updateable views- doing
that implies to add a lot of time altering views when base table
change, and of course we maybe don't want all views get that values.
Sorry for the late posting on this thread.
Might there be any way to have something like ...SET DEFAULT
pg_same_as('public','foo','col1').
Hmm - you'd need pg_same_as_int/text/etc. depending on the return-type -
still not ideal.
--
Richard Huxton
Archonet Ltd
On 6/6/05, Richard Huxton <dev@archonet.com> wrote:
Jaime Casanova wrote:
I know you're right, but -when dealing with updateable views- doing
that implies to add a lot of time altering views when base table
change, and of course we maybe don't want all views get that values.Sorry for the late posting on this thread.
Might there be any way to have something like ...SET DEFAULT
pg_same_as('public','foo','col1').Hmm - you'd need pg_same_as_int/text/etc. depending on the return-type -
still not ideal.
Actually, i try to do that but i need the function to be polymorphic
and because polymorphic functions needs to receive at least one
polymorphic argument that teach about the return type of the function
i can't go ahead with this idea.
About the code i talk earler in this thread i solve the problem with
the update to serial columns.
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)