Thread: Re: MOVE
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@postgresql.org>
e.com> cc:
Sent by: Subject: [GENERAL] 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@postgresql.org so that your
message can get through to the mailing list cleanly
Use an after inset trigger.
On Fri, 2005-01-14 at 15:38, Richard_D_Levine@raytheon.com 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@postgresql.org>
> e.com> cc:
> Sent by: Subject: [GENERAL] 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@postgresql.org 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@postgresql.org)
--
Dave Smith
CANdata Systems Ltd
416-493-9020
> Use an after inset trigger.
Well I did the reverse, an after delete trigger on the live table which
inserts the deleted row in the history table, and it works very well.
Thanks.