[lug] (OT) postgresql

[Michael Deck]

At 10:02 AM 11/11/2002, you wrote:
>I run Java in Autocommit mode all the time and it works. You must not be
>using the same connection handle across requests.

I was having a problem with INSERT immediately followed by SELECT CURRVAL only when I was *not* using autocommit, i.e. 

  BEGIN WORK
  INSERT
  SELECT CURRVAL
  COMMIT 

My app requires control of the transactions so I ended up with the select last_value approach. Also because I am doing all this in carefully-controlled transactions, I think I'm immune to the various race conditions discussed elsewhere. However Bryan's suggestion looks like it should be equivalent and would definitely be better when you don't have tight control over transactions. 



>-----Original Message-----
>From: lug-admin at lug.boulder.co.us [mailto:lug-admin at lug.boulder.co.us]On
>Behalf Of Bear Giles
>Sent: 11 November, 2002 8:31 AM
>To: lug at lug.boulder.co.us
>Subject: Re: [lug] (OT) postgresql
>
>
>Sexton, George wrote:
>> to read the value just inserted:
>>
>> select CurrVal('users_seq') as newKey
>
>This only works until you commit or abort the transaction.  I mention
>this only because some interface libraries do auto-commit - you'll
>either need to disable it so you can bring up the value for use in other
>tables, or create insertion triggers and/or rules that will
>automatically update those other tables.
>
>This brings up an important difference between PostgreSQL and MySQL.
>PostgreSQL supports views, rules, triggers and a robust constraint set.
>In practice, if I have to update multiple tables I prefer to set up a
>view that combines all of this information, then a rule that performs
>the necessary insertions.  This decouples the business logic (which
>determines what needs to be stored in the database) from the database
>logic (which determines what goes where, etc.), and life is much simpler
>for everyone involved.
>
>That's why I cringe every time I see some tool that requires you to
>specify a table and every column name for it to access the database.
>Besides being a real pain to maintain, it assumes that all of the data
>*is* in a single table.  I always set up a view for each application
>(which also allows me to control access to the rest of the database),
>but why can't the app just tell me what view and attributes to set up?
>It's not like I'll already have tables with the application name in them!
>
>
>_______________________________________________
>Web Page:  http://lug.boulder.co.us
>Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
>Join us on IRC: lug.boulder.co.us port=6667 channel=#colug
>
>_______________________________________________
>Web Page:  http://lug.boulder.co.us
>Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
>Join us on IRC: lug.boulder.co.us port=6667 channel=#colug


Michael Deck
Cleanroom Software Engineering, Inc.