Re: [HACKERS] Get OID of just inserted record

From: "Aaron J(dot) Seigo" <aaron(at)gtv(dot)ca>
To: pgsql-hackers(at)postgreSQL(dot)org, "D'Arcy" "J(dot)M(dot)" Cain <darcy(at)druid(dot)net>
Subject: Re: [HACKERS] Get OID of just inserted record
Date: 1999-11-03 19:21:00
Message-ID: 99110312295803.00702@stilborne
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Aaron J. Seigo 1999-11-03 20:16:13 Re: [HACKERS] getting new serial value of serial insert
Previous Message Ed Loehr 1999-11-03 18:43:04 [HACKERS] getting new serial value of serial insert