Thread: Modified FIFO queue and insert rule

I found an excellent description of how to implement a fifo que in
PostgreSQL at Greg Mullane's blog:

http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-version.html

I have used the 'rule' approach to implement a queue that generates a
quick-list of last selected places. The only modification I need is
that if an item already exists in the list, a new reference should be
written to the top, and the old reference should be deleted. But it
seems like I'm in over my head here:

-- short FIFO list of recently selected places
CREATE TABLE recent_places (
    id SERIAL PRIMARY KEY,
    place_fk INTEGER REFERENCES places ON DELETE CASCADE
);

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?
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

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?
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.

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

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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/

On Wednesday 8. August 2007 15:12, Alban Hertroys wrote:
>You should probably use a trigger (a before one maybe) instead of a
> rule.

I tried that too, but I'm still quite shaky on how to write triggers,
and the same thing happened there: the inserted record was immediately
deleted. I solved the problem temporarily with two lines in PHP:

function set_last_selected_place($place) {
    pg_query("DELETE FROM recent_places WHERE place_fk = $place");
    pg_query("INSERT INTO recent_places (place_fk) VALUES ($place)");
}

As my application is single-user, and everything is already wrapped up
in a transaction anyway, there's no real problem with this. But I'd
still like to understand how to do it 'properly' inside the DB.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

On Thu, Aug 09, 2007 at 06:14:43PM +0200, Leif B. Kristensen wrote:
> On Wednesday 8. August 2007 15:12, Alban Hertroys wrote:
> >You should probably use a trigger (a before one maybe) instead of a
> > rule.
>
> I tried that too, but I'm still quite shaky on how to write triggers,
> and the same thing happened there: the inserted record was immediately
> deleted. I solved the problem temporarily with two lines in PHP:

You have to use a BEFORE trigger for this to work, unless you're careful
about how you build your where clause. The AFTER trigger is going to see
the row that you just inserted, so you'd have to explicitly exclude it
from the DELETE.

> function set_last_selected_place($place) {
>     pg_query("DELETE FROM recent_places WHERE place_fk = $place");
>     pg_query("INSERT INTO recent_places (place_fk) VALUES ($place)");
> }
>
> As my application is single-user, and everything is already wrapped up
> in a transaction anyway, there's no real problem with this. But I'd
> still like to understand how to do it 'properly' inside the DB.

Better than what you're doing right now would be to wrap everything into
a function and just call that. Depending on your design, that could be
more (or less) "correct" than trying to do it with a trigger.
--
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)