Re: Session based transaction!! - Mailing list pgsql-general

S Arvind <arvindwill@gmail.com> wrote:
>
> Hi Everyone,
>
> In a web application running in Tomcat and Postgres 8.3 as DB, i need to
> know whether my given task is feasible or not.
>            All the db operation which is done by that web application must
> be rollback at the end(session dies) and the DB data must be same as the
> starting stage(of session). Its like virtually placing the data for that
> session alone and rollbacking the db to the template which is originally
> been. So whenever users get in the webapplication, the initial data must be
> the template data only and he can perform any operation for which data is
> visible for that user alone and when the users leaves(session destroyed) all
> the data changed in that time must be rollbacked.
>
> I thought this by, When the session created in the application a transaction
> must be started and all the activites must be done on the DB, but the
> transaction will not be commited or rollback across request but it must be
> done across the session. By not destroying the connection and using it for
> all the work done by that session. when session destroy we can rollback the
> entire transaction
>
> Is there any other way to achieve the requirements in postgres.
> Thanks in advance..

Would be easy except for one factor that I don't know about in Tomcat.

In most web applications, the database connection is not maintained between
page loads.  Each new page view may (and usually does) get a different DB
connection than the previous one.  If Tomcat maintains a single DB
connection for a session across all page view, then you should be able
to implement this.  However, if Tomcat runs like most of the other web
systems I've seen, you'll have no way to ensure that a particular page
view will have the same DB connection as a previous page view.  It will
require some sort of middleware that keeps the DB connections open and
associates HTTP sessions with DB connections.

Past that, however, I expect it will be a maintenance nightmare.  Each
rolled back DB session is going to generate a lot of dead rows that
vacuum will have to reclaim.  Whether or not this is feasible overall
depends on a lot of questions that I don't know the answers to.  Partly,
it's going to depend on the amount of change and amount of concurrency
that occurs.

Personally, I would recommend coming up with a different approach, but
I might be wrong.

--
Bill Moran
http://www.potentialtech.com