Use an after inset trigger. On Fri, 2005-01-14 at 15:38, Richard_D_Levine@xxxxxxxxxxxx wrote: > PFC wrote: I'd really like to have a sql command, say MOVE, or SELECT AND > DELETE, whatever, which acts like a SELECT, returning the rows, but > deleting them as well. > > Oracle implements this with the syntax DELETE FROM ... RETURNING ...; > There is also UPDATE ... RETURNING ...; > > Where the deleted rows are also returned. This is non-standard SQL, but > there is a precedent. It is efficient because it saves at least one round > trip from the database to the client. I don't use them to stay portable. > > Rick > > > > PFC > <lists@boutiquenumeriqu To: "Postgres general mailing list" <pgsql-general@xxxxxxxxxxxxxx> > e.com> cc: > Sent by: Subject: MOVE > pgsql-general-owner@pos > tgresql.org > > > 01/14/2005 02:49 PM > > > > > > > > Hello, > > Here I'm implementing a session management, which has a connections table > partitioned between > active and archived connections. A connection represents a connection > between a user and a chatroom. > > I use partitioning for performance reasons. > > The active table contains all the data for the active session : user_id, > chatroom_id, session start > time, and other information. > The archive table contains just the user_id, chatroom_id, session start > and end time, for logging > purposes, and for displaying on the site, which user was logged to which > chatroom and from when to when. > > Thus, when a user disconnects from a chatroom, I must move one row from > the active to the archive > table. This poses no problem as there is a UNIQUE index > (iser_id,chatroom_id) so I select the row FOR > UPDATE, insert it in the archive table, then delete it. > > Now, when a user logs out from the site, or when his session is purged by > the auto-expiration cron > job, I must also expire ALL his open chatroom connections. > INSERT INTO archive (...) SELECT ... FROM active WHERE user_id = ...; > DELETE FROM active WHERE user_id = ...; > > Now, if the user inserts a connection between the two queries above, the > thing will fail (the > connection will just be deleted). I know that there are many ways to do it > > right : > - LOCK the table in exclusive mode > - use an additional primary key on the active table which is not related > to the user_id and the > chatroom_id, select the id's of the sessions to expire in a temporary > table, and use that > - use an extra field in the table to mark that the rows are being processed > - use transaction isolation level SERIALIZABLE > > However, all these methods somehow don't feel right, and as this is an > often encountered problem, > I'd really like to have a sql command, say MOVE, or SELECT AND DELETE, > whatever, which acts like a SELECT, > returning the rows, but deleting them as well. Then I'd just do INSERT > INTO archive (...) SELECT ... AND > DELETE FROM active WHERE user_id = ...; > > which would have the following advantages : > - No worries about locks : > - less chance of bugs > - higher performance because locks have to be waited on, by definition > - No need to do the request twice (so, it is twice as fast !) > - Simplicity and elegance > > There would be an hidden bonus, that if you acquire locks, you better > COMMIT the transaction as > soon as possible to release them, whereas here, you can happily continue > in the transaction. > > I think this command would make a nice cousin to the also very popular > INSERT... OR UPDATE which > tries to insert a row, and if it exists, UPDATES it instead of inserting > it ! > > What do you think ? > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your > message can get through to the mailing list cleanly > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx) -- Dave Smith CANdata Systems Ltd 416-493-9020 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
