Thread: Get OID of just inserted record
Hi, Is there any way to obtain an OID of record just inserted by SPI_execp? Thanks in advance, Andriy Korud, Lviv, Ukraine
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)
------------------------------------------------------------------------------
>
> 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) #
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
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
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
"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
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
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
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) #
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.
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
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.
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