Re: Modified FIFO queue and insert rule

On Wednesday 8. August 2007 15:12, Alban Hertroys wrote:
>Leif B. Kristensen wrote:
>> CREATE RULE placelimit AS
>> ON INSERT TO recent_places DO ALSO
>> DELETE FROM recent_places
>> WHERE
>> -- this clause doesn't work
>> -- (place_fk = NEW.place_fk AND id <> NEW.id) OR
>> id NOT IN (SELECT id FROM recent_places ORDER BY id DESC LIMIT
>> 10);
>>
>> When I try to use the commented clause above, no records are written
>> to the table at all! Why?
>
>Do you use nextval() for that id?

I use :

INSERT INTO recent_places (place_fk) VALUES ($place);

And that's the functional equivalent of using nextval() I guess.

>In that case I think you immediately delete the record after inserting
>it, as nextval gets called again in the delete statement, and thus id
> <> NEW.id.

Yeah, that sounds reasonable.

>You should probably use a trigger (a before one maybe) instead of a
> rule.

I might consider that. But I figured that it should be trivial to modify
Greg's example rule.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/