is this trigger safe and efective? - locking (caching via triiggers)

Hello,

I am sorry, this mail had to be send only to pgsql-general

nice a day
Pavel Stehule

---------- Forwarded message ----------
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: 15.8.2007 8:01
Subject: is this trigger safe and efective? - locking (caching via triiggers)
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>

Hello

I write sample about triggers and i have question. is my solution
correct and exists better solution?

Regards
Pavel Stehule

DROP SCHEMA safecache CASCADE;

CREATE SCHEMA safecache;

CREATE TABLE safecache.source_tbl(category int, int_value int);

CREATE TABLE safecache.cache(category int, sum_val int);

CREATE OR REPLACE FUNCTION safecache.source_tbl_trg_fce()
RETURNS trigger AS
$$
BEGIN
IF TG_OP = 'INSERT' THEN
-- row cannot exists in cache -- complication
-- I would to finish these transaction without conflict
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
LOCK TABLE safecache.cache IN SHARE ROW EXCLUSIVE MODE;
-- I have to repeat test
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
INSERT INTO safecache.cache
VALUES(NEW.category, NEW.int_value);
END IF;
ELSE
-- simple
UPDATE safecache.cache
SET sum_val = sum_val + NEW.int_value
WHERE category = NEW.category;
END IF;
ELSEIF TG_OP = 'UPDATE' THEN
-- if category is without change simple
IF NEW.category = OLD.category THEN
UPDATE safecache.cache
SET sum_val = sum_val + (NEW.int_value - OLD.int_value)
WHERE category = OLD.category;
ELSE
-- old category has to exists
UPDATE safecache.cache
SET sum_val = sum_val - OLD.int_value
WHERE category = OLD.category;
-- new category is maybe problem
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
LOCK TABLE safecache.cache IN SHARE ROW EXCLUSIVE MODE;
-- I have to repeat test
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
INSERT INTO safecache.cache
VALUES(NEW.category, NEW.int_value);
END IF;
ELSE
-- simple, new category exists
UPDATE safecache.cache
SET sum_val = sum_val + OLD.int_value
WHERE category = NEW.category;
END IF;
END IF;
ELSE -- DELETE
-- value have to exist in cache, simple
UPDATE safecache.cache
SET sum_val = sum_val - OLD.int_value
WHERE category = OLD.category;
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER actualise_cache
AFTER INSERT OR UPDATE OR DELETE
ON safecache.source_tbl
FOR EACH ROW EXECUTE PROCEDURE safecache.source_tbl_trg_fce();

I don't like the locking... take a look at Ex 37-1 at the end of
http://lnk.nu/postgresql.org/fhe.html for a better way (though, the
comment below about going into an infinite loop is a good observantion,
but I think perhaps after some number of fast tries it should start
putting a sleep in the loop, rather than just arbitrarily bombing after
10 tries.

Also, I remember discussion on -performance about this from folks using
it in the real world... the problem they ran into is that doing the
updates in the cache/mview table directly bloated it too much... they
found it was better to just insert changes into an interim table, and
then periodically batch-process that table.

On Wed, Aug 15, 2007 at 08:01:24AM +0200, Pavel Stehule wrote:

Hello

I write sample about triggers and i have question. is my solution
correct and exists better solution?

Regards
Pavel Stehule

DROP SCHEMA safecache CASCADE;

CREATE SCHEMA safecache;

CREATE TABLE safecache.source_tbl(category int, int_value int);

CREATE TABLE safecache.cache(category int, sum_val int);

CREATE OR REPLACE FUNCTION safecache.source_tbl_trg_fce()
RETURNS trigger AS
$$
BEGIN
IF TG_OP = 'INSERT' THEN
-- row cannot exists in cache -- complication
-- I would to finish these transaction without conflict
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
LOCK TABLE safecache.cache IN SHARE ROW EXCLUSIVE MODE;
-- I have to repeat test
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
INSERT INTO safecache.cache
VALUES(NEW.category, NEW.int_value);
END IF;
ELSE
-- simple
UPDATE safecache.cache
SET sum_val = sum_val + NEW.int_value
WHERE category = NEW.category;
END IF;
ELSEIF TG_OP = 'UPDATE' THEN
-- if category is without change simple
IF NEW.category = OLD.category THEN
UPDATE safecache.cache
SET sum_val = sum_val + (NEW.int_value - OLD.int_value)
WHERE category = OLD.category;
ELSE
-- old category has to exists
UPDATE safecache.cache
SET sum_val = sum_val - OLD.int_value
WHERE category = OLD.category;
-- new category is maybe problem
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
LOCK TABLE safecache.cache IN SHARE ROW EXCLUSIVE MODE;
-- I have to repeat test
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
INSERT INTO safecache.cache
VALUES(NEW.category, NEW.int_value);
END IF;
ELSE
-- simple, new category exists
UPDATE safecache.cache
SET sum_val = sum_val + OLD.int_value
WHERE category = NEW.category;
END IF;
END IF;
ELSE -- DELETE
-- value have to exist in cache, simple
UPDATE safecache.cache
SET sum_val = sum_val - OLD.int_value
WHERE category = OLD.category;
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER actualise_cache
AFTER INSERT OR UPDATE OR DELETE
ON safecache.source_tbl
FOR EACH ROW EXECUTE PROCEDURE safecache.source_tbl_trg_fce();

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On Aug 15, 2007, at 4:57 AM, Pavel Stehule wrote:

I write sample about triggers and i have question. is my solution
correct and exists better solution?

Regards
Pavel Stehule

DROP SCHEMA safecache CASCADE;

CREATE SCHEMA safecache;

CREATE TABLE safecache.source_tbl(category int, int_value int);

CREATE TABLE safecache.cache(category int, sum_val int);

CREATE OR REPLACE FUNCTION safecache.source_tbl_trg_fce()
RETURNS trigger AS
$$
BEGIN
IF TG_OP = 'INSERT' THEN
-- row cannot exists in cache -- complication
-- I would to finish these transaction without conflict
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
LOCK TABLE safecache.cache IN SHARE ROW EXCLUSIVE MODE;
-- I have to repeat test
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
INSERT INTO safecache.cache
VALUES(NEW.category, NEW.int_value);
END IF;
ELSE
-- simple
UPDATE safecache.cache
SET sum_val = sum_val + NEW.int_value
WHERE category = NEW.category;
END IF;
ELSEIF TG_OP = 'UPDATE' THEN
-- if category is without change simple
IF NEW.category = OLD.category THEN
UPDATE safecache.cache
SET sum_val = sum_val + (NEW.int_value - OLD.int_value)
WHERE category = OLD.category;
ELSE
-- old category has to exists
UPDATE safecache.cache
SET sum_val = sum_val - OLD.int_value
WHERE category = OLD.category;
-- new category is maybe problem
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
LOCK TABLE safecache.cache IN SHARE ROW EXCLUSIVE MODE;
-- I have to repeat test
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
INSERT INTO safecache.cache
VALUES(NEW.category, NEW.int_value);
END IF;
ELSE
-- simple, new category exists
UPDATE safecache.cache
SET sum_val = sum_val + OLD.int_value
WHERE category = NEW.category;
END IF;
END IF;
ELSE -- DELETE
-- value have to exist in cache, simple
UPDATE safecache.cache
SET sum_val = sum_val - OLD.int_value
WHERE category = OLD.category;
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER actualise_cache
AFTER INSERT OR UPDATE OR DELETE
ON safecache.source_tbl
FOR EACH ROW EXECUTE PROCEDURE safecache.source_tbl_trg_fce();

From what I can tell from your example it's "correct" and should
work under light loads. However, if that trigger will fire a lot,
you might see those updates "stacking" due to the necessary locking
(both your explicit locks and those take out by the updates). What
I've done in that case (this is actually a pretty standard setup), is
to have the trigger just make inserts into another table of the
category that needs to be updated and by how much. The you have some
other (probably user-land) process periodically sweep that table,
aggregate the updates to the cache table, then delete the interim
entries just processed. Oh yeah, you could simplify that function a
lot by simply initializing your cache table with a row for each
category with sum_val = 0. Then it's all updates and you don't need
those locks to determine if the category exists there.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

2007/8/15, Erik Jones <erik@myemma.com>:

On Aug 15, 2007, at 4:57 AM, Pavel Stehule wrote:

I write sample about triggers and i have question. is my solution
correct and exists better solution?

Regards
Pavel Stehule

DROP SCHEMA safecache CASCADE;

CREATE SCHEMA safecache;

CREATE TABLE safecache.source_tbl(category int, int_value int);

CREATE TABLE safecache.cache(category int, sum_val int);

CREATE OR REPLACE FUNCTION safecache.source_tbl_trg_fce()
RETURNS trigger AS
$$
BEGIN
IF TG_OP = 'INSERT' THEN
-- row cannot exists in cache -- complication
-- I would to finish these transaction without conflict
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
LOCK TABLE safecache.cache IN SHARE ROW EXCLUSIVE MODE;
-- I have to repeat test
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
INSERT INTO safecache.cache
VALUES(NEW.category, NEW.int_value);
END IF;
ELSE
-- simple
UPDATE safecache.cache
SET sum_val = sum_val + NEW.int_value
WHERE category = NEW.category;
END IF;
ELSEIF TG_OP = 'UPDATE' THEN
-- if category is without change simple
IF NEW.category = OLD.category THEN
UPDATE safecache.cache
SET sum_val = sum_val + (NEW.int_value - OLD.int_value)
WHERE category = OLD.category;
ELSE
-- old category has to exists
UPDATE safecache.cache
SET sum_val = sum_val - OLD.int_value
WHERE category = OLD.category;
-- new category is maybe problem
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
LOCK TABLE safecache.cache IN SHARE ROW EXCLUSIVE MODE;
-- I have to repeat test
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
INSERT INTO safecache.cache
VALUES(NEW.category, NEW.int_value);
END IF;
ELSE
-- simple, new category exists
UPDATE safecache.cache
SET sum_val = sum_val + OLD.int_value
WHERE category = NEW.category;
END IF;
END IF;
ELSE -- DELETE
-- value have to exist in cache, simple
UPDATE safecache.cache
SET sum_val = sum_val - OLD.int_value
WHERE category = OLD.category;
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER actualise_cache
AFTER INSERT OR UPDATE OR DELETE
ON safecache.source_tbl
FOR EACH ROW EXECUTE PROCEDURE safecache.source_tbl_trg_fce();

From what I can tell from your example it's "correct" and should
work under light loads. However, if that trigger will fire a lot,
you might see those updates "stacking" due to the necessary locking
(both your explicit locks and those take out by the updates). What
I've done in that case (this is actually a pretty standard setup), is
to have the trigger just make inserts into another table of the
category that needs to be updated and by how much. The you have some
other (probably user-land) process periodically sweep that table,
aggregate the updates to the cache table, then delete the interim
entries just processed. Oh yeah, you could simplify that function a
lot by simply initializing your cache table with a row for each
category with sum_val = 0. Then it's all updates and you don't need
those locks to determine if the category exists there.

I know it, but I don't know all posible category numbers, and others.
I sent sample with minimum (one) pk attributies.

Erik Jones

I have 98% of SELECTs and 2% of INSERTs and UPDATE

On Aug 15, 2007, at 11:14 AM, Pavel Stehule wrote:

2007/8/15, Erik Jones <erik@myemma.com>:

On Aug 15, 2007, at 4:57 AM, Pavel Stehule wrote:

I write sample about triggers and i have question. is my solution
correct and exists better solution?

Regards
Pavel Stehule

DROP SCHEMA safecache CASCADE;

CREATE SCHEMA safecache;

CREATE TABLE safecache.source_tbl(category int, int_value int);

CREATE TABLE safecache.cache(category int, sum_val int);

CREATE OR REPLACE FUNCTION safecache.source_tbl_trg_fce()
RETURNS trigger AS
$$
BEGIN
IF TG_OP = 'INSERT' THEN
-- row cannot exists in cache -- complication
-- I would to finish these transaction without conflict
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
LOCK TABLE safecache.cache IN SHARE ROW EXCLUSIVE MODE;
-- I have to repeat test
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
INSERT INTO safecache.cache
VALUES(NEW.category, NEW.int_value);
END IF;
ELSE
-- simple
UPDATE safecache.cache
SET sum_val = sum_val + NEW.int_value
WHERE category = NEW.category;
END IF;
ELSEIF TG_OP = 'UPDATE' THEN
-- if category is without change simple
IF NEW.category = OLD.category THEN
UPDATE safecache.cache
SET sum_val = sum_val + (NEW.int_value - OLD.int_value)
WHERE category = OLD.category;
ELSE
-- old category has to exists
UPDATE safecache.cache
SET sum_val = sum_val - OLD.int_value
WHERE category = OLD.category;
-- new category is maybe problem
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
LOCK TABLE safecache.cache IN SHARE ROW EXCLUSIVE MODE;
-- I have to repeat test
IF NOT EXISTS(SELECT category
FROM safecache.cache
WHERE category = NEW.category) THEN
INSERT INTO safecache.cache
VALUES(NEW.category, NEW.int_value);
END IF;
ELSE
-- simple, new category exists
UPDATE safecache.cache
SET sum_val = sum_val + OLD.int_value
WHERE category = NEW.category;
END IF;
END IF;
ELSE -- DELETE
-- value have to exist in cache, simple
UPDATE safecache.cache
SET sum_val = sum_val - OLD.int_value
WHERE category = OLD.category;
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER actualise_cache
AFTER INSERT OR UPDATE OR DELETE
ON safecache.source_tbl
FOR EACH ROW EXECUTE PROCEDURE safecache.source_tbl_trg_fce();

From what I can tell from your example it's "correct" and should
work under light loads. However, if that trigger will fire a lot,
you might see those updates "stacking" due to the necessary locking
(both your explicit locks and those take out by the updates). What
I've done in that case (this is actually a pretty standard setup), is
to have the trigger just make inserts into another table of the
category that needs to be updated and by how much. The you have some
other (probably user-land) process periodically sweep that table,
aggregate the updates to the cache table, then delete the interim
entries just processed. Oh yeah, you could simplify that function a
lot by simply initializing your cache table with a row for each
category with sum_val = 0. Then it's all updates and you don't need
those locks to determine if the category exists there.

I know it, but I don't know all posible category numbers, and others.
I sent sample with minimum (one) pk attributies.

One workaround is to make an on insert trigger that fires before this
one and checks to see if this is a new category and sets up the row
with value 0 in the cache table.

Erik Jones

I have 98% of SELECTs and 2% of INSERTs and UPDATE

Sounds like you should be ok then and you may not need to go with the
suggestions I've outlined. However, be sure to keep a close eye on
pg_locks when you push that trigger into production.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com