Message-Id: <9111041850.AA04080@postgres.Berkeley.EDU>
From: Peter van Oosterom <Peter.van.Oosterom@tnofel.fel.tno.nl>
Subject: Re: oid's Subject: Re: oid's
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
To: postgres@postgres.berkeley.edu
Date: Mon, 4 Nov 91 19:53:07 MET

Forwarded message:
>From pjmg1 Mon Nov  4 19:45:15 1991
Subject: Re: oid's
To: postarch@postgres.Berkeley.EDU (Postgres Mailing Archive)
Date: Mon, 4 Nov 91 19:45:15 MET
In-Reply-To: <9111041648.AA02008@postgres.Berkeley.EDU>; from "Postgres Mailing Archive" at Nov 4, 91 8:48 am
X-Mailer: ELM [version 2.3 PL11]

Dear Postgres-group,

Thanks for answering my oid-questions.

> > First, would it be possible to show the oid of a new instance in the
> > "return"-string.(e.g. APPEND-54321 with 54321 the oid of the new instance).
> > I assume that the monitor shows the same string as
> > returned by the function "char* PQexec(query)" in libpq.
> > There are some (rare multi-user) cases in which it would be
> > impossible to obtain the oid of this instance in any other way.
> 
> I had thought this might be fairly straightforward and useful to provide.
> However, before we try to decide what to implement
> i would be interested to hear the multi-user situation you cite to get a
> feel for how general or specific the problem really is.

The reason for asking was not the "rare multi-user situation", but most
of the time our application seems to need the "handle" (oid) of an new
instance after it was inserted. Therefore an append is often followed by
a retrieve of the same instance (based on a unique combination of attributes
of this instance). This could be implemented more efficiently if we get the
oid as a return-value of the append (we only have to perform one database
access instead of two). However, if there does not exist a unique
combination of attributes (this may be the case in an "oo-dbms"), then the
retrieve may not provide the answer for our question (the oid of the
new tuple... oops instance). You could try to use the insert time(system
attribute) for this, but if multiple users do appends then you have the
same problem. Perhaps you could use the xmin/cmin system attributes (id
of inserting transaction and command id within transaction), but this seems
a little difficult for our simple (and common) question. 

> >Second, would it be possible to set the oid of a new instance
> >before the rules triggered by this append are executed.
> >A small example:
> >   create c1 ( attr = int4)\g
> >   create c2 ( father = oid)\g
> >   define rule add is
> >      on append to c1
> >      do append c2 ( father = new.oid )\g
> >Now we only do explicit appends to c1:
> >   append c1 ( attr = 2 )\g
> >The father attribute of c2 is set to 0 and not to the oid of the new c1.
> >However, we have several useful applicaions for these types of rules.
> >Could you give any comments on these issues?
> >
> >Regards, Peter van Oosterom.
> 
> I am afraid that there is no straightforward solution to this problem.
> The rules are invoked before an oid is assigned to the new tuple.
> Although this can be easily (??? famous last words...) fixed by hacking
> the code a little bit, there is nothing a user can do about.

Still, I would appreciate if you could do that :-)
The work-around you propose is clear (however I do not like the extra my-oid
if there is already a system oid) and more elegant than the one we
currently use. This is based on inserting everything twice: first append to the
original table, then the same append to a shadow table:

create c1copy ( attr = int4)\g
define rule add2 is
  on append to c1copy
  do instead append c2 (father = f.oid) from f in c1 where f.attr = new.attr\g

However, the user must be aware of this and do two appends (performance)
and there must a unique attribute (or combination). Again!

Kind regards, Peter van Oosterom.