[Nitro] question about postgresql.rb && last_insert_id and insert_sql

Reid Thompson Reid.Thompson at ateb.com
Thu Oct 26 04:30:49 EDT 2006

Jonathan Buch wrote:
> Hi,
>>> The problem is, that the frontend (Og) has to know the oid which has
>>> been inserted.
>> Why does the frontend need to know which oid has been inserted?
>> When I think of an activity that requires insertion ( the creation of a
>> new record in the DB ), I think in terms that all actions that need to
>> be performed on the object are performed prior to .create .save, etc.
>> I.E. at the point that I insert into the DB, that is the last action I
>> expect to take on that object.  If I need to alter the record after it's
>> created, then I expect to have to query for it first.
> Lets use a simple example:
Lets use a more realistic example:
> class OgModel
      property :name, String, :unique => true

>    has_many OgModel
> end
> Now we create a instance and save it to the database with either of
> those methods:
> m = OgModel.create_with(:name => 'George')
> m = OgModel.new
> m.name = 'Stella'
> m.save
> After that statement, we have an object called 'm'.  When I now work on
> this object (same object, not gotten anew from database):
'Oops, I created that record with the wrong name'
m = OgModel.find_by_name('Stella')

> m.name = 'Takeo'
> m.save
> What happens?  The .save method needs to UPDATE the model we just CREATEd
> and it can only do this, if it can exactly identify the model.
which it can
> So we need to know at this point which oid (or any other primary key) the
> model has.
we do
> So, .save is by far not the last action, it is merely the first step.
I contend that it should be the last step.  Every column should be populated
that you have information for before initiating the insert.  Why make more
than one DB call if you don't have to. If you don't have
all the information that you need, then when the point comes that you have
that information, you retrieve that record and update it.

> But, taking your example that you use .save as the last, and querying
> the database when you want to use the object further:
> How (when you haven't got the primary key) do you know, that you get
> exactly the object you want?
By using a realistic model in which a sequence generated integer oid is
not the only means of identifying the column you want.  Situations where the
sequence oid is the only unique identifier on a table should be the
abnormality -- for those cases perhaps a method that returns nextval()
for that table's oid sequence should be provided.
I.E. in what situation would a table such as this be used in a production app
-- where name is not unique?
class OgModel
   property :name, String
   has_many OgModel
Given this model, one could create 1*N up to N*N records with the name 'Stella', each with
a different oid.  When you come back later, how do you know which 'Stella' you

> OgModel.create_with(:name => 'Chichi')
> OgModel.find_with_oid(... what oid to use here?...)
OgModel.create_with(:name => 'Chichi') ( where name is unique )

or for those cases where you must...
aoid = OgModel.oid_nextval
OgModel.create_with(:name => 'Chichi', :oid => aoid)

> Also, when working with relations, it would be mighty inconvenient to
> not be able to work on the same object;  maybe adding a few other related
> objects to a has_many for example (which must know the primary key for
> insertion in the related objects) to a just created OgModel instance.
As shown above, this would not be an  issue.
> But maybe I'm just getting you wrong somehow..
> Was my explanation right now enough to convince you that Og needs to know
> the primary key before really inserting?  If not, I will try to think of
> another way to explain this.
I understand fine( and the current implementation is fine, I was just
wondering if a different implementation may be more efficient for the
majority of cases most of the time?). It just seems the reverse of the
normal sequence of record generation/usage.  If the majority of records
that are inserted require an immediate update of that record, or some
other immediate action requires the oid then it's two DB transactions
regardless of the implementation.  However, if the majority of records
inserted do not require an immediate update and do not require an other
immediate action using the oid -- then you've eliminated a DB
transaction( not a good example, but => if you're bulk loading 300K
records from a data file the current implementation will make 600K DB
calls,...)  Do the majority of Og record creations require an immediate
update of the record or an immediate use of it's oid? If yes, then it's
a moot point; if not, then altering the implementation may be much more

> Jonathan

More information about the Nitro-general mailing list