Re: feature request for Postgresql Rule system.

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