[Wellington-pm] Perl database stuff....
Andrew McMillan
andrew at catalyst.net.nz
Mon Dec 25 00:12:48 PST 2006
On Sun, 2006-12-24 at 22:55 +1100, Jacinta Richardson wrote:
> Andrew McMillan wrote:
>
> > (c) In the perl you can make it "select, select, insert" as:
> > 1. select the record (and fail).
> > 2. select the sequence nextval
> > 3. insert the row using the sequence.
> >
> > Which is actually the way I would tend to do it myself because it seems
> > so much cleaner to know the ID in advance and then write multiple SQL
> > calls using that value. I tend to take the (a) approach when I have
> > some values that I want to 'either update or insert these values', which
> > is slightly different from your question here.
>
> Make sure you're aware of how your database handles sequencing here, and also
> what requirements you have on your data. For example, if your database does not
> increment the sequence when you merely look at it, but rather does so when it is
> used then that could cause the same sequence number to be given out to multiple
> records. You can test this pretty easily by just doing a number of reads and
> seeing whether they're all different.
Good points in general, Jacinta. In this case Cliff explicitly said
PostgreSQL, which does sequences in an efficient and transaction safe
manner, and which I know very well, so I gave him a fairly explicit
answer.
> However, if you need all numbers to exist, then you've got a
> problem again.
Yeah. Hopefully it's merely a problem with whoever wrote your
requirements, and you can solve it by educating or replacing them :-)
Otherwise it's just a deadly recipe for race conditions or applications
having to block on a table lock.
:-)
Andrew.
-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267
Today is what happened to yesterday.
-------------------------------------------------------------------------
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://mail.pm.org/pipermail/wellington-pm/attachments/20061225/4725f875/attachment.bin
More information about the Wellington-pm
mailing list