Re: [GENERAL] Session based transaction!!
Web application have single DB only.. On Fri, Dec 25, 2009 at 7:03 PM, Bill Moran wmo...@potentialtech.comwrote: S Arvind arvindw...@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
Re: [GENERAL] Session based transaction!!
S Arvind arvindw...@gmail.com wrote: Web application have single DB only.. I'm unsure what you mean by that and how it relates to my answer. On Fri, Dec 25, 2009 at 7:03 PM, Bill Moran wmo...@potentialtech.comwrote: S Arvind arvindw...@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 -- Bill Moran http://www.potentialtech.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Session based transaction!!
On Sat, Dec 26, 2009 at 3:02 PM, Bill Moran wmo...@potentialtech.com wrote: S Arvind arvindw...@gmail.com wrote: Web application have single DB only.. I'm unsure what you mean by that and how it relates to my answer. On Fri, Dec 25, 2009 at 7:03 PM, Bill Moran wmo...@potentialtech.comwrote: S Arvind arvindw...@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 -- Bill Moran http://www.potentialtech.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Seem like you would like to generate and manipulate data within the confines of a user's session with your website. Depending on many factors you may choose from the 2 options below (there other options too). 1)The Java Servlet 2.x provides for the session object to which you can store serializable Java objects, the session object does get destroyed at the end of the session and the servlet specification you the developer with the ability to receive notification of the start or end of each session. This option is good for small amounts of data, to store a lot of data the a given HTTP session perhaps using the DB may be in order. 2)You may store the data in table(s) and have a field to store the session id (generated by the servlet container) in the primary table(s). During the initial table creation of these tables create the foreign key with on delete cascade. Populate these tables to store the users' data. When the HTTP user session is destroyed simply program to allow your Java application to be notified of this event (by the servlet container), obtain the sessionId of the session being destroyed and delete the records from the table(s) having this sessionId. This option may pose some challenges as Bill pointed out. Starting a DB transaction and leaving it in effect for the entire duration of a visitor's visit to your web site IMHO is in _most_ case not the best solution. When a transaction is running the DB connection in which the transaction is running is dedicated to that transaction which means at least one active DB connection per each visitor having an active HTTP session, then there is the possibility of deadlocks and so on. Allan. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Session based transaction!!
S Arvind arvindw...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Session based transaction!!
S Arvind 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 when exactly might the session be destroyed if, for instance, the user wanders off bored or abruptly closes his browser ? http sessions are independent of http sockets, at least in http/1.1 eventually, presumably, you time out the idle session, bit isn't that typically in several hours? transactions that run many hours are painful for postgres, they intefere with vacuum processing, and can lead to bloated tables, especially on databases that ahve high transactional rates. you likely will need to manage your own database connections, and keep them with your session tracking data. you may need to implement your own specialized version of a connection pool, this one would return a specific session upon request rather than the next available one, and be closely tied into your session manager. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Session based transaction!!
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.. Arvind S * Many of lifes failure are people who did not realize how close they were to success when they gave up. -Thomas Edison*
Re: [GENERAL] Session based transaction!!
Hi, S Arvind schrieb: 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.. Isn't that the default? If not you should handle your database interaction with some high priority handler which runs first and ends last in your request and handles all exceptions bubbling from other activities inside it and does a rollback in this case. (Thats the way Zope does it - but it has a transaction framework. Not sure if Tomcat offers the same easy hooks but there should be a way.) Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
