[lug] (OT) postgresql

[Sexton, George]

I run Java in Autocommit mode all the time and it works. You must not be
using the same connection handle across requests.



-----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