[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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



On Fri, 17 Jun 2011, Erik Huelsmann wrote:

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.

I'm throwing this out there unconsidered. I accept that it may be an utterly stupid idea.

Thus disclaimed, I note that one way to use a globally unique ID, would be to keep a sequence table.

Basically you'd have two columns: a sequence and a type.

Your first action is to insert a new type record, which returns the ID thus generated, which may be then used as a foreign key in any number of other places.

The advantage, is that you're not generating IDs in a bunch of different places. (However, I'm not actually sure that you're doing that now, so maybe that's irrelevant.)

Add a control-number field, and an order, invoice, GL, etc. number can be retroactively added to the sequence table, to identify what document of type type, the user might expect to find associated with that ID.
(or not)

Anyway, it was a random thought I had while reading this, which may be negated by the workings of modern PostGreSQL (I've been more of a developer of applications which use MySQL).

I won't be offended by having this ignored for being non-applicable.:)

Luke