[Wellington-pm] Perl database stuff....

[Andrew McMillan]

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