MOVE - Mailing list pgsql-general

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 ?