Re: [SQL] How to keep at-most N rows per group? periodic DELETEs or constraints or..?

How about using a trigger to call a stored procedure ? [ON INSERT to user_item_history DO ...] and have your stored procedure count the records for that user and delete the oldest record if necessary... IF (SELECT COUNT(*) WHERE user_id = NEW.user_id) = 50 THEN -- DELETE THE

Re: [SQL] How to keep at-most N rows per group? periodic DELETEs or constraints or..?

Hi codeWarrior, codeWarrior wrote: For user_ids that have more than 50 rows, I want to keep the most recent 50 and delete the rest. How about using a trigger to call a stored procedure ? [ON INSERT to user_item_history DO ...] [snip] Thanks for your input! I've implemented this but I'm

Re: [SQL] How to keep at-most N rows per group? periodic DELETEs or constraints or..?

On Jan 8, 2008, at 8:24 AM, Jamie Tufnell wrote: Hi, I have a table that stores per-user histories of recently viewed items and I'd like to limit the amount of history items to = 50 per user. I'm considering doing this with a query run from cron every so often but I'm not happy with what I've

Re: [SQL] How to keep at-most N rows per group? periodic DELETEs or constraints or..?

Jamie: I think you are probably having slowdown issues in your DELETE FROM WHERE NOT IN SELECT ORDER BY DESCENDING construct -- that seems a bit convoluted to me NOT IN is what is probably slowing you down the most ALSO: It looks to me like you have a column named timestamp' ???

Re: [SQL] How to keep at-most N rows per group? periodic DELETEs or constraints or..?

On 1/8/08, codeWarrior [EMAIL PROTECTED] wrote: Jamie: I think you are probably having slowdown issues in your DELETE FROM WHERE NOT IN SELECT ORDER BY DESCENDING construct -- that seems a bit convoluted to me Hmm so rather than NOT IN ( .. LIMIT 50) would you suggest IN ( ... OFFSET

[SQL] Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?

I think what you want is related to this post on how to create a FIFO queue in Postgres: http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-version.html The major difference is that you want a FIFO queue per user_id, so the triggering code

[SQL] trigger for TRUNCATE?

Hi all. Acording to the docs, TRUNCATE will not fire a DELETE trigger on the table being truncated. There is a way to capture a TRUNCATE in any way? Thanks! Gerardo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] How to keep at-most N rows per group? periodic DELETEs or constraints or..?

On 1/8/08, Erik Jones [EMAIL PROTECTED] wrote: Hmm so rather than NOT IN ( .. LIMIT 50) would you suggest IN ( ... OFFSET 50) like in Erik's example? Or something else entirely? Well, that would give you some gain. Think about it like this: once a given user's history records are at 50

Re: [SQL] How to keep at-most N rows per group? periodic DELETEs or constraints or..?

On Jan 8, 2008, at 11:41 AM, Jamie Tufnell wrote: On 1/8/08, codeWarrior [EMAIL PROTECTED] wrote: Jamie: I think you are probably having slowdown issues in your DELETE FROM WHERE NOT IN SELECT ORDER BY DESCENDING construct -- that seems a bit convoluted to me Hmm so rather than NOT

Re: [SQL] trigger for TRUNCATE?

[EMAIL PROTECTED] (Gerardo Herzig) writes: Hi all. Acording to the docs, TRUNCATE will not fire a DELETE trigger on the table being truncated. There is a way to capture a TRUNCATE in any way? I think there's some sort of to do on that... It ought to be not *too* difficult (I imagine!) to be

Re: [SQL] trigger for TRUNCATE?

Hello theoretically you can have trigger on any statement, but I am not sure about conformance with std. But, you can wrap TRUNCATE statement into some procedure, and then call this procedure with some other actions. Regards Pavel Stehule On 08/01/2008, Chris Browne [EMAIL PROTECTED] wrote: