Обсуждение: feature request for Postgresql Rule system.
I didn't see this on the to-do list. Would there be any interest in making rules with multiple sql statements acid compliant? Regards, Richard Broersma Jr.
On Mon, 2006-12-18 at 13:20 -0800, Richard Broersma Jr wrote: > I didn't see this on the to-do list. > > Would there be any interest in making rules with multiple sql statements acid compliant? > They are. Regards, Jeff Davis
> > Would there be any interest in making rules with multiple sql statements acid compliant? > They are. Am I missing something then, becuase I have cases where it is possible to get partial updates from the multi-sql statement rule? I suppose that my understanding of "ACID" actually mean may not be correct. postgres=# select * from vwife; id | name | dresssize ----+---------+----------- 3 | dodie | 13 4 | heather | 10 2 | katie | 11 (3 rows) postgres=# update vwife set name = 'Katheryn', dresssize = 12 where (id,name,dresssize)=(2,'katie',11); UPDATE 0 postgres=# select * from vwife; id | name | dresssize ----+----------+----------- 3 | dodie | 13 4 | heather | 10 2 | Katheryn | 11 ^^^^^^^^ <-- update 0 is false CREATE OR REPLACE RULE vwife_update AS ON UPDATE TO public.vwife DO INSTEAD ( UPDATE public.person SET name = NEW.name WHERE id = OLD.id; UPDATE public.wife SET dresssize = NEW.dresssize WHERE id = OLD.id );
Richard Broersma Jr wrote: > postgres=# update vwife > set name = 'Katheryn', > dresssize = 12 > where (id,name,dresssize)=(2,'katie',11); > UPDATE 0 > > postgres=# select * from vwife; > id | name | dresssize > ----+----------+----------- > 3 | dodie | 13 > 4 | heather | 10 > 2 | Katheryn | 11 > ^^^^^^^^ <-- update 0 is false None of A, C, I, or D say that you need to report a truthful update count. The fact that the update count is wrong with updatable views is a known deficiency. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Mon, 2006-12-18 at 13:42 -0800, Richard Broersma Jr wrote: > > > Would there be any interest in making rules with multiple sql statements acid compliant? > > They are. > > postgres=# update vwife > set name = 'Katheryn', > dresssize = 12 > where (id,name,dresssize)=(2,'katie',11); > UPDATE 0 > > postgres=# select * from vwife; > id | name | dresssize > ----+----------+----------- > 3 | dodie | 13 > 4 | heather | 10 > 2 | Katheryn | 11 > ^^^^^^^^ <-- update 0 is false > > CREATE OR REPLACE RULE vwife_update AS ON UPDATE TO public.vwife > DO INSTEAD > ( > UPDATE public.person > SET name = NEW.name > WHERE id = OLD.id; > > UPDATE public.wife > SET dresssize = NEW.dresssize > WHERE id = OLD.id > ); In "UPDATE #", # is the result of the libpq function PQcmdTuples(), and it refers to the number of tuples affected by the last command executed. What's happening is that the first UPDATE in the rule changes 1 record in public.person, but the second update matches no rows, so that value is 0. That means that the WHERE clause of the second update matches nothing. Are you perhaps using two different id fields, and comparing against the wrong one? This can't be an ACID issue, because ACID has more to do with *when* changes take effect than *whether* changes take effect. In your case the second UPDATE simply does nothing (if there was something wrong, it would error out, it would not be silently ignored). If there's any TODO here, I think it would be to allow you to explicitly set the PQcmdTuples() result (the thing that's returning 0 for you). Regards, Jeff Davis
> None of A, C, I, or D say that you need to report a truthful update > count. > > The fact that the update count is wrong with updatable views is a known > deficiency. I see. However, my my case I would like all of the sql statments in the rule to succeed and if they don't I would want none of them to succeed so that at least my data isn't partially changed. Is there anyway to catch this? Regards, Richard Broersma Jr.
On Mon, 2006-12-18 at 15:06 -0800, Richard Broersma Jr wrote: > > None of A, C, I, or D say that you need to report a truthful update > > count. > > > > The fact that the update count is wrong with updatable views is a known > > deficiency. > > I see. However, my my case I would like all of the sql statments in the rule to succeed and if > they don't I would want none of them to succeed so that at least my data isn't partially changed. > Is there anyway to catch this? > What I was trying to explain is that all of your statements *are* succeeding. A WHERE clause in an UPDATE may match zero or more rows. The second UPDATE in your rule matches zero rows. For instance, you can do an UPDATE like: UPDATE foo SET i = 1 WHERE ( 11 = 12 ); And that statement will succeed, but nothing in foo will change, because eleven is never equal to twelve. Similarly, your second UPDATE is succeeding but not changing anything, thus PQcmdTuples() returns 0, and you see an "UPDATE 0". You need to examine that UPDATE, because it's not doing what you expect. Perhaps you have several int fields in each table, and you're comparing against the wrong one in the WHERE clause? We need to see your table definitions and perhaps some sample content to help you further. Regards, Jeff Davis
> > postgres=# update vwife > > set name = 'Katheryn', > > dresssize = 12 > > where (id,name,dresssize)=(2,'katie',11); > > In "UPDATE #", # is the result of the libpq function PQcmdTuples(), and > it refers to the number of tuples affected by the last command executed. > What's happening is that the first UPDATE in the rule changes 1 record > in public.person, but the second update matches no rows, so that value > is 0. agreed. > That means that the WHERE clause of the second update matches nothing. > Are you perhaps using two different id fields, and comparing against the > wrong one? In this case, the id are that same since wife.id is a foreign key of person.id. The think the problem lies in the where clause of the update statement to the update-able view. where (id,name,dresssize)=(2,'katie',11); If I only specify "where id=2" in the update statement to the view everything works fine and the updates always succeed. However, for some reason, the rule system also takes into account the other redundant fields in the where clause even though I have no such fields defined in the rule's update statements. In this case (name,dresssize)=('katie',11), causes the update to partially succeed and partially fail because the initial update will find ('katie',11) but the second doesn't since one of these values is already changed due to the first update statement in the rule. It would be nice if the rule system could either ignore redundant fields in the where clause from update statements made to an update-able view; Or if all update statements in the update rule could still see the initial state of the view's tuple so that all statements in the rule can successfully find a match in the rule statements' where clauses. <I hope this last sentence is clear.> Regards, Richard Broersma Jr.
> What I was trying to explain is that all of your statements *are* > succeeding. A WHERE clause in an UPDATE may match zero or more rows. The > second UPDATE in your rule matches zero rows. I see, that makes sense. I guess that my confussion was that update 0 was not the same as success. > You need to examine that UPDATE, because it's not doing what you expect. > Perhaps you have several int fields in each table, and you're comparing > against the wrong one in the WHERE clause? We need to see your table > definitions and perhaps some sample content to help you further. Below is my sample table, update-able view and update rule. CREATE TABLE public.person( id integer primary key not null default nextval('public.person_seq'), name varchar(30) unique not null); CREATE TABLE public.wife( id integer primary key references person(id) on delete cascade, dresssize integer not null); CREATE OR REPLACE VIEW public.vwife (id, name, dresssize) AS SELECT A.id, A.name, B.dresssize FROM public.person as A INNER JOIN public.wife as B ON A.id = B.ID; CREATE OR REPLACE RULE vwife_update AS ON UPDATE TO public.vwife DO INSTEAD ( UPDATE public.person SET name = NEW.name WHERE id = OLD.id; UPDATE public.wife SET dresssize = NEW.dresssize WHERE id = OLD.id ); Regards, Richard Broersma Jr.
On Mon, 2006-12-18 at 15:30 -0800, Richard Broersma Jr wrote: > > > postgres=# update vwife > > > set name = 'Katheryn', > > > dresssize = 12 > > > where (id,name,dresssize)=(2,'katie',11); > > > > In "UPDATE #", # is the result of the libpq function PQcmdTuples(), and > > it refers to the number of tuples affected by the last command executed. > > What's happening is that the first UPDATE in the rule changes 1 record > > in public.person, but the second update matches no rows, so that value > > is 0. > agreed. > > > That means that the WHERE clause of the second update matches nothing. > > Are you perhaps using two different id fields, and comparing against the > > wrong one? > > In this case, the id are that same since wife.id is a foreign key of person.id. The think the > problem lies in the where clause of the update statement to the update-able view. > > where (id,name,dresssize)=(2,'katie',11); > Actually, I am seeing some unexpected behavior, or rather behavior that I wouldn't expect. After the first UPDATE in the rule, NEW and OLD are gone. After reading up on the rules document, I think this is happening because the WHERE clause is applied again, and since neither NEW nor OLD satisfy the WHERE clause (because of the first UPDATE), they become non- existent. I am still a little unsure on this topic, would someone else like to comment? It seems almost like NEW and OLD act like views with the outer WHERE clause included, rather than materialized relations. The way to fix this definitively is to pass the OLD/NEW values as arguments to a function, and then they are stored as new values until the end of the function's execution, during which you can run as many UPDATEs as you want. Regards, Jeff Davis
> Actually, I am seeing some unexpected behavior, or rather behavior that > I wouldn't expect. After the first UPDATE in the rule, NEW and OLD are > gone. I guess the end-result behaviour I am looking for (as you mentioned) is having an update-able view behave exactly as if it were a table in regard to update and delete statements. (Delete statements had a similar behavior behaviour, but I got around that problem by using "delete cascade" on the leaf tables.) Regards, Richard Broersma Jr.
On Mon, 2006-12-18 at 17:09 -0800, Richard Broersma Jr wrote: > > Actually, I am seeing some unexpected behavior, or rather behavior that > > I wouldn't expect. After the first UPDATE in the rule, NEW and OLD are > > gone. > > I guess the end-result behaviour I am looking for (as you mentioned) is having an update-able view > behave exactly as if it were a table in regard to update and delete statements. (Delete > statements had a similar behavior behaviour, but I got around that problem by using "delete > cascade" on the leaf tables.) > Try: CREATE OR REPLACE FUNCTION upd_func(INT,TEXT,INT) RETURNS VOID LANGUAGE sql AS $upd_func$ UPDATE public.person SET name = $2 WHERE id = $1; UPDATE public.wife SET dresssize = $3 WHERE id = $1; $upd_func$; CREATE OR REPLACE RULE vwife_update AS ON UPDATE TO public.vwife DO INSTEAD SELECT upd_func(OLD.id,NEW.name,NEW.dresssize); The difference here is that by passing the values into a function, it creates a copy of the value, meaning it won't change due to an UPDATE. The only negative of using a function is that the number of affected tuples will always be zero. Thanks for bringing this topic up... it made me understand the rule system much better than I did before. I think I'll have to read through that document a few more times. Regards, Jeff Davis
> The difference here is that by passing the values into a function, it > creates a copy of the value, meaning it won't change due to an UPDATE. > The only negative of using a function is that the number of affected > tuples will always be zero. The function and rule does indeed work from the psql command line. But as you mentioned, the "UPDATE 0" makes my client frontend thinks that the update failed. So it ends up rolling the transaction that contained the initial update statement to the view. However, the advantage with this solution is that I know I don't get inconsistant updates, only I don't get any update either.:-) There is a way to get an updateable view to work just as long as I join no more than two relations using the leaf table's ctid. This works just fine for two tables, but in my case I can have as mean as 4 joined tables. The work around is to create a view of two tables and then recursively join the remaining tables one at a time. The problem here is the excessive amount of coding involved and a potential hit in preformace in selecting from the view since the optimizer cannot use a bit-map-index scan in a multinested view. For this reason, I don't like this solution very much. This is a link to an email that illistrates this test case: http://archives.postgresql.org/pgsql-general/2006-12/msg00913.php By the way thanks for responding, very few people show interest in this topic. Regards, Richard Broersma Jr.
> > postgres=# update vwife > > set name = 'Katheryn', > > dresssize = 12 > > where (id,name,dresssize)=(2,'katie',11); > > UPDATE 0 > > postgres=# select * from vwife; > > id | name | dresssize > > ----+----------+----------- > > 2 | Katheryn | 11 <- DRESSSIZE should not be 12 correct? > > ^^^^^^^^ <-- update did change name but not dresssize? > None of A, C, I, or D say that you need to report a truthful update > count. > The fact that the update count is wrong with updatable views is a known > deficiency. Peter sorry for belaboring this point, but I just wanted to verify if what I am seeing is already know (whether it is a deficiency or not). My concern regarding the rule system is not related to the incorrect update count but the fact that my update statement was suppose to change BOTH name AND dresssize. However, as you see only the name was changed, dresssize remains unchanged. Therefore, I assumed that the update statement was not completed "atomically". Regards, Richard Broersma Jr.
Richard Broersma Jr <rabroersma@yahoo.com> writes: > My concern regarding the rule system is not related to the incorrect > update count but the fact that my update statement was suppose to > change BOTH name AND dresssize. However, as you see only the name was > changed, dresssize remains unchanged. Therefore, I assumed that the > update statement was not completed "atomically". At no point did you show us details, but I suppose that this rule is relying on a join view? Once you update one side of the join with a different join key value, the join row in question no longer exists in the view ... so the second update doesn't find a row to update. This has nothing to do with ACID. regards, tom lane
> At no point did you show us details, but I suppose that this rule is > relying on a join view? Yes, the view is a join between two tables as a test case. I provided the details of my test case below. However, I could see the use of joining as many as four tables in an updatable view. > Once you update one side of the join with a > different join key value, the join row in question no longer exists in > the view ... so the second update doesn't find a row to update. This > has nothing to do with ACID. I see, ACID wasn't the correct word choice to use. I realize that the rule system can have many uses and allowing views to become updatable is just one of its many uses. But if a view is going to be updatable, shouldn't behave exactly as a table would to at least for single tuple insert, update, and delete statements? Regards, Richard Broersma Jr. -- Table Definitions CREATE SEQUENCE public.person_seq INCREMENT BY 1 START WITH 1; CREATE TABLE public.person ( id integer primary key not null default nextval('public.person_seq'), name varchar(30) unique not null); ALTER SEQUENCE public.person_seq OWNED BY public.person.id; CREATE TABLE public.husband ( id integer primary key references person(id) on delete cascade, tiesize integer not null); CREATE TABLE public.wife ( id integer primary key references person(id) on delete cascade, dresssize integer not null); -- view definitions CREATE OR REPLACE VIEW public.vwife (id, name, dresssize) AS SELECT A.id, A.name, B.dresssize FROM public.person as A INNER JOIN public.wife as B ON A.id = B.ID; CREATE OR REPLACE RULE vwife_insert AS ON INSERT TO public.vwife DO INSTEAD ( INSERT INTO public.person ( id, name ) VALUES ( DEFAULT, NEW.name); INSERT INTO public.wife ( id, dresssize ) VALUES ( currval('public.person_seq'), NEW.dresssize ) ); CREATE OR REPLACE RULE vwife_update AS ON UPDATE TO public.vwife DO INSTEAD ( UPDATE public.person SET name = NEW.name WHERE id = OLD.id; UPDATE public.wife SET dresssize = NEW.dresssize WHERE id = OLD.id ); CREATE OR REPLACE RULE vwife_delete AS ON DELETE TO public.vwife DO INSTEAD ( DELETE FROM public.person WHERE id = OLD.id ); CREATE OR REPLACE VIEW public.vhusband (id, name, tiesize) AS SELECT A.id, A.name, B.tiesize FROM public.person as A INNER JOIN public.husband as B ON A.id = B.ID; CREATE OR REPLACE RULE vhusband_insert AS ON INSERT TO public.vhusband DO INSTEAD ( INSERT INTO public.person ( id, name ) VALUES ( DEFAULT, NEW.name); INSERT INTO public.husband ( id, tiesize ) VALUES ( currval('public.person_seq'), NEW.tiesize) ); CREATE OR REPLACE RULE vhusband_update_person AS ON UPDATE TO public.vhusband DO INSTEAD ( UPDATE public.person SET name = NEW.name WHERE id = OLD.id; UPDATE public.husband SET tiesize = NEW.tiesize WHERE id = OLD.id ); CREATE OR REPLACE RULE vhusband_delete AS ON DELETE TO public.vhusband DO INSTEAD ( DELETE FROM public.person WHERE id = OLD.id );
On Tue, 19 Dec 2006 07:22:36 -0800 (PST), Richard Broersma Jr <rabroersma@yahoo.com> wrote: >> Once you update one side of the join with a >> different join key value, the join row in question no longer exists in >> the view ... so the second update doesn't find a row to update. This >> has nothing to do with ACID. > I see, ACID wasn't the correct word choice to use. I realize that the > rule system can have many > uses and allowing views to become updatable is just one of its many uses. > But if a view is going > to be updatable, shouldn't behave exactly as a table would to at least for > single tuple insert, > update, and delete statements? I've already thought about that while dealing with auto-updatable views. You'd have to provide a view update rule with a consistent "snapshot" on your view data during update while updating the underlying base tables. I haven't dug into this deeper since i was concentrated on updatable views on single base tables only. However, the rule system already adds the CTID TE to the query tree and it looks to me this can be extended to provide the requested behavior. The way the rewriter handles it's query qualifications would have to be redesigned as well, i think, but i don't know what can of worms there are, too.... Bernd
> However, the rule system already > adds the CTID TE to the query tree and it looks to me this can be extended to provide > the requested behavior. The way the rewriter handles it's query qualifications would have to > be redesigned as well, i think, but i don't know what can of worms there are, too.... True, and it works well enough for a view joining two tables. However, if you want to join more than two tables in a view you have to create additional Nested Views one for each joined child table in order to cascade updates down to each of these tables. This way each child table's ctid can be directly referenced in it own view so there is no ambiguity. As you can imagine, creating these additional updatable views with associated rules for cascading updates requires a voluminous amount of DDL code. At least this is what my current experimenting has lead me to believe. Perhaps there is a better logical model with can achieve what I want from an updatable view without the additional overhead that I am creating. Any suggests or corrections on this topic are very much welcomed. The advantage on the otherhand with method this of-course is that the finial result is a view that appears to preform exactly like a table for single tuple insert, update, and delete statements. My feature request is based on the assertion that single updatable view on multiple tables joined with simply an inner join based on surrogate integer primary keys shouldn't require the additional overhead that I am now creating. Regards, Richard Broersma Jr.