On insertion patterns: INSERT <garbage> + UPDATE <true values> vs INSERT <true values>

[Erik Huelsmann <..hidden..>]

Hi Chris,

We've been talking on GoogleTalk about the way oldcode is using the pattern:

  INSERT <garbage>
  <do stuff>
  UPDATE <true values>

I'm now looking at LedgerSMB/OE.pm::generate_orders() which is using
this pattern between lines 2354 (the INSERT) and 2440 (the UPDATE).

This specific case uses the INSERT to acquire a unique ID from the
index, continues to insert records in another table with that specific
ID and then inserts the true data in the main table. You seemed to say
that it's very hard to rewrite code not to use that pattern. While I
admit that it's going to be a lot of work to do so for the entire code
base, I'm not seeing how the problem of doing so is going to be
'hard', especially if we're going to target 8.4 and beyond. [Which all
have the INSERT INTO ... RETURNING ... functionality which would allow
the INSERT to tell us the ID it generated for the record it's
inserted.]

Specifically this case, I'm thinking, should be quite easy to add a
wrapping transaction -- which is a good idea even if only to make sure
all data gets added to both tables, or nothing. The idea would be to
insert all "meaningful" data in an insert, then insert the orderlines
in the lines table followed by an update to the table adding the
calculated amounts.

All in all, that saves 1 query: the query which finds the inserted
record and prevents partial information to be inserted into the
database.


Did I correctly interpret you?


Bye,


Erik.