Re: 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(at)yahoo(dot)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(at)yahoo(dot)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.