Re: MOVE - Mailing list pgsql-general

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