[thelist] Postgre and Cold Fusion

Joshua Olson joshua at waetech.com
Wed Aug 11 07:23:54 CDT 2004


> -----Original Message-----
> From: Jeff Howden
> Sent: Wednesday, August 11, 2004 3:31 AM
>
> Well, curval() is one way.  Just because you can call it between
> page loads
> and get the same value doesn't mean it's not thread safe.
> Rather, it means
> that nothing has happened to the sequence you're requesting the current
> value for so therefore it's still the same.

Actually, it does in the sense that I meant.  curval() is supposed to return
the last sequence number generated within the current session.  If each page
load initiated a new session then the call to curval would result in an
error on the subsequent page.  Cftransaction did clear this up this problem
and my preliminary tests indicate that wrapping the insert and following
select in the cftransaction does indeed make it thread safe.  Arguably the
most taxing issue remaining is that you cannot embed multiple cftransactions
without throwing an error.  Since I often place insert queries in CFC's or
CT's, it's often not possible to know if you are already within a
cftransaction at runtime.  In inspection of GetBaseTagList will tell you
this (which then affords you the possibility to perform the query with or
without the wrapping cftransaction tags), but redundant code is never ideal.

> Alternatively, you could swap the order of your queries and swap curval()
> for nextval(), then insert the newly created id.
>
> Additionally, I would recommend setting your table up like this:
>
> CREATE TABLE tablename
> (
>   id int4 default nextval('sequencename'),
>   ...
> );
>
> Now you can insert the value yourself in situations where that's necessary
> or have the database do it for you the rest of the time.

Thanks for the info.  This is exactly how I set it up.  I was hoping to be
able to write code that would execute the same on both SQL Server and
PostGRE without modification.  It doesn't seem that this will be possible
given this limitation.  I have created a Custom Tag that does an amicable
job of necessitating almost no code change whatsoever.

I'll post the code to http://concepts.waetech.com/ shortly.

Thanks again.

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168




More information about the thelist mailing list