Using $_SHARED as table cache in plperl
Oracle has new function result cache. It's nice idea. There are some use cases, mostly in www applications. There are nothing similar in PostgreSQL. I found simple similar solution in plperl (with important disadvantage to Oracle solution). It works, but without any pooling mechanisms its not really effective.
I created some tables:
CREATE TABLE Books(and I created function Top10Books that returns top ten books.
id serial PRIMARY KEY,
name VARCHAR(20));
CREATE TABLE Sale(
book_id integer REFERENCES Books(id),
inserted timestamp DEFAULT(CURRENT_TIMESTAMP)
);
INSERT INTO Books VALUES(1,'Dracula');
INSERT INTO Books VALUES(2,'Nosferatu');
INSERT INTO Books VALUES(3,'Bacula');
INSERT INTO Sale VALUES(1, '2007-10-11');
INSERT INTO Sale VALUES(2, '2007-10-12');
INSERT INTO Sale VALUES(2, '2007-10-13');
INSERT INTO Sale VALUES(3, '2007-10-10');
-- Top10One my customer put similar function on every page on some site (with really destructive impact to load on db server. Correct solution is to use some php cache or some similar tool. With plperl I am able cache result in Postgres too:
CREATE OR REPLACE FUNCTION Top10Books(IN date, OUT ordr integer, OUT name varchar(20))
RETURNS SETOF RECORD
AS $$
BEGIN
ordr := 0;
FOR name IN SELECT b.name
FROM Books b
JOIN
Sale s
ON b.id = s.book_id
WHERE s.inserted BETWEEN date_trunc('month', $1)
AND date_trunc('month', $1)
+ interval '1month' - interval '1day'
GROUP BY b.name
ORDER BY count(*) DESC
LIMIT 10
LOOP
ordr := ordr + 1;
RETURN NEXT;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION Top10BooksCached(IN date, IN bool,or with cache expiration
OUT ordr integer, OUT name varchar(20))
RETURNS SETOF RECORD
AS $$
return $_SHARED{tableof_top10book}
if (defined ($_SHARED{tableof_top10book}) and not (defined($_[1]) and $_[1] eq "t"));
if (not defined($_SHARED{plan_for_top10books}))
{
$_SHARED{plan_for_top10books} = spi_prepare(
'SELECT b.name
FROM Books b
JOIN
Sale s
ON b.id = s.book_id
WHERE s.inserted BETWEEN date_trunc(\'month\', $1)
AND date_trunc(\'month\', $1)
+ interval \'1month\' - interval \'1day\'
GROUP BY b.name
ORDER BY count(*) DESC
LIMIT 10' , 'DATE');
}
my $row;
my $i = 0;
my $heap;
my $sth = spi_query_prepared($_SHARED{plan_for_top10books}, $_[0]);
while (defined ($row = spi_fetchrow($sth))) {
push @$heap, {ordr => ++$i, name => $row->{name}}
}
$_SHARED{tableof_top10book} = $heap ;
return $_SHARED{tableof_top10book};
$$ LANGUAGE plperlu;
CREATE OR REPLACE FUNCTION Top10BooksCached(IN date, IN integer,An usage is simple:
OUT ordr integer, OUT name varchar(20))
RETURNS SETOF RECORD
AS $$
return $_SHARED{tableof_top10book}
if (defined ($_SHARED{tableof_top10book})
and defined($_SHARED{actualised_top10book})
and ($_SHARED{actualised_top10book} + $_[1] > time));
if (not defined($_SHARED{plan_for_top10books}))
{
$_SHARED{plan_for_top10books} = spi_prepare(
'SELECT b.name
FROM Books b
JOIN
Sale s
ON b.id = s.book_id
WHERE s.inserted BETWEEN date_trunc(\'month\', $1)
AND date_trunc(\'month\', $1)
+ interval \'1month\' - interval \'1day\'
GROUP BY b.name
ORDER BY count(*) DESC
LIMIT 10' , 'DATE');
}
my $row;
my $i = 0;
my $heap;
my $sth = spi_query_prepared($_SHARED{plan_for_top10books}, $_[0]);
while (defined ($row = spi_fetchrow($sth))) {
push @$heap, {ordr => ++$i, name => $row->{name}}
}
$_SHARED{tableof_top10book} = $heap ;
$_SHARED{actualised_top10book} = time;
return $_SHARED{tableof_top10book};
$$ LANGUAGE plperlu;
-- first call is slowUse it carefully! It's not good for big tables, and what more, this function can returns out-of-date values.
postgres=# select * from Top10BooksCached(current_date, 300);
ordr | name
------+-----------
1 | Nosferatu
2 | Bacula
3 | Dracula
(3 rows)
Time: 128,965 ms
-- second call is fast
postgres=# select * from Top10BooksCached(current_date, 300);
ordr | name
------+-----------
1 | Nosferatu
2 | Bacula
3 | Dracula
(3 rows)
Time: 11,911 ms
Labels: plperl, postgresql