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

Reid Thompson reid.thompson at ateb.com
Thu Oct 26 00:59:45 EDT 2006


Reid Thompson wrote:
> 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
> end
> 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
> want?
>
>   
>> OgModel.create_with(:name => 'Chichi')
>> OgModel.find_with_oid(... what oid to use here?...)
>>   
>>     
> OgModel.create_with(:name => 'Chichi') ( where name is unique )
> OgModel.find_by_name('Chichi')
>
> or for those cases where you must...
> aoid = OgModel.oid_nextval
> OgModel.create_with(:name => 'Chichi', :oid => aoid)
> OgModel.find_with_oid(aoid)
>   
actually, thinking about this,, what about a default param for 
create/save that turns on/off pre-fetches
of the oid.  So one could bypass the pre-fetch in those cases where the 
programmer knows
that immediate re-use of the object or immediate use of the objects's 
oid is not going to occur????
>   
>> 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
> efficient.
>
>   
>> Jonathan
>>
>>   
>>     
>
> _______________________________________________
> Nitro-general mailing list
> Nitro-general at rubyforge.org
> http://rubyforge.org/mailman/listinfo/nitro-general
>   



More information about the Nitro-general mailing list