Subject: What's wrong with rules?

From: [email-masked] (Fernando Ariznavarreta)
Date: Sat, 17 Jul 1993 10:46:44 +0100 (MDT)

I'm having problems with rules. I'm trying to use them to simulate a special
king of view.
I'll explain the problem with a simplified example.

First let's create to simple classes.

create A(a= int4)\g
create B(b= int4,aoid= oid)\g

Objetcs in B hold the oid of an object in A in it's aoid field.

let's now create a view of the A objects and their corresponding B objects:

create AB(a= int4, b= int4)\g

define rewrite rule AB_ret
is on retrieve to AB
do instead retrieve (A.a,B.b) where B.aoid= A.oid \g

Let's now try it:

append A(a= 1)\g
append B(b= 1, aoid= A.oid) where A.a= 1 \g
append B(b= 2, aoid= A.oid) where A.a= 1\g

retrieve (AB.all) \g

Postgres answers as expected:
Query sent to backend is "retrieve (AB.all) "
-----------------------------
| a           | b           |
-----------------------------
| 1           | 1           |
-----------------------------
| 1           | 2           |
-----------------------------

Now let's create a rule to make append to B objects using the view:

define rewrite rule AB_a
is on append to AB 
do instead append B(b= current.b,aoid= A.oid) where A.a= current.a \g

Let's try it now:

append AB(b= 3,a= 1)\g

The answer from Postgres is erroneous:
Query sent to backend is "append AB(b= 3,a= 1)"
APPEND 0

If we query the AB view now:
retrieve (AB.all) \g

we get:

Query sent to backend is "retrieve (AB.all) "
-----------------------------
| a           | b           |
-----------------------------
| 1           | 1           |
-----------------------------
| 1           | 2           |
-----------------------------
| 1           | 1           |
-----------------------------
| 1           | 2           |
-----------------------------

Does anybody now what's going on?

If I try to view the rules by making the query:

retrieve (pg_rewrite.all)\g

The backend unexpectedly hangs and never returns.

I tried this using 'define view' with similar results.

I am using a port of Postgres v 4.01 to hpux.
Is this a problem with this release or with the port?

Thanks in advance.

-- 
Fernando Ariznavarreta Fernandez       E-Mail:[email-masked]
Dpto. Explotacion y Prospeccion de Minas     Phone: +34 8 510 4267
School of Mines. University of Oviedo               +34 8 510 4336
Independencia, 13                            Fax:   +34 8 510 4242
Oviedo 33004, SPAIN                                 +34 8 510 4340

---

From: [email-masked] (Paul M. Aoki)
Date: Fri, 17 Sep 93 11:39:31 -0700

> do instead append B(b= current.b,aoid= A.oid) where A.a= current.a \g

i think you're getting hung up in the funky semantics of rewrite rules.
i don't understand them myself so i can't help you.
(could just be a bug.  i stared at the query plan that the rewrite
system produced for a couple of minutes and couldn't figure out why 
the heck it would want to produce two range variables with the same 
name and class oid in the same plan..)

but unless i misunderstand your example, i think
	define rule AB_a
	is on append to AB
	do instead append B(b= new.b, aoid=A.oid) where A.a = new.a
does what you want..
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki