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 ;) ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [hidden email] |
Jaime Casanova <[hidden email]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq |
In reply to this post by Jaime Casanova
Jaime Casanova <[hidden email]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq |
On 5/28/05, Tom Lane <[hidden email]> wrote:
> Jaime Casanova <[hidden email]> 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. > 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 ;) ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [hidden email] so that your message can get through to the mailing list cleanly |
In reply to this post by Greg Stark-3
On 28 May 2005 10:25:48 -0400, Greg Stark <[hidden email]> wrote:
> > Jaime Casanova <[hidden email]> 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. > -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq |
In reply to this post by Jaime Casanova
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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings |
On 6/6/05, Richard Huxton <[hidden email]> 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. > 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 ;) ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq |
Free forum by Nabble | Edit this page |