Hi,
Is there any way to obtain an OID of record just inserted by SPI_execp?

Thanks in advance, 
Andriy Korud, Lviv, Ukraine 


1 Nov 1999 19:10:56 +0200
"Andrij Korud" <akorud@polynet.lviv.ua>
Get OID of just inserted record


On 1 Nov 1999, Andrij Korud wrote:

> Hi,
> Is there any way to obtain an OID of record just inserted by SPI_execp?
> 

SELECT max(oid) ...   which is not implement now :-) 

If is any way for this is prabably good idea add this to SPI API (as
SPI_oidStatus()). What?

						Karel

------------------------------------------------------------------------------
Karel Zak <zakkr@zf.jcu.cz>                      http://home.zf.jcu.cz/~zakkr/

Kim Project:  http://home.zf.jcu.cz/~zakkr/kim/              (process manager)
FTP:          ftp://ftp2.zf.jcu.cz/users/zakkr/              (C/ncurses/PgSQL)
------------------------------------------------------------------------------


Mon, 1 Nov 1999 18:39:09 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] Get OID of just inserted record
>
> Hi,
> Is there any way to obtain an OID of record just inserted by SPI_execp?

    How  should  that  work  consistenty?  What  do you expect as
    return if the query executed was an

        INSERT INTO t2 SELECT * FROM t1;

    The first, a random one or the last of the two  million  rows
    inserted?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Tue, 2 Nov 1999 02:05:48 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Get OID of just inserted record


On Tue, 2 Nov 1999, Jan Wieck wrote:

> >
> > Hi,
> > Is there any way to obtain an OID of record just inserted by SPI_execp?
> 
>     How  should  that  work  consistenty?  What  do you expect as
>     return if the query executed was an
> 
>         INSERT INTO t2 SELECT * FROM t1;
> 
>     The first, a random one or the last of the two  million  rows
>     inserted?
> 
> 
My question is:
"CREATE TABLE t1 (word text)"
"INSERT INTO t1 VALUES('xxx')" (using SPI_execp)

So, is there any way to obtain OID of word 'xxx' just after insertion
without doing "SELECT oid FROM t1 WHERE word='xxx'"?

Thanks in advance,
Andriy Korud, Lviv, Ukraine



2 Nov 1999 09:15:14 +0200
"Andrij Korud" <akorud@polynet.lviv.ua>
Re: [HACKERS] Get OID of just inserted record
hi...

> My question is:
> "CREATE TABLE t1 (word text)"
> "INSERT INTO t1 VALUES('xxx')" (using SPI_execp)
> 
> So, is there any way to obtain OID of word 'xxx' just after insertion
> without doing "SELECT oid FROM t1 WHERE word='xxx'"?

i've been watching this thread and it has caused this thought rumble forth:

would it be possible to add a RETURN clause to INSERT? e.g.

INSERT into t1 VALUES('xxx') RETURN oid;

i could see where this would be useful in many different circumstances.. i
know this isn't standards compliant, but would be very cool =) i know that with
triggers, you have access to the current/old/new information, could this be
harnessed to supply a RETURN facility?

just a thought.. probably ignorable.

-- 
Aaron J. Seigo
Sys Admin

Tue, 2 Nov 1999 11:13:40 -0700
"Aaron J. Seigo" <aaron@gtv.ca>
Re: [HACKERS] Get OID of just inserted record
On Tue, 2 Nov 1999, Aaron J. Seigo wrote:

> would it be possible to add a RETURN clause to INSERT? e.g.
> 
> INSERT into t1 VALUES('xxx') RETURN oid;
> 
> i could see where this would be useful in many different circumstances.. i
> know this isn't standards compliant, but would be very cool =) i know that with
> triggers, you have access to the current/old/new information, could this be
> harnessed to supply a RETURN facility?

I'm not sure what I'm missing here:

=> insert into foo values (4, 'aaa');
INSERT 7998067 1

This line is generated by libpq's PQcmdStatus(). You can also just get the
oid part by using PQoidStatus(). Is that what you wanted or do you need a
wrapper or binding for a certain environment?

	-Peter

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Tue, 2 Nov 1999 19:48:41 +0100 (MET)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: [HACKERS] Get OID of just inserted record
"Aaron J. Seigo" <aaron@gtv.ca> writes:
> would it be possible to add a RETURN clause to INSERT? e.g.
>
> INSERT into t1 VALUES('xxx') RETURN oid;

Not necessary --- the backend already does return the OID of the
inserted tuple (if just one is inserted).  You can see it in psql,
for example.  The problem here is just that not all frontend libraries
make it possible to get at that value :-(.

			regards, tom lane

Tue, 02 Nov 1999 14:14:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Get OID of just inserted record 
hi...

> > i could see where this would be useful in many different circumstances.. i
> > know this isn't standards compliant, but would be very cool =) i know that with
> > triggers, you have access to the current/old/new information, could this be
> > harnessed to supply a RETURN facility?
> 
> I'm not sure what I'm missing here:
> 
> => insert into foo values (4, 'aaa');
> INSERT 7998067 1
> 
> This line is generated by libpq's PQcmdStatus(). You can also just get the
> oid part by using PQoidStatus(). Is that what you wanted or do you need a
> wrapper or binding for a certain environment?
> 
> 	-Peter

this assumes that one is using libpq.. it would be nice to have access to this
from psql or anywhere for that matter.. and not just oids.. but, say for
instance, default values in tables that are generated dynamically... etc

-- 
Aaron J. Seigo
Sys Admin

Tue, 2 Nov 1999 12:39:54 -0700
"Aaron J. Seigo" <aaron@gtv.ca>
Re: [HACKERS] Get OID of just inserted record
On Nov 2, Aaron J. Seigo mentioned:

> > => insert into foo values (4, 'aaa');
> > INSERT 7998067 1
> > 
> > This line is generated by libpq's PQcmdStatus(). You can also just get the
> > oid part by using PQoidStatus(). Is that what you wanted or do you need a
> > wrapper or binding for a certain environment?
> > 
> > 	-Peter
> 
> this assumes that one is using libpq.. it would be nice to have access to this
> from psql or anywhere for that matter.. and not just oids.. but, say for

You can access it right there :) How exactly do you wish to access it in
psql though? (I'm writing around in psql at the moment, so I might
actually implement it!)

> instance, default values in tables that are generated dynamically... etc

Well, now you're saying "I want all this complex data from the database
but I don't want to use SELECT". That does make much sense. The point of
defaults is that you don't need to worry about them. If you need to read
back a record right after you insert it, perhaps you should rethink your
application. Admittedly, I know of several interfaces that make this sort
of thing a royal pain, but you can't get everything for free.

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Tue, 2 Nov 1999 22:29:00 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] Get OID of just inserted record
Aaron J. Seigo wrote:

> > This line is generated by libpq's PQcmdStatus(). You can also just get the
> > oid part by using PQoidStatus(). Is that what you wanted or do you need a
> > wrapper or binding for a certain environment?
> >
> >  -Peter
>
> this assumes that one is using libpq.. it would be nice to have access to this
> from psql or anywhere for that matter.. and not just oids.. but, say for
> instance, default values in tables that are generated dynamically... etc

    Where  should I place the information about the final queries
    the  rule  system  changed  the  original  one  into?  During
    rewrite,   one   INSERT   could  be  rewritten  into  several
    different, conditional INSERT, UPDATE and DELETE  statements.
    I think this would be of interest for you too!

    I'm  not serious right now (as the ppl knowing me should have
    seen already between the lines).  I  can  see  the  point  of
    getting  the last inserted OID, but I absolutely don't see it
    on something like generated default values or the like.  This
    would  finally  mean,  that an INSERT returns a result set of
    the values it inserted. And the same then must happen (to  be
    consistent)  for  UPDATE  and  DELETE  statements,  where the
    UPDATE returns pairs of OLD/NEW rows and DELETE reports which
    rows got deleted.  All this data has to be sent to the client
    (to be thrown away usually).


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Wed, 3 Nov 1999 02:10:28 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Get OID of just inserted record
Thus spake Aaron J. Seigo
> > => insert into foo values (4, 'aaa');
> > INSERT 7998067 1
> 
> this assumes that one is using libpq.. it would be nice to have access to this
> from psql or anywhere for that matter.. and not just oids.. but, say for
> instance, default values in tables that are generated dynamically... etc

Just to see if I understand you, is this what you want to be able to do?

UPDATE t1 SET other_oid =
    (INSERT INTO t2 VALUES (1, 'aaa') RETURN OID)
    WHERE someting = 'something';

or

SELECT (INSERT INTO t2 (f1, f2) VALUES (1, 'aaa') RETURN f3);

In other words, sub-inserts.  It is kind of a neat idea.  I don't know
that it is worth spending much time on but it would be a neat feature
that no one else has.

Just wondering, how would you handle insert only tables?  That is, you
have insert privleges but not select.  Would you still return the field
or fields requested surprising the database designer, accept the insert
but return an error or refuse the insert entirely since the task could
not be completed?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Tue, 2 Nov 1999 22:04:06 -0500 (EST)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] Get OID of just inserted record
hi...

> Just to see if I understand you, is this what you want to be able to do?
> 
> UPDATE t1 SET other_oid =
>     (INSERT INTO t2 VALUES (1, 'aaa') RETURN OID)
>     WHERE someting = 'something';
> 
> or
> 
> SELECT (INSERT INTO t2 (f1, f2) VALUES (1, 'aaa') RETURN f3);
> 
> In other words, sub-inserts.  It is kind of a neat idea.  I don't know
> that it is worth spending much time on but it would be a neat feature
> that no one else has.

both actually, though the former has much greater interest than the latter in
terms of implications... the second example allows a streamlining of tasks (not
having to do a select on a newly inserted piece of data, therefore cutting down
on statements and perhaps even back-end processing)... the first example
though, would only be possible otherwise with several lines of code, and if it
is viewed as an implicit mini-transaction (see below) then it would add
some rather new functionality.

 the reason this sparked in my head originaly was that the fellow who posted
the first question was wondering about output (select) from an input function
(insert) .. i started thinking about it ... having statements able to operate
both ways (input/output) simultaneously would be quite nice, especially from a
power user's point of view... intuitively it makes sense (to me anyways =) and
would allow more complex tasks to be handled with less
code/statements/processing

> Just wondering, how would you handle insert only tables?  That is, you
> have insert privleges but not select.  Would you still return the field
> or fields requested surprising the database designer, accept the insert
> but return an error or refuse the insert entirely since the task could
> not be completed?

i think the task should be refused in entirety so as not to cause unexpected
results. performing insert/select tasks would require more permissions to
the system in general than someone just wanting to do an insert, but that is not
unusual in any way and should be expected... 

further, if any part of the query broke, the entire thing should fail... it
should act as an implicit mini-transaction, consisting of exactly one
statement... so that if a piece of it failed, any and all remaining parts (outer
'loops') of the query are not processed and any previous parts (inner 'loops')
are rolled-back.. and of course an error would come spittering forth. the
implications this holds towards data integrity and conglomerating/atomizing
changes to the dataset are obvious.

as you mentioned, i haven't seen this anywhere else, ... how
much use would it get? well. i know i'd use it if it were available.. i use
triggers/rules, procedures and external code to do what i need now.. so
"sub-inserts" (as you aptly called them) wouldn't really push the bounds of
what is possible, but i think they would push the bounds of what is easily and
dependly possible. 

my 0.02 (and that's canadian.. so..)

-- 
Aaron J. Seigo
Sys Admin

Tue, 2 Nov 1999 21:47:04 -0700
"Aaron J. Seigo" <aaron@gtv.ca>
Re: [HACKERS] Get OID of just inserted record
Thus spake Aaron J. Seigo
> > Just wondering, how would you handle insert only tables?  That is, you
> > have insert privleges but not select.  Would you still return the field
> > or fields requested surprising the database designer, accept the insert
> > but return an error or refuse the insert entirely since the task could
> > not be completed?
> 
> i think the task should be refused in entirety so as not to cause unexpected
> results. performing insert/select tasks would require more permissions to
> the system in general than someone just wanting to do an insert, but that is not
> unusual in any way and should be expected... 

Exactly.  The reason I ask my question is that in PyGreSQL I already fake
this behaviour by doing a select * immediately after an insert and if it
succeeds I load the caller's dictionary with the data so that they have
the oid and any triggered or defaulted fields.  This function would be
useful for me except that I have to be able to deal with tables with
insert only access and still let the insert go through.  My problem is
that it is a generic function so I can't hard code the decision and need
to have some way to check each time.

> as you mentioned, i haven't seen this anywhere else, ... how
> much use would it get? well. i know i'd use it if it were available.. i use
> triggers/rules, procedures and external code to do what i need now.. so
> "sub-inserts" (as you aptly called them) wouldn't really push the bounds of
> what is possible, but i think they would push the bounds of what is easily and
> dependly possible. 

I hope we also allow the following if we do it.

INSERT INTO foo VALUES (1, 'aaa') RETURN f1, f2;

or

INSERT INTO foo VALUES (1, 'aaa') RETURN *;

> my 0.02 (and that's canadian.. so..)

Dollarettes?
Dollar Lite?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Wed, 3 Nov 1999 07:10:23 -0500 (EST)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] Get OID of just inserted record
hi...

>Well, autocommit would only matter if it was decided that it wasn't an atomic
>transaction.  If, as seems both sensible and consensed (look, I made up another
>word :-) the transaction should be atomic, then the state of autocommit
>shouldn't matter.

exactly... i would be most comfortable with it if it were an implied
transaction.

> The reason I ask my question is that in PyGreSQL I already fake
> this behaviour by doing a select * immediately after an insert and if it
> succeeds I load the caller's dictionary with the data so that they have
> the oid and any triggered or defaulted fields.  This function would be

so i'm not the only one doing this! nice to know =)

> useful for me except that I have to be able to deal with tables with
> insert only access and still let the insert go through.  My problem is
> that it is a generic function so I can't hard code the decision and need
> to have some way to check each time.

>feature that I could have used in a database I have.  Instead I had to
>give SELECT perms to a user on a table that I would have preferred to
>otherwise keep hidden.

this is an issue that doesn't really come up until you put a database with
sensitive information on a (semi-)public network... subinserts and RETURNs
would allay many security concerns i deal with on a daily basis at our
installation... 

i like the idea of another permission, such as ISELECT to allow this
behaviour...

> I hope we also allow the following if we do it.
> 
> INSERT INTO foo VALUES (1, 'aaa') RETURN f1, f2;
> 
> or
> 
> INSERT INTO foo VALUES (1, 'aaa') RETURN *;

does anybody know if there would be a processing time improvement with this
scheme? isn't the tuple (re)written during an INSERT or UPDATE, implying that
it is, at least temporarily, in memory? this seems to say to me that allowing an
immediate RETURN of data on an INSERT/UPDATE would be faster and easier on the
back end than an INSERT/UPDATE followed by a SELECT... can anyone with a deeper
understanding of the guts of pgsql verify/deny this?
      
> > my 0.02 (and that's canadian.. so..)
> 
> Dollarettes?
> Dollar Lite?

less filling! buys less!

-- 
Aaron J. Seigo
Sys Admin

Wed, 3 Nov 1999 12:21:00 -0700
"Aaron J. Seigo" <aaron@gtv.ca>
Re: [HACKERS] Get OID of just inserted record